User Friends System & Database Design

Social media user friend system and database design: User followers database design, 2-way friend list system design

Welcome to The Scalable, blog and newsletter to help you faster in becoming tech leader. Check our tech lead roadmaps.

Before we start, kindly to subscribe, follow me on Twitter & Linkedin and share this content to your friends. Enjoy.

Quick Links:

• Intro

• User Followers System & Database Design (One-Way)

• Two-Way Friend System

  • Using is_approve Column

  • Add rule UserID1 < UserID2

  • Double Row

• Conclusion

---

Intro

One important component of a social media feature is the friend system. Having a friend feature in your product fosters social interactions and enhances user experiences, which can lead to better engagement.

I categorize the friend system into two types:

1. User Followers (one-way): This is a Twitter-like friend system.

2. Two-Way Friend: This is a Facebook and LinkedIn-like friend system.

There are several components in friend systems, including:

• Adding friends

• Approving or rejecting requests (depending on the use case)

• Friend List

• Friends of friends

In today's article, we will discuss the system and database design of both types, and we will use MySQL or Postgres as the database. We will provide an overview of the user follower system and focus more on the two-way friend system. Additionally, we will specifically address the use case of adding friends and displaying the friend list.

User Followers Database & System Design

In a user followers system, users can establish a one-way relationship where one user can follow another user without requiring mutual consent.

Twitter User Followers System

On Twitter, you can follow other users without their consent. The database design for this feature is relatively straightforward.

Both option 1 and option 2 are relatively the same; it's just a matter of perspective. Personally, I prefer the first option because it's more intuitive for me.

Instagram User Followers System

On Instagram's following system, it's a bit different because you have the option to enable approvals. However, it is still considered a one-way relationship.

To solve this, we just need to add a new column: is_approve column.

Two-Way Friend System

In the two-way friend system, mutual consent is required to establish a friendship between two users. This is similar to the friend systems found in Facebook and LinkedIn.

Here is the general system design:

Option #1: Using is_approve Column

Let's start with the first solution, where we use the same database design as Instagram's approval-based one-way user followers system. The difference lies in how we query the data.

Here are some notes you need to know to understand this schema:

• uid1 refers to the user who initiates a friend request

• is_approve == false, means the request is still pending

• is_approve == true, means both users have become friends.

Add Friend Request

// `id 10` add request to `id 15`
SELECT count(*) FROM user_friend 
WHERE (uid1 = 10 AND uid2 = 15) 
OR (uid1 = 15 AND uid2 = 10)

// If the result is 1, then the request has been made
// If the result is 0, then we could insert the request to the table
INSERT INTO user_friend (uid1, uid2, is_approve) 
VALUES (10, 15, false) 

Retrieve Pending Friend Request

SELECT * FROM user_friend 
WHERE uid2 = 10 
AND is_approve is false

Retrieve Friend List

// use OR statement
SELECT * FROM user_friend 
WHERE (uid1 = 10 OR uid2 = 10) AND is_approve is true

// use UNION statement
SELECT * FROM user_friend WHERE uid1 = 10 AND is_approve is true
UNION ALL
SELECT * FROM user_friend WHERE uid2 = 10 AND is_approve is true

Approve / Reject

// Approve
UPDATE user_friend SET is_approve is true 
WHERE (uid1 = 10 AND uid2 = 15) 
OR (uid1 = 15 AND uid2 = 10)

// Reject
DELETE FROM user_friend 
WHERE (uid1 = 10 AND uid2 = 15) 
OR (uid1 = 15 AND uid2 = 10)

Advantage:

• Easy to implement

• Uses only 1 table

Disadvantage:

• The retrieval query is not satisfactory.

  • If using an OR query, it cannot be indexed.

  • If using UNION, sorting can be tricky.

• There is no guarantee that the data will not be duplicated (e.g., 10, 15 and 15, 10 are both possible)

Option #2: Improved version, adding rule UserID1 < UserID2

This is an improved version of the first option. It is similar but with an adjustment:

• Adding the rule uid1 < uid2

• Changing the status to an enum

  • REQ_UID1: uid1 makes a friend request to uid2

  • REQ_UID2: uid2 makes a friend request to uid1

  • FRIEND means both users are friends

Add Friend Request

// `id 10` add request to `id 15`
INSERT INTO user_friend (uid1, uid2, status)
VALUES (10, 15, 'REQ_UID1');

// `id 10` add request to `id 4`
INSERT INTO user_friend (uid1, uid2, status)
VALUES (4, 10, 'REQ_UID2');

Retrieve Pending Friend Request

SELECT * FROM user_friend 
WHERE (uid1 = 10 AND status = 'REQ_UID2')
OR (uid2 = 10 AND status = 'REQ_UID1')

// OR using UNION

SELECT * FROM user_friend WHERE uid1 = 10 AND status = 'REQ_UID2'
UNION ALL
SELECT * FROM user_friend WHERE uid2 = 10 AND status = 'REQ_UID1'

Retrieve Friend List

SELECT * FROM user_friend 
WHERE (uid1 = 10 AND status = 'FRIEND')
OR (uid2 = 10 AND status = 'FRIEND')

// OR using UNION

SELECT * FROM user_friend WHERE uid1 = 10 AND status = 'FRIEND'
UNION ALL
SELECT * FROM user_friend WHERE uid2 = 10 AND status = 'FRIEND'

Approve / Reject

// Approve
UPDATE user_friend 
SET status = 'FRIEND' 
WHERE uid1 = 10 AND uid2 = 15

// Reject
DELETE FROM user_friend WHERE uid1 = 10 AND uid2 = 15

Advantages:

• Easy to implement.

• Intuitive, the data looks tidy.

• Uses only 1 table.

• Data is guaranteed not to duplicate.

Disadvantages:

• The retrieval query still requires an OR or UNION statement

Option #3: Double Row

This solution is implemented by creating duplicate rows, such as AB and BA. Users who have become friends will have two rows in the database.

To make this approach possible, we need to add a new table called friend_request. Let's check the design:

Notes:

• I suggest we use the rule of uid1 < uid2 for the friend_request table.

• In the requestor column

  • UID1: if uid1 is the one who initiate the friend request.

  • UID2: if uid2 is the one who initiate the friend request.

Add Friend Request

// `id 10` add request to `id 15`
INSERT INTO friend_request (uid1, uid2, requestor) 
VALUES (10, 15, 'UID1')

// `id 10` add request to `id 4`
INSERT INTO friend_request (uid1, uid2, requestor) 
VALUES (4, 10, 'UID2')

Retrieve Pending Friend Request

SELECT * FROM friend_request 
WHERE (uid1 = 10 AND requestor = 'UID2') 
OR (uid2 = 10 AND requestor = 'UID1')

Retrieve Friend List

SELECT * FROM user_friend WHERE user_id = 10

Approve / Reject

// Approve
INSERT INTO user_friend (user_id, friend_id) VALUES (10, 15)
INSERT INTO user_friend (user_id, friend_id) VALUES (15, 10)
DELETE FROM friend_request 
  WHERE (uid1 = 10 AND uid2 = 15) 
  OR (uid1 = 15 AND uid2 = 10) 

// Reject
DELETE FROM friend_request 
  WHERE (uid1 = 10 AND uid2 = 15) 
  OR (uid1 = 15 AND uid2 = 10) 

Advantages:

• The data is tidy and intuitive.

• Indexing can work effectively.

Disadvantages:

• Data stored becomes duplicated.

• The friend_request table acts as temporary data but is required to implement this design.

Conclusion

There are many creative ways to solve the friend system. In the user follower friend system, such as a Twitter-like friend system, it is more straightforward, as we can simply match the user_id and friend_id.

In the two-way friend system, like the friend systems found in Facebook and LinkedIn, there are more options to choose from depending on your use case. Personally, I would suggest considering the 2nd option or the 3rd option.

---

Thank you for reading today's newsletter! If you find it valuable, don’t forget to: