pkk pkk - 1 year ago 65
SQL Question

Design a friends table for a social networking site

Hi i am facing problem regarding the structure of a table for storing the all the friends list of an user. In the below table which i have used if user 84 has requested for friendship with user 70 & 74 then user 84 is friends are 70 & 74 and vice versa. Similarly user 77 with 84.

enter image description here

But the problem is that if i want to find out all the friends and their detail of user 84 i.e. of user (70,74,77).

Should i enter duplicate record like image 2 Ex: if user 84 send friend request to user 70 when user 70 accept the request then another row will be inserted like user_id 70 & friend_id 84.

OR is there any mysql query to join with users table based on the column value of the friends table. Like if user_id is 84 it will join based on the friend_id and if friend_id is 84 then it will join with user table by using the user_id column.

enter image description here

Answer Source

I assume that in your system friendship is symmetrical, i.e. if A is friends with B that implies that B is also friends with A. In which case, I suggest that you change your Friend table's columns to reflect the fact that the two people have equal status in the relationship e.g.


  • @ User1_Id
  • @ User2_Id

With that in place, you could do a query something like this

    (SELECT User1_Id FROM Friend WHERE User2_Id = MY_USER_ID)
    (SELECT User2_Id FROM Friend WHERE User1_Id = MY_USER_ID)

This uses a sub-select rather than a join, plus a union to get a single list of friends' user ids depending on which way around the users are listed in the Friend table. Documentation of sub-query and union.

It's probably worth keeping it like this, rather than simplifying the code by having 2 rows in your Friends table for each relationship - (A, B) and (B, A). If you have N users the Friends table will have up to N*(N-1) rows if you duplicate and half that if you don't.