----- Original Message ----- From: "Stuart Felenstein" <[EMAIL PROTECTED]> To: "Peter Brawley" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, August 27, 2004 10:06 AM Subject: Re: Many to Many: Does this make sense ?
> Thank you for the "stop" sign. > As for the RAD tool I'm open to suggestions, but I > think I've read about them all. PHP - MySQL platform. > I don't have an alternate RAD tool to suggest since I don't know what's out there. Let me suggest a different *design* that may work well with your existing RAD tool. Most database designers would never implement a true many-to-many relationship in a real database. Instead, they would break the many-to-many relationship into two one-to-many relationships based centered on something called an "association table" (or sometimes an "intersection table"). For example, in your case, I would create these tables: Members (1 row per member) ------------------------------------ member_id member_name etc. A Jones ... B Smith ... Primary key(member_id) Titles (1 row per title) -------------------------- title_id title_name etc. 1 Bullitt ... 2 Serpico ... Primary key(title_id) Member_title (1 row for each title associated with a member) ----------------------------------------------------------------------- member_id title_id A 1 A 2 B 2 Primary key(member_id, title_id) Foreign key #1 (member_id) Foreign key #2 (title_id) In other words, Jones owns both movies while Smith owns only Serpico. This design is not using mnemonic codes - it's a lot harder to think of good mnemonics for people and movie titles than for airlines - so you will usually have to join back to the Members and Titles tables to find out the name of the member or the title of his movie. The tables are small and the joins should be very efficient so this shouldn't be a problem for you. This design allows any member to own as many movies as they want. If you want to limit it to 5 movies per member, your application code can simply count how many the member currently has before allowing an insert of a new movie. In this way, your RAD tool only needs to come up with a form that allows one row to be inserted into each table. It sounds as if it can already do this. Of course users will have to invoke that form once for each title that they want so they may find this tedious. Then again, how many people will really want 5 movies in one go? Aren't they more likely to pick one or two at a time? In that case, they'd only be invoking that form once or twice, not five times, so they might not object too much. Do you see why this design is a better approach? Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
