Question

How to model database tables for implementing Friend relationship?

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
 45  75357  45
1 Jan 1970

Solution

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

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

2008-12-18

Solution

 64

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.

2015-07-10