Question About Many-to-Many Relationship Tables

[Tehuti]

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?

[phranque]

get rid of column movie_category_id in table Movie_Table and your db schema should work properly.

[ZydoSEO]

Not sure why you have movie_category_id in the Movie_Category_Table. This table only needs the primary key from Movie_Table (Movie_ID) and the primary key from Category_Table (Category_ID) to relate a movie to a category. Unless of course you have other tables tied to a particular relationship and you want a simple single field key for the Movie_Category_Table. But if you have no other tables where you use Movie_Category_ID then it's really an unnecessary field.

[Tehuti]

Phranque and ZydoSEO, thank you both very much! I have to say, however, that there's still something which I don't understand . . .

According to your instructions, my database should look like this:

Movie_Table
movie_id
movie

Category_Table
category_id
category

Movie_Category_Table
movie_id
category_id

What I don't understand is, with only two columns in Movie_Category_Table, the only way I can get a movie to be associated with more than one category is if I entered the movie multiple times, each time with a different category_id.

Is that how it's meant to be? Or have I misunderstood you?

[Tehuti]

Oh, and another thing . . .

If I did understand you correctly, what type of join would there be between Movie_Table and Movie_Category_Table?

[rocknbil]

No this will work, any minor complexity comes in your programming (how to add this movie to another category.) Try visualizing some data in your tables.

Movie_Table 
movie_id movie 
1........Titanic 
2........Aliens 

Category_Table 
category_id category 
1...........Love Story 
2...........Adventure 
3...........Drama 
4...........Sci Fi 
5...........Horror 

Movie_Category_Table 
movie_id category_id 
1.........1 
1.........2 
1.........3 
2.........4 
2.........5 

[Tehuti]

Thanks, Rocknbil! I appreciate it, man.

There's still one more thing though . . .

How would I select a movie and all of its categories? What type of join would it be?

[Tehuti]

Can anyone help, please?

How do I select 1 movie and all of its categories? I don't understand the joins.

[phranque]

something like this should work:
select movie,category from Movie_Table,Category_Table,Movie_Category_Table where Movie_Category_Table.movie_id=Movie_Table.movie_id and Movie_Category_Table.category_id=Category_Table.category_id and Movie_Category_Table.movie_id=N

[Tehuti]

Phranque, thank you very much!

I done what you said and it worked. However, it also worked when I tried both a LEFT JOIN and a RIGHT JOIN, thus:

-------------

SELECT movie, category FROM Category_Table, Movie_Table
LEFT JOIN Movie_Category_Table on Movie_Table.movie_id=Movie_Category_Table.movie_id
WHERE
Movie_Category_Table.category_id=Category_Table.category_id
AND
Movie_Category_Table.movie_id='1';

-------------

SELECT movie, category FROM Category_Table, Movie_Table
RIGHT JOIN Movie_Category_Table on Movie_Table.movie_id=Movie_Category_Table.movie_id
WHERE
Movie_Category_Table.category_id=Category_Table.category_id
AND
Movie_Category_Table.movie_id='1';

-------------

Does it matter which way I do it?

[ZydoSEO]

I'm not sure why you would need LEFT OUTER or RIGHT OUTER joins. You should be able to use normal INNER JOIN (just plain JOIN) unless you are trying to find out which categories a movie is NOT in.

For example, to find all of the categories for all movies you might do something like:

SELECT m.movie_id, m.movie, c.category_id, c.category
FROM Movie_Table m
JOIN Movie_Category_Table mc ON m.movie_id = mc.movie_id
JOIN Category_Table c ON mc.category_id = c.category_id
ORDER BY m.movie, c.category

To find all categories for a particular movie (by movie_id) you might use something like:

SELECT m.movie_id, m.movie, c.category_id, c.category
FROM Movie_Table m
JOIN Movie_Category_Table mc ON m.movie_id = mc.movie_id
JOIN Category_Table c ON mc.category_id = c.category_id
WHERE m.movie_id = 123
ORDER BY m.movie, c.category

To get a list of movies that are in a particular category (by category_id) you might do something like:

SELECT m.movie_id, m.movie, c.category_id, c.category
FROM Movie_Table m
JOIN Movie_Category_Table mc ON m.movie_id = mc.movie_id
JOIN Category_Table c ON mc.category_id = c.category_id
WHERE m.category_id = 456
ORDER BY m.movie, c.category

The key is to get from a movie to their category (or categories) or to get from a category to a movie (or movies) you have to go through your relationship table (Movie_Category_Table). So you SELECT FROM Movie_Table then JOIN to Movie_Category_table using movie_id then JOIN to Category_Table using category_id. Or you can do the reverse where you SELECT FROM Category_table then JOIN to Movie_Category_Table using category_id then JOIN to Movie_Table using movie_id.

You cannot just JUMP from Movie_Table straight to Category_Table or visa versa. You go through the relationship table Movie_Category_Table anytime you need to know the categories a movie is in or which movies are in a category.

Hope that helps...

[edited by: ZydoSEO at 5:48 pm (utc) on Sep. 24, 2008]