SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot Caged's Avatar
    Join Date
    May 2003
    Location
    United States
    Posts
    107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Best Data Type for Storing Favorites

    Hi,

    I'm wanting to store a group of id's in mysql wich will be seperated by spaces (1 23 342 232 ) etc. What would be the best datatype to use for this considering they could have anywhere from 1 favorite to 1000.

    Regards -Justin

  2. #2
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is a bad, bad idea, and is in violation of the first normal form. Do a Google search for "repeating groups" and normalization to see why.

  3. #3
    SitePoint Zealot Caged's Avatar
    Join Date
    May 2003
    Location
    United States
    Posts
    107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey Matt, thanks for that bit of information.. I was unaware of this. Is this stating that values that are stored within the database should only be single values, and not multiple representation of many values?

    In any case, what would you suggest for accomplishing this task?

    A user needs the ability to add to his/her favorites. Assuming I have to store this in a seperate table, I need to store the id,userid and article id's (whats being fav'd).

    Thanks again

  4. #4
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Caged
    Is this stating that values that are stored within the database should only be single values, and not multiple representation of many values?
    In short, yep.

    In any case, what would you suggest for accomplishing this task?

    A user needs the ability to add to his/her favorites. Assuming I have to store this in a seperate table, I need to store the id,userid and article id's (whats being fav'd).

    Thanks again
    That is generally how it would be done. However, what would be the purpose of the ID column? What would logically be a good primary key for that table?

  5. #5
    SitePoint Zealot Caged's Avatar
    Join Date
    May 2003
    Location
    United States
    Posts
    107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, Each user would be unique, all though depending on how its done, there would be multiple records with the same userid ie


    userid fav_id
    1 34
    4 2
    1 19

    I looked at the way www.4images.de does it, and they store it like I had originally thought would work, but in a seperate table from the users table. They generate an md5 hash for a lightboxid , lightbox being favorites group.
    There structure is as follows


    Field Type Null Key Default Extra
    lightbox_id varchar(32) MUL
    user_id mediumint(8) MUL 0
    lightbox_lastaction int(11) unsigned 0
    lightbox_image_ids text YES NULL

    They have no primary, but two Keys, one being user_id and lightbox_id.

  6. #6
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, you have to answer the question "What attribute or attribute combination can uniquely identify a row?"

    In your case, you have it correct:
    userid fav_id
    1 34
    4 2
    1 19
    There is no need to append an ID on the table beacause userid, fav_id combination is unique. So, that would be a perfect primary key.

  7. #7
    SitePoint Zealot Caged's Avatar
    Join Date
    May 2003
    Location
    United States
    Posts
    107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for all your help and advice Matt That pretty much sums it up.


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
  •