Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2006
    Posts
    3

    One to many, Two or Three tables

    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.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    ...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.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •