I don't see the bigger picture yet. I'm 3 weeks in database design :). What I understand and perceive to be of value in this design is the reinforcement of the foreign keys. Related to that, the control over record deletion, cascading, etc.
I mentioned earlier that the "interim" table that must exist in the many to many / one to many design makes no sense to me. I guess it serves as some type of support structure ? I agree that it would be better to have a list prepared for users to choose from. I'm working on a job board. Looking out on the vast sea of careers and positions it's pretty staggering as to the amount of job titles out there. My plan was to track the more common ones and start building a list from there. As it relates to the many to many issue, well I am still confused. I promise to read more and hit my head with the book. Stuart --- [EMAIL PROTECTED] wrote: > 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 > === message truncated === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
