Database design: Best table structure for capturing the User/Friend relationship? [closed]

Question

I'm trying to design a data model that denotes one user being the friend of another user. This is what i've come up with so far, but it seems clunky, is there a better solution?

User ===== Id Name etc...  UserFriend =========== UserId FriendId IsMutual IsBlocked 

Answer 1

UserRelationship ==== RelatingUserID RelatedUserID Type[friend, block, etc] 

Agree that mutuality doesn't belong as a column; breaks normalization.

Answer 2

To go one record per two users and avoid consuming extra memory that the proposed methods suggest (twice as much as needed, since there are two records for each user), you can do the following:

1. Table structure:

   USER_RELATIONSHIP {     user_first_id,     user_second_id,     type      primary key(user_first_id, user_second_id) } 

2. Ensure: user_first_id < user_second_id

3. The most interesting part - type: for all of the possible states of a relationship, you create the corresponding values. For exmaple:

   pending_first_second pending_second_first friends block_first_second block_second_first block_both 

What you have:

1. The user_first_id < user_second_id ensures that there is only one record of a relationship between two given users, since this and primary key constraints won't allow placing it otherwise.
2. By proper switching between the relationship states, you determine how each of two users relate to each other. If there is no relationship between two users, there is no record.

3. To find out the relationship between two users (as well as update), you simply go by a single query:

   select * from USER_RELATIONSHIP where     user_first_id = user1_id and     user_second_id = user2_id; 

   without an or statement that would check this columns vice versa, which is faster.

Example scenario:

1. no record : are not in a relationship

2. pending_first_second : the first made a friend request to the second

3. friends : the second approved the friend request

4. no record : one of the users removed the other from his firends

This solution is efficient in terms of both memory and speed, because you create, store and update only one single record.