Database Design

[selliottsxm]

HI,

 

Hope someone can clarify for me.  I'm refering to the examples used in Chapeter 6 regarding the movie database.  I have to build a database very similar.  Currently all the data is stored in an excel spreadsheet.  I have one column for actors (multiple values each cell) which contains all the actors names (groan not separated by last and first names), and the names are sperated by pipes.  I also have a director column (1 value each cell), studio column (1 value each cell) and genre (multiple values each cell) column which also uses pipes as a separator.

 

I realize that I have to create separate tables.  In the example given the movie-actors table has two fields, movie_id and actor_id. Wouldn't that end up violating the 2NF compliancy rule since the movie_id would have to be in multiple records with each actor?

 

In other words movie_id 1001 has 4 actors.  Wouldn't that require 4 rows defining each actor? 

 

Help

 

Steve

[HartleySan]

You would have a table for movies, another table for actors, and then a third table that joins the two.

If, for example, the movie in question has the ID 1, and the actors in that movie have IDs 1, 2, 3 and 4, then the intermediary table would probably have the following entries:

movie_id   actor_id
1          1
1          2
1          3
1          4

[selliottsxm]

So a different row for each actor with the movie_id being the same as opposed to having actor_1, actor_2 columns?

[HartleySan]

Absolutely. If you have actor_1 and actor_2 fields, then what would you do when there are three actors?

[selliottsxm]

I guess what Hollywood does and kill the third one off? 

All joking aside thanks for the help. 

You made it very clear as to how my tables will relate and how to make them compliant

[HartleySan]

Cool. Good luck with your project.