Hi again,

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

OR

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.

...depends
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.

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