DVD movie database

[timbits35]

Hi,

I am trying to design a movie database. Yes, I know there are templates around but they do not go into enough detail. I am specifically having problems with many-to-many relationships. Do I need a junction table for each many-to-many relationship that exists. So if I have 5 many-to-many relationships, I have to have 5 junction tables. Also, I do not know how to deal with TV series and episodes. I will have both movies and tv series with episodes.

So far I have these tables.

tblmovies - title, country, year, length etc.
tblgenre - each movie can only have 1 genre
tblsubgenre - each movie has only 1 subgenre
tblproducer - each movie may have more than 1 producer and each producer can produce many movies
tbldirector - each movie may have more than 1 director and each director can direct many movies
tblactors - each movie has more than 1 actor and each actor can appear in many movies

Would I put the episode in the tblmovies as if it were a movie and then have another table for the series?

Please help me with the relationships and how many junction tables I would need? If someone could post an image of the table relationships that would be great.

Thank you

 

[jdraw]

Watch this video to understand resolution of Many to Many.

https://www.youtube.com/watch?v=7XstSSyG8fw

These free video tutorials describe the processes of data modelling, normalization and entity relationship diagramming. There are other videos, but this group is by the same presenter and covers an example situation. The example relates Customers, Orders and Items.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2

The first few topics at this site are well worth the read.

http://www.rogersaccesslibrary.com/forum/topic238.html

Good luck with your project.

 

[timbits35]

Hi,

Thank you for the links. However, I understand many-to-many relationships IF there is just ONE of them. But I do not know what to do if I have SEVERAL many-to-many relationships. Do I need a junction table for each of them?

And it also does not help resolve how to best deal with tv series and episodes in the same database as movies.

Thank you

 

[jdraw]

When you are looking at a relationship between 2 Entities, you have to ask yourself (using your test data or business set up) whether or not there is a Many to many relationship between these 2 entities. If there is a Many to Many relationship, you will require a Junction table. So the same process as shown in the video occurs between 2 entities that have a Many to Many relationship. You would just go through the process with the next pair of entities.

Now for a more basic process to set up relationships in general, there is a step by step process with examples at
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

You can go through the example provided, then you can use the supplied templates to go through the same process with your own data/situation.

One thing you may want to do because you know what exactly you are trying to achieve and we don't, is to write down a description of what each of your entities is. By doing this you will know what distinguishes a genre from a subgenre, and a tv series from an episode.
You may also wish to research granularity as it refers to database (ie how much detail do you want -- remember that more detail may mean more maintenance).

http://toytoygogie.blogspot.ca/2012/02/what-does-granularity-mean-when.html