As I said in my reply, if you go with the association table design, you will likely write your code so that the movie information will be prompted 5 times if the member chooses 5 movies (or 3 times if they choose 3 movies, etc.). However, you don't HAVE to do it that way. You could always create a form that asks them for up to 5 movies. You'd probably have to create that form manually though, unless your RAD tool has the ability to do that.
You say that this design doesn't completely make sense to you. What are your questions about it? I assure you, I'm not making this design up out of thin air; much smarter people than I have created this design and it is very widely used in relational databases around the world. I'm just trying to tell you how "the pros" do things. If you have specific questions, I'll do my best to explain why the design is the way it is. Rhino ----- Original Message ----- From: "Stuart Felenstein" <[EMAIL PROTECTED]> To: "Rhino" <[EMAIL PROTECTED]>; "Peter Brawley" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, August 27, 2004 11:47 AM Subject: Re: Many to Many: Does this make sense ? > I think this design is a better approach but only if > it's based on human tendencies (aka max 2 movies at a > clip) > Othewise (and if I'm missing something tell me please) > if they should decide to enter 5 or 10 then the form > comes at them 5 or 10 times. > > Your right though my RAD will allow it. Right now > I've created a series of pages, with options to either > enter another one (next page), or proceed to the next > category. > > In effect that would be similar. Fortunately unlike > movies, this form is probably going to be used > infrequently. Once they've maxed out , the only option > after this is replace or delete. > > Finally I don't think M2M or 12M make total sense to > me. I'm not sure about the interim table. A query > with some joins would net back the same results. What > I think is it's necessary to the 12M process. > > Thank you, > Stuart > > --- Rhino <[EMAIL PROTECTED]> wrote: > > > > > ----- 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] > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
