Any help will be rewarded with reputation points. Imagine this simple database: Movie_Table movie_id movie category_id Category_Table category_id category In the example above, each movie can only have 1 category. How can I make it so that each movie can have multiple categories? I heard that I need to add another table to make a many-to-many relationship, thus: Movie_Table movie_id movie movie_category_id Category_Table category_id category Movie_Category_Table movie_category_id movie_id category_id What I don't understand is, with this structure, movie_id can still only be associated with 1 category_id. Am I correct?
Remove the movie_category_id column from the Movie_Table. The Movie_Category_Table has both the movie_id and category_id columns you need for the many-to-many relationship. I hope this makes sense. Here is an example: Movie_Table 1, "Movie A" 2, "Movie B" 3, "Movie C" Category_Table 10, "Category A" 20, "Category B" 30, "Category C" Movie_Category_Table 100,1,10 200,1,20 300,1,30 "Movie A" is associated with "Category A", "Category B", and "Category C".
Thanks for the response, Social.Network, but I still don't get it. If I done it your way, every time I inserted a movie into Movie_Category_Table, I would have to insert it multiple times, each time with a different category. The categories, too, would therefore be repeated multiple times with different movies. The tables wouldn't even be normalised. Did I misunderstand you or something?
The Movie_Category_Table is a link table, which contains common fields from two tables. In this case, movie_id from the Movie_Table and category_id from the Category_Table. The tables are normalized, but you need to know that the link table will contain duplicate keys for the parent. Also, I should have noted that you need to create the primary key from movie_id and category_id, so that you do not have duplicate movie and category entries in the link table. I hope this make sense.
So there will be duplicate content in Move_Category_Table? Each movie_id will occur on multiple rows, each time associated with a different category_id? If so, may I ask one final thing? What kind of join would there be between Movie_Table and Movie_Category_Table?
Can anyone help, please? How would I select the categories of 1 movie? What type of join would it be? Whatever I try, it doesn't work!
But thats the point of this, the requirement is such where you need to relate multiple categories to each movie, I dont understand why you worried about this. You need to accept that Many to Many relationship is a special relationship and we are achieving by this trick, obiviously, we doing it with a compromise, at the cost of normalisation of the table, but then this is the way. Or else what could be the solution to relate each movie to many categories ?
Thanks, Khudania, but I still don't understand the correct way to select which categories any one movie is in.