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
