2

after watching some tutorials, I feel like I am still confused about cardinality when it comes to one to many and many to many relationship. I am trying to practice a little example here, I know my design is wrong when it comes to cardinality, what would be the correct relationship between these entities ?? I get confused when I have 3 table relationship like FilmPeople and FilmGenre

enter image description here

Ris
  • 1,892
  • 9
  • 26
  • 43

1 Answers1

2

Relationships look right. There are some problems though.

  1. Naming convention: You should name People table as Person because you don't have plural naming anywhere else.
  2. Name FilmDirector table as Director. See next point for why. Once you do that Director table should not have film_ID column.
  3. Your model has a mixture of logical and physical model. Let me explain. Relationship between FilmDirector and Film is a logical many-to-many relationship. When that relationship is physically created in a database it goes through Film->FilmDirector and Director->FilmDirector tables with Film to FilmDirector one-to-many and Director to FilmDirector one-to-many relationships. FilmDirector table connects Film and Director tables to establish many-to-many relationship. Therefore I suggest naming your FilmDirector table as Director so that the connection table can be correctly named as FilmDirector. Such a connection table is needed in many-to-many relationship because it is not possible to put multiple values of foreign key references in one table row. Entries go in the connection table for every row in one table being related to multiple rows in other table. In this example for relating one Film with multiple Directors we make multiple entries in FilmDirector table. Similarly for relating one Director with multiple Films we make entries in FilmDirector table.
  4. Relationship you have shown between Film, FilmGenre and Genre is a physical many-to-many between Film and Genre. If your model is a logical model you should not show this as a 3 table relationship. If the model is a physical model you should show Film and Director many-to-many as a 3 table relationship. So, decide whether you want to show physical model or logical model and change your model accordingly.

Hope my explanation is clear enough!

RaviH
  • 3,544
  • 2
  • 15
  • 14
  • you said filmDirector table as Director , so what if Film (meaning the film itself) has to be directed by one or more directors ? would I still have not have FilmDirector and Film...thats why I put it there – Ris Jan 22 '14 at 03:34
  • edit: yes I was thinking Film with multiple Directors or just one – Ris Jan 22 '14 at 03:38
  • thank you so much!...I went over this one more, I'm glad you told me the difference between logical and physical many to many relationship..wish I could give you 10 likes – Ris Jan 22 '14 at 03:49