Stuart, Both Rhino and I have suggested the same basic data storage structure to you. What you need to understand is that your RAD tool is not supporting your application design. You can use a form with 5 or 10 blanks on it where the user can list all of their choices. Then when the user submits the multiple field input form, your handling code will need to go down the list of fields and make the appropriate database entries. 1 form with 5 fields. User is happy, database is happy. You have kept the user from entering too many choices because you only gave them as many fields as you wanted them to have. In this case, the application's user interface is enforcing a business rule through its design. Your data base doesn't care how many titles each member has (from 0 to several billion) but your application requirements need the user to have no more than 5. That's a business rule and you code for that above the database (either in your validation code or, in this case, through UI design)
I would stick with the many-to-many database design as you don't want people mis-typing titles all of the time:"Godfather" is not "The Godfather" which is different than "The God Father" and wouldn't match "The Godfather I" ...(can you see where I am going?). Keep a separate table of titles, even if they are used only once or never at all. That way if anyone needs to look one up, you already have a list. You could also add titles to the list BEFORE any members wants to see them and you can make sure they are spelled correctly. I only trust users to type in things that the users are expert at and I don't think that your users are going to be a bunch of film historians. I would pre-enter what I could and review any new entries they make (nobody said your users can't add to the list, did they...). Let us know if any of this makes sense or not, please. This is an important topic in database design and you are not the first person, nor will you be the last, to make this design decision. The more we discuss this, the better off the next person with this problem will be. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Stuart Felenstein <[EMAIL PROTECTED]> wrote on 08/27/2004 11:47:33 AM: > 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] >
