selliottsxm Posted April 1, 2014 Share Posted April 1, 2014 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 Link to comment Share on other sites More sharing options...
HartleySan Posted April 1, 2014 Share Posted April 1, 2014 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 1 Link to comment Share on other sites More sharing options...
selliottsxm Posted April 1, 2014 Author Share Posted April 1, 2014 So a different row for each actor with the movie_id being the same as opposed to having actor_1, actor_2 columns? Link to comment Share on other sites More sharing options...
HartleySan Posted April 2, 2014 Share Posted April 2, 2014 Absolutely. If you have actor_1 and actor_2 fields, then what would you do when there are three actors? Link to comment Share on other sites More sharing options...
selliottsxm Posted April 2, 2014 Author Share Posted April 2, 2014 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 Link to comment Share on other sites More sharing options...
HartleySan Posted April 2, 2014 Share Posted April 2, 2014 Cool. Good luck with your project. Link to comment Share on other sites More sharing options...
Recommended Posts