How do I handle (what I think is) a many-to-many relationship in MySql?

May 31, 2004 1:40 PM

MySQL (or generic relational database) question.

I'm building a site for a dvd store and am new to php/mysql. I read Larry Ullman's excellent book PHP and MySQL for Dynamic Web Sites as well as did a search but can't find an answer to this question. It may be because I don't really know how to phrase the question succinctly. Here goes:

I have a table (say, TALENT) which will have various fields including a talent_id field as the key.

I have another table (DVDs) which will have a reference to the talent_id key from the TALENT table.

What I can't seem to get my head around is in the DVDs table, how do I handle instances of multiple actors? Most of the other fields (title, year, etc.) will have one answer, but the talent field in the DVDs table will have many different "answers".

Is this an example of a many-to-many relationship? From the descriptions I've read of M2M, I don't think so, but I can't seem to figure out how to handle the situation... any help, clarity, or links would be appreciated.

posted by dobbs to Computers & Internet (11 answers total)

you could have an extra table that is called cast, and which has two columns - talent_id and film_id (film_id could be the same as dvd_id, but you might want to worry about having more than one dvd of the same film).

then, to get the names of all the talent in a given film, you might do:

select name from talent where talent_id in (select talent_id from cast where film_id = 42)

(or something similar - not used sql for a while). there may be other ways to do it, perhaps better normalised (the cast table doesn't have a single unique key), but i hope this shows you the general idea.
posted by andrew cooke at 1:53 PM on May 31, 2004

It is a many-to-many relation. Each DVD links to many actors, and each actor links to many DVDs. One way to handle it is to use a separate linking table with two columns, one for DVD ID, the other for actor ID. Each row contains a link between a single DVD and a single actor.

I guess if you've already read about many-to-many relations, I don't need to go into detail you already have. So maybe we can help you understand better if you say why you don't think it's that type?

As for links, here's a random link about many-to-may relations (but probably nothing you didn't already know).
posted by whatnotever at 1:55 PM on May 31, 2004

Yes, it's a many-to-many relationship. (On preview, what everyone else said) Each DVD is related to one or more actors (1:M); each actor is related to one or more DVDs (1:M); those reciprocal 1:M relationships together make the M:M. Don't store the talent_id in the DVD table. This link has an basic example and diagram.
posted by nakedcodemonkey at 2:09 PM on May 31, 2004

Here's a good book, by the way, for help with this stuff: Concepts of Database Management was very helpful back when I was struggling to wrap my head around this stuff. As the title implies, it addresses the broader conceptual issues (how to visualize, design, and efficiently query a relational database) rather than a specific RDBMS's feature set. It helps you figure out how to break down data usefully, the illustrations are clear, and there are SQL examples in the relevant places. It's one of the few old textbooks I've saved and continue to refer to. Paul DuBois' MySQL also has an excellent general intro-to-relational-databases section. Once you master the base concepts, the rest gets MUCH easier to understand. :-)
posted by nakedcodemonkey at 2:30 PM on May 31, 2004

Response by poster: Okay, thanks, all. I think I understand it now. I think my big problem was not realizing that I needed a third table.

So, if I'm understanding correctly:

TALENT TABLE
talent_id
... details

DVDs TABLE
dvd_id
... details

CAST_AND_CREW TABLE
talent_id
dvd_id
nothing else

Then, whenever I want to do a search on the site of any talent, I'm searching the Talent table, finding the name, grabbing the talent id, matching it to the cast and crew table, grabbing the dvd_id and then outputting results from the DVD table?

Wow, that seems convoluted. :)

On preview, thanks makedcodemonkey... you just reminded me that somewhere in the deep, dark, back-hall-of-my-apartment I have a book called Database Design for Mere Mortals from a previous outing into rdbm which failed. Will have to go dig that one up.
posted by dobbs at 2:35 PM on May 31, 2004

Database Design for Mere Mortals is a terrific primer on this stuff and will explain this situation very well. Everyone else already gave you the answer on how to handle a many-to-many realtionship. If you're going to store more than just actors in the mapping table (as a convention I usually call those tables something like TALENT_MAP_DVD to make it painfully obvious 6 months from now what the table does), you might want to add a third column that explains what the "talent" did for that DVD (at the risk of overwhelming you, this would probably be just a simple INT or ENUM field that tied into another table like POSITIONS which held items like 'actor', 'director', etc.).
posted by yerfatma at 2:47 PM on May 31, 2004

your example is fine. i don't know what mysql lets you do, but you might want to define the pair of talent_id and dvd_id together as the key for cast_and_crew. that'll give the db engine something to organise things with and save you from accidentally defining the same member of a dvd twice. i'm sure this is the kind of thing other people's links explain.

a chap called celko also has a good reputation for writing db books, but they're perhaps not very how-to.
posted by andrew cooke at 2:58 PM on May 31, 2004

CAST_AND_CREW TABLE
talent_id
dvd_id
nothing else

Keep in mind, dobbs that this structure allows you to also add any data associated with that particular talent in that particular film too, such as: what role they played, compensation, etc.
posted by vacapinta at 4:07 PM on May 31, 2004

Another suggestion: be prepared to throw out your design.

The nuts and bolts of relational database design are not difficult, but understanding the data and how you want to represent it can be tricky indeed.

As an example from your "DVD Store" database:

What constitutes a "DVD" in your DVD table? Seems simple: an individual disc can be identified by a SKU number, right?

Well, what about different editions (Fullscreen, Widescreen, Director's Cut, etc)? Different SKU numbers, but they all have the same "cast and crew," so linking the cast directly to a specific disc will require duplicating the cast info for each version of the disc.

Maybe you should have a FEATURE table that describes the film ("THE THING") linked to the "cast and crew" ("directed by Howard Hawks, starring Kenneth Tobey"), and the different DVDs point to the FEATURE.

(BTW, I'm sitting here looking at a disc that contains _two_ films: VOYAGE TO THE BOTTOM OF THE SEA and FANTASTIC VOYAGE. Now what do you do?)

The point is: don't be surprised if you discover that your initial database design has problems. Design is a "wicked" problem; you need at least a partial solution to really understand the requirements. So, be prepared to start over and re-do it.

Finally, it wouldn't hurt to browse through an existing database similar to what you want to design to get ideas. Spend a little time with the IMDB Database and note some of the odd things that turn up in the listings. Good luck.
posted by SPrintF at 9:07 PM on May 31, 2004

dbDesigner 4 is a free, open source tool that might help you with design and implementation.
posted by billsaysthis at 9:08 PM on May 31, 2004

Response by poster: Thanks, all.
posted by dobbs at 8:40 PM on June 1, 2004