SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict
    Join Date
    Aug 2007
    Posts
    328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    list of items in a single field? (DB design query)

    Hi Guys,
    I'm going to be making a social database driven application and it will allow people to have a friends list for each person.

    I've seen a database that someone is using and they've got a table for users and in that table it has a field for which areas of the website the user has access to (a comma seperated list of areas).

    Personally I don't think this is the best way, as if I want to remove someone's access to an area in sql, it's very difficult (As they're unique numeric keys).

    I know that an UI could be made for this, but I'd ideally like it to be easy in sql (If I want to adjust thousands of records at a time) as well as a UI for specific people.

    Is there a better way in terms of database design to get this done, or is this just the usual method.

    I thought that the best method would be for 1 field to have 1 item in it.

    What would you do?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by stevex33 View Post
    ...a field for which areas of the website the user has access to (a comma seperated list of areas).
    please describe more about these areas

    a comma-separated list of values in a single column is usually a Bad Idea

    however, there are exceptions, and this might be one of them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Aug 2007
    Posts
    328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In the example I gave, the "area" was pretty similar to a website directory.
    Each area have a unique numerical key, and each user has an "allowed areas" field with a comma separated list of the keys of the areas they have access to.

    For my application though, it would be a user with a "friends" field, with a comma separated list of the userids of people he's friends with.

    My feelings are that this would be a bad implementation.

    Do you have an alternative you can recommend?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by stevex33 View Post
    Each area have a unique numerical key, and each user has an "allowed areas" field with a comma separated list of the keys of the areas they have access to.
    so this was just a hypothetical case?

    well, it might just be one of those exceptions i mentioned, but i guess we'll never know


    Quote Originally Posted by stevex33 View Post
    For my application though, it would be a user with a "friends" field, with a comma separated list of the userids of people he's friends with.
    that's definitely wrong

    have a friends table, with exactly two columns: the id of the person, and the id of the person he's friends with
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Aug 2007
    Posts
    328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's not a hypothetical, it's a database that I've seen before, I just thought it would be a good example of something similar that I wanted to do.

    I was hoping you'd tell me that my current idea was wrong (As I knew it wasn't the best way, just didn't know the alternative.

    I like your idea of having a friends table with 2 columns.

    So if 1 person had 1000 friends, that persons id would appear 1000 times in the database, each time having the id of the the person he's friends with, right?

    But how could you reciprocate that without duplicating data?
    For example, if person A has 10 friends, he will have 10 records, 1 for each friend.
    But what would be the solution for each of his 10 friends?
    Would they have a record of their own, with him listed as their friend?

    So rather than just having 10 rows, I'll have 100.
    This quickly scales up if 100 people are all friends with each other.

    Would you suggestion still apply or are there alternatives?
    I guess you could do a piece of code to first say something like:

    If person A is friends with person B, then person B cannot be made friends with person A.

    What do you think?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by stevex33 View Post
    So rather than just having 10 rows, I'll have 100.
    This quickly scales up if 100 people are all friends with each other.
    disk space is cheap, a row consisting of two integers is fairly short, and you can fit quite a few into a couple of gigabytes

    Quote Originally Posted by stevex33 View Post
    If person A is friends with person B, then person B cannot be made friends with person A.
    this depends on what the relationship means

    if the relationship is "has a crush on" then you can see that if john has a crush on mary, this does not imply that mary has a crush on john, so there could be one or two rows

    but if the relationship is "is friends with" then you would always have two rows and some people see this as an opportunity to simplify by storing only one row, but the downside of this is that in order to find a person's friends you have to look for that person in either of the two columns

    did that make sense?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,085
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by r937 View Post
    did that make sense?
    Yes it did. What I always wonder is whether it would be best to create the friendship once and create a UNION query for both directions (and have an index on both, i.e. 2 indices, one for each direction), or create the friendship twice (one for both directions) and query directly (and have an index on the first friend's id).

    Off Topic:


    I've had problems with MySQL tables crashing regularly in the past when all fields had a fixed data type (like INT). Setting the row format for the table to "fixed" solved the problem then. Just thought I'd give you a heads up.
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy


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
  •