Question About Many-to-Many Relationship Tables

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?

 

sounds like a one-to-many join. My normalisation is a little rusty tho.

 

I meant, what type of join would I use in my SELECT statement? Anyone know?

 

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.

 

I no longer need any help with this one, guys.

I've added points to your reputations.