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

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 http://www.faqts.com/knowledge_base/view.phtml/aid/5914/fid/52 (but probably nothing you didn't already know).

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. http://www.frick-cpa.com/ss7/Theory_Relationships.asp#NN

Here's a good book, by the way, for help with this stuff: http://www.amazon.com/exec/obidos/tg/detail/-/0619064625/qid=1086037887/sr=8-1/ref=sr_8_xs_ap_i1_xgl14/103-9987331-8711044?v=glance&s=books&n=507846 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. :-)

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.

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.).

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.

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.

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 http://www.imdb.com and note some of the odd things that turn up in the listings. Good luck.

http://www.fabforce.net/dbdesigner4/index.php is a free, open source tool that might help you with design and implementation.