Thread: One to many, Two or Three tables
10-24-06, 05:51 #1Registered User
- Join Date
- Oct 2006
One to many, Two or Three tables
I am wondering about one to many relationships. What is preferred one -> lookup table -> many or one -> many
Ie a user table with user_id, a user_images table with user_id and image_id and and image table with image_id
user table with user_id, and image table with image_id and user_id
To be honest I quite like the idea of an inbetween lookup table as thats how I have been doing it but it occurred to me I can do it with two. What is good practice, what is the performance of either method?
Will I be caught out later on by doing it one way?
Thanks for your time.
10-24-06, 07:59 #2Jaded Developer
- Join Date
- Nov 2004
- out on a limb
if the image is unique to a user then two tables should do. Arguably if you have a single image for a single user then the image becomes an attribute of the user, so only one table is required.
if an image may be common, ie used by one or more users then two tables are required with the image ID stored against a user in the user table.
if you need to store more than one image per user, and those images can be shared then you need the intermediate lookup / cross reference table.I'd rather be riding on the Tiger 800 or the Norton
10-24-06, 08:01 #3SQL Consultant
- Join Date
- Apr 2002
- Toronto, Canada
the "lookup" table which you describe is actually a relationship table which implements a many-to-many relationship -- users, images, and user_images as the relationship table
yes, it is possible to utilize this structure to implement a one-to-many relationship instead of a many-to-many relationship beween users and images
you cannot get "caught out later" by doing this
the code will obviously be a bit more complicated to maintain and to retrieve from the many-to-many structure, but there is one good thing in its favour -- you can easily change the actual data being represented from one-to-many into many-to-many
however, if you simply implement a one-to-many relationship, with a users table and an images table, where each image is owned by a specific user (with the userid foreign key in the images table), then it is painful to change this relationship to many-to-many later