Sunday, March 25, 2012

Author and movies (many to many relation) database question...

Hi to all,
first of all I apologise if there is already someone that asked the
same thing.
I have 3 entities (tables).
- AUTHOR (id_author, author_name, birth_date, biography)
- AUTHOR_FUNCTION (id_function, function),
- MOVIE (id_movie, movie_title, year...).
Their relations:
1.) Each author can work in many movies
2.) Each author can have many functions in different movies.
3.) Each author can have many functions in the same movie.
4.) Each movie can have many authors.
And here comes the question:
I already know that I have to create a "middle" entity/table
ex.AUTHOR_FUNCTION_MOVIE (id_movie, id_author, id_function) but is
there any other way to solve the many-many relation? I don't like very
much the idea to have so much entries in the table - in fact the same
author&movie can be listed many times because of its different
function...
Thank you in advance for your help:-)> there any other way to solve the many-many relation? I don't like very
> much the idea to have so much entries in the table - in fact the same
> author&movie can be listed many times because of its different
> function...
Why ? It is a classic many-to-many soultion to have a "junction" table
Actually, if you need some searching or other issues that we do not know you
may try to denormalize tables
<Peter.Laganis@.gmail.com> wrote in message
news:1147685467.399149.310320@.v46g2000cwv.googlegroups.com...
> Hi to all,
> first of all I apologise if there is already someone that asked the
> same thing.
> I have 3 entities (tables).
> - AUTHOR (id_author, author_name, birth_date, biography)
> - AUTHOR_FUNCTION (id_function, function),
> - MOVIE (id_movie, movie_title, year...).
> Their relations:
> 1.) Each author can work in many movies
> 2.) Each author can have many functions in different movies.
> 3.) Each author can have many functions in the same movie.
> 4.) Each movie can have many authors.
> And here comes the question:
> I already know that I have to create a "middle" entity/table
> ex.AUTHOR_FUNCTION_MOVIE (id_movie, id_author, id_function) but is
> there any other way to solve the many-many relation? I don't like very
> much the idea to have so much entries in the table - in fact the same
> author&movie can be listed many times because of its different
> function...
> Thank you in advance for your help:-)
>|||Yes I know, but ex.if I have 20.000 movies and 5.000 authors and 15
functions the no of entries in the "junction" table can be a lot. Will
not that impact on the aplication response time?|||Hi
No, it should not. I have been working on very large database which has a
junction table that contains 50 mln rows. If you have properly defined
indexes on the tables it should not be an issue at all
<Peter.Laganis@.gmail.com> wrote in message
news:1147687257.391706.17460@.i40g2000cwc.googlegroups.com...
> Yes I know, but ex.if I have 20.000 movies and 5.000 authors and 15
> functions the no of entries in the "junction" table can be a lot. Will
> not that impact on the aplication response time?
>

No comments:

Post a Comment