Thanks. I realized I was explaining it wrong, saying a 1-to-many, when it's really a many-to-many. One of those days. : )
Joel ----- Original Message ----- From: <[EMAIL PROTECTED]> To: "SQL" <[EMAIL PROTECTED]> Sent: Wednesday, August 20, 2003 2:10 PM Subject: RE: 1 to many relationships > Technically, you only have to do a separate table when you have a > many-to-many relationship. In a one-to-many relationship, you can bring > the primary key of the songs table over into the members table as a > foreign key, which is still normalized form. However, looking at the > sample data included with the original email, it looks like a many-to-many > relationship, which makes Tim's statement the best answer to the problem. > > > > > > "Raster, Tim" <[EMAIL PROTECTED]> > 08/20/2003 01:00 PM > Please respond to sql > > > To: SQL <[EMAIL PROTECTED]> > cc: > Subject: RE: 1 to many relationships > > > Short answer: Yes. > > You want to hang a new table with a 1-Many relationship off with the 2 > columns, just as you said. It will mean you need to do a few extra > queries to store/retrieve your data, but you will almost invariably be > happier for it in the long run. > > > -----Original Message----- > From: Joel Firestone [mailto:[EMAIL PROTECTED] > Sent: Wednesday, August 20, 2003 08:48 > To: SQL > Subject: 1 to many relationships > > Everyone: > > If this has been asked to death, I apologize now. : ) > > I'm currently redoing a system, and the way the previous developer > was storing data for a user was to have a list of ID's as a list in 1 > column of a table. Like so: > > Members table: > 1|Test|[EMAIL PROTECTED]|1,2,3 > 2|Test|[EMAIL PROTECTED]|3 > 1|Test|[EMAIL PROTECTED]|1,3 > etc > > Songs table: > 1|Song Name > 2|Song #2 > 3|Song #3 > etc > > Now, after reading some stuff here, I don't think this is the best way > to do it. So, from a dbms standpoint, what is the best practice for > storing this data? A seperate table with just the members ID and 1 > song ID? Or is there a better way to do it. > > Note: This is a MySQL db, and not MS SQL or Oracle. So I can't do > sub-queries or SP's. > > Thanks for your time. > > ====================== > Joel Firestone - Developer > http://www.dmv.com/ > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:6 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:6 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=<:emailid:>.<:userid:>.<:listid:> Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
