SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Mar 2010
    Location
    London UK
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Normalise a small table. Should be easy for pros.

    Hi,

    I would like to normalise the table shown below. At the moment, the table is limited to 3 photos for each user, but later on I may want to increase the photo limit to 6. What is the best way to normalise this table?

    ____Table not normalised___
    user_id (INT)
    username (VARCHAR)
    password (CHAR)
    email (CHAR)
    date (DATETIME)
    photo1_url (VARCHAR)
    Photo2_url (VARCHAR)
    Photo3_url (VARCHAR)


    Is the normalisation below ok? If each user has 6 photos and the table has 1000s of records, table 2 would be massive. Should I introduce a 3rd table some how?

    __TABLE 1__
    user_id (INT)
    username (VARCHAR)
    password (CHAR)
    email (CHAR)
    date (DATETIME)

    __TABLE 2__
    user_id (INT)
    photo_url (VARCHAR)

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,204
    Mentioned
    106 Post(s)
    Tagged
    1 Thread(s)
    I wouldn't introduce a new table - it's not needed. The only thing I might add is a unique identifier on the photo table, but that would only if the photo needed to be identified somewhere else. You could use a mixed key of userid + url, but it's not critical.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,411
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    ^^ What Dave said
    Quote Originally Posted by Topkat View Post
    If each user has 6 photos and the table has 1000s of records, table 2 would be massive. Should I introduce a 3rd table some how?
    Just out of curiousity: how did you imagine a third table would diminish the volume of the data?

  4. #4
    SitePoint Member
    Join Date
    Mar 2010
    Location
    London UK
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your help guys!

    Quote Originally Posted by guido2004 View Post
    Just out of curiousity: how did you imagine a third table would diminish the volume of the data?
    All this normalisation stuff is new to me and I wasn't sure if someone could make another table out of the unnormalised data.

    I have one more question if you don't mind. :-)

    If I want to limit the amount of photos a user can upload, I will need to do it in PHP (or some other web scripting language). Is that correct?

    So for example in PHP, I would first query the database to find the amount of photos stored under "user_id". If it's less than 3, I can upload a new photo. Or is there some other way I could limit it in sql?


Bookmarks

Posting Permissions

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