Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jan 2009
    Posts
    23

    Question how is friendship stored in database

    I am always thinking one question. on those SNS sites like Facebook, how are the relationships among friends stored in database. there may be millions of users registerred on the site and each user may have thousands of friends. if the relationship is considerred as a entity, a huge number of instances will be stored in the database. so I am thinking if there may be a way to represent the friendship and also less store spaces are needed.
    does anyone have an idea about it
    Last edited by chiefman; 01-15-09 at 04:28.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Users(user_id, name, email, etc)
    Friends(user_id, friends_user_id, how_do_you_know_eachother, etc)
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2009
    Posts
    23
    it makes sense. anyway I'd like to suppose a scenario. A and B are friends. thus there is a record (A, B, ...) in the table Friends which represents A has a friend B, but how about the other side B has a friend A. should I add another record like (B,A,..) to the table. if so, I think the data is duplicate

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by chiefman
    it makes sense. anyway I'd like to suppose a scenario. A and B are friends. thus there is a record (A, B, ...) in the table Friends which represents A has a friend B, but how about the other side B has a friend A. should I add another record like (B,A,..) to the table. if so, I think the data is duplicate
    You could easily have 2 indexes on the same table
    Code:
    create clustered index my_main_idx on Friends ( user_id, friends_user_id )
    create index my_main_idx on Friends ( friends_user_id, user_id )
    Which means you wouldn't have to store the data twice but it does mean you'd have to do 2 searches of the table to get all the friends of A. The biggest problem though is that friendships aren't necessarily a two way thing: say A has declared 2 friends B & C. Then D comes along and declares A as his friend. A will suddenly now find he has 3 friends B,C & D even though he hasn't declared D as his friend. A will probably also find he won't be able to delete D from his list of friends either.

    Just my 2c.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Why do you have to do two searches?

    All friends of A
    Code:
    SELECT a.name As [user]
         , b.name As [friend]
    FROM   users a
     INNER
      JOIN (
            SELECT user_id As [f1]
                 , friend_user_id As [f2]
            FROM   friends
            UNION ALL
            SELECT friend_user_id As [f1]
                 , user_id As [f2]
            FROM   friends
           ) As [f]
        ON a.user_id = f.f1
     INNER
      JOIN users b
        ON f.f2 = b.user_id
    WHERE  a.name = 'A'
    There's undoubtebly betteer ways, but i just knocked this up quickly to illustrate.
    George
    Home | Blog

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    the only effective way I can see of identifying freindshops is to have an intersection table with both FKS to person.

    the problem is how people perceive such friendships

    A may think they are frends with B, but B may think they are acquaintences, or colleagues or similar. ie its possible that the realtionship type may not be the same for two people.

    eg BillyNoMates is desparate to be friends with anybody
    but only one or two real poeple are freinds with BillyNoMates, and most of those are probably forced to becuase they are family or employee
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by georgev
    Why do you have to do two searches?
    I wasn't refering to the number of different select statements but to how many searches of the indexes the database optimiser would need to do to get your complete list of friends. While it's easy to put these queries into one query that certainly doesn't make it any faster.

    Also, was that the simplest search you could come up with for that query??? it looks a triumph of NZDF coding

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mike_bike_kite
    A will probably also find he won't be able to delete D from his list of friends either.
    you're not an active facebook user, are you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by healdem
    the only effective way I can see of identifying freindshops is to have an intersection table with both FKS to person.

    the problem is how people perceive such friendships

    A may think they are frends with B, but B may think they are acquaintences, or colleagues or similar. ie its possible that the realtionship type may not be the same for two people.

    eg BillyNoMates is desparate to be friends with anybody
    but only one or two real poeple are freinds with BillyNoMates, and most of those are probably forced to becuase they are family or employee
    I suppose when we start to draw Venn diagrams of our friends just to find out which are the real ones then we have to realise we've hit rock bottom

    Quote Originally Posted by r937
    you're not an active facebook user, are you
    I don't think so.

  10. #10
    Join Date
    Mar 2008
    Posts
    89
    Here's another thought:
    What's with friend-requests?

    For example:
    A requests to be friends with B, but needs to wait until B approves.
    You want to record this status as a "possible (mutual) friendship" and then convert it into a "mutual" friendship when approved.

    My first thought would be to simply add another column to the friends table as proposed above, like
    Friends(user_id, friends_user_id, how_do_you_know_eachother, status),
    where "status" could have values like "requested" and "approved".

    Hm...
    "My brain is just no good at being a relational Database - my relations suck real bad!"

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by chiefman
    how is friendship stored in database
    Possibly my favorite thread title ever.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Jan 2009
    Posts
    23
    Quote Originally Posted by freeBatjko
    Here's another thought:
    For example:
    A requests to be friends with B, but needs to wait until B approves.
    You want to record this status as a "possible (mutual) friendship" and then convert it into a "mutual" friendship when approved.

    My first thought would be to simply add another column to the friends table as proposed above, like
    Friends(user_id, friends_user_id, how_do_you_know_eachother, status),
    where "status" could have values like "requested" and "approved".

    Hm...
    if B refuses the request from A, should the record be removed then?
    I found Facebook's database schema from the Internet, and there is a table named FriendRelation. property "AreFriends" should identify the relationship between two people and I guess its type is boolean
    Attached Thumbnails Attached Thumbnails facebook database schema.jpg  

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by chiefman
    I found Facebook's database schema from the Internet
    that's pretty cool

    do you remember or did you save the url?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jan 2009
    Posts
    23

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks, i bookmarked the blog entry
    Facebook Object-Oriented Diagram
    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
  •