Is this movie database model correct and is it efficient?

Question

Here is my schema:

enter image description here

I need to keep it simple as it's a homework but I'm kind of confused about the Participant and MovieInGenre entities, as I understand they are weak entities, yet Person and Genre and Movie are strong entities. Also how does this model show that the Movie – Genre relationship is n:n?

Here is an answer elaborating more on the relations between a person and a movie: http://stackoverflow.com/a/490490/679227 — Haralan Dobrev, Apr 08 '13 at 13:02
your Participant PK is on 2 fields but should be on all three, in case a person plays multiple roles in a movie (director and editor) — Neil McGuigan, Apr 08 '13 at 17:28
Just out of curiosity: what tool was used to make this diagram? — RBarryYoung, Apr 25 '13 at 18:39

score 0 · Answer 1 · answered Apr 08 '13 at 12:58

0

In my own opinion, the schema is fine. There's a little changes. I think you need to remove table IMDBLink and put the Link (make it NULLABLE) on table Movie.

answered Apr 08 '13 at 12:58

John Woo

258,903
69
498
492

I put it like this, because we need one 1:n, m:n and 1:1 relations and that was, as I understand, the only logical 1:1 relation. We also need weak and strong entities and I'm having a hard time understanding that. – user2257652 Apr 08 '13 at 13:20
oh ok, if that's the case, then it's fine. – John Woo Apr 08 '13 at 13:53
This isn't a case where a 1:1 is really useful. But if it's a school project and you're required to include a 1:1, well then it's as good a place as any. – Jay Apr 25 '13 at 17:39

score 0 · Answer 2 · answered Apr 25 '13 at 17:38

Your schema looks pretty good to me.

I'm not sure what your concern is about Participant and MovieInGenre. You need these entities to create the many-to-many relationships.

Whenever you have three tables with A <- M:1 -> B <- 1:M -> C (if you see what I'm trying to write there without drawing a diagram), that indicates that the A/C relationship is M:M and B exists just to implement that relationship. You can also draw your diagram omitting a table like MovieInGenre and just directly showing a M:M relationship between Movie and Genre. At some point you need to create the table to implement the relationship, but you don't need to show it on your diagram. I normally do not include such entities on my diagrams, as they're just clutter.

What do you mean by "role". If "role" is the name of the character the person plays, then this is fine. If role is something like "director", "producer", "actor", etc, i.e. chosen from a list of standard values, then there should be another table for that and you just post a foreign key. If it's both or either, than that's a bad idea and it should be broken into two fields.

Is this movie database model correct and is it efficient?

2 Answers2