Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2010
    Posts
    2

    Unanswered: Storing 'Friend List'

    A friend list isn't what I'm going for, but it's the same 'situation', and it saves having to first explain my idea.

    I'm just fooling around learning php\mysql and I cannot figure out how to design it.

    There are two ideas I have, but frankly I definitely don't think either are good:

    1. Have a column for 'friends' for each user and add each new friend in the column. Then I'd have to parse the information every time from the table (which definitely sounds like a bad idea)

    2. Simply store every accepted friend into a separate table. This kind of seems like it'd be hectic in the long run though. For this fooling around\learning project it'd work, but if I ever move onto 'real' projects idk if this would be best.

    Surely there is some way to do this that I'm not thinking of?

    Thanks!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Option 2 for sure.
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    My reading of Option 2 would be a deffo don't use Option 2, you dont want to be having separate tables for each friend.

    friends are people/persons (unless you live in the hills where anything on two or 4 legs is accepted as fair game)

    so I'd suggest you have a table which identifies people
    and another table which associates specific people with other people, lets be dull an call that table friendships. it has the foreign keys that point back to the person table

    eg
    table: Persons
    ID: an autogenerated number identifying this record
    Title:
    Forenames:
    Surnames:
    KnownAs:
    ....and so on

    table: Freindships
    PersonID: a foreign key to Persons pointing to a Persons.ID
    IsFreindsWith: a foreign key to Persons pointing to a Persons.ID

    make PersonID and IsFriendsWith the primary key in Friendships so that means the same person cannot be friends with the same person more than once
    PersonID and IsFriendsWith must be not null and the same datatype as the Person.ID

    you may want to consider if a person can be friends with themselves, or whetrehr you want to put a rule that says PersonID and IsFriendsWith cannot be the same

    there is an exposure that Person A may be registered as a friend with Person F, and you may also get Person F may be registered as a friend with Person A

    retrieving the details of friendships becomes relatively trivial
    to find who is freinds with Person A you issue a select that looks for Person A's ID in either Friendships.PersonID or Friendships.IsFriendsWith
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by healdem View Post
    My reading of Option 2 would be a deffo don't use Option 2, you dont want to be having separate tables for each friend.
    Ooh, I hadn't realised that was option 2!

    In which case, yeah, definately don't use that option either!
    George
    Home | Blog

  5. #5
    Join Date
    Aug 2010
    Posts
    2
    Thank you Gvee and Healdem.

    That's what I meant for option 2, but I must have made it unclear trying to keep it short! Definitely appreciate the overview of it though.

    Time to go and get this all working now.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by healdem View Post
    there is an exposure that Person A may be registered as a friend with Person F, and you may also get Person F may be registered as a friend with Person A
    What about a check constraint for PersonID < IsFriendsWith ?
    George
    Home | Blog

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    To extend on my above suggestion, pseudo code for inserting records to fir that logic:
    Code:
    SELECT Min(person) As PersonID
         , Max(person) As IsFriendsWith
    FROM   (
            SELECT @person1 As person
            UNION ALL SELECT @person2
           ) As x
    George
    Home | Blog

Posting Permissions

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