Relationship design plays an important role in any social network designed to connect people. In this post, the database design of the Friends Relationship is shown. This will give you an idea of how to implement the relationship network in any social networking website irrespective of the database used.

Here, the queries provided are based on the MySQL database. It also provides the basic relationship operations like in Facebook.

Database Schema

Following is the database schema of the relationship database design.

Note: One thing you can do to speed up the retrieval and insert operations is to keep the foreign key only in design and not to implement it in the Database. You must check the constraints programmatically rather than using database constraints.

Database Schema

Users Table

This table contains 4 fields with user_id as the PRIMARY KEY, with AUTO_INCREMENT set to it.

Relationship Table

The first two fields correspond to the id of the two users who is to be related and status represents the relationship status between the two users.

Status Codes

Code	Meaning
0	Pending
1	Accepted
2	Declined
3	Blocked

Using numbers instead of strings for representing status might speed up the database operations.

Action User

The action_user_id represent the id of the user who has performed the most recent status field update.

For example,

If user with id 1 has sent a request to user with id 2, action user id will be 1. So, the user who sent the friend request will be user 1.

If user id 2 has blocked user id 3, then user id 2 will be the action user id. The user id 2 has blocked user id 3.

Unique Pair

To make the users pair insertion unique, we add an UNIQUE index to for both the user_one_id and user_two_id fields combined. This way we can avoid duplicate user’s pair to be inserted.

How it works?

The most important thing to remember when using this model is to make sure that always user_one_idis smaller than user_two_id for any operation. Following is a sample screenshot of the inserted user relationship data.

Following is a sample relationship table data

Sample relationship table data

Friend Request

Inserting a new Friend request. Friend request sent by user 1 to user 2. Once a record is inserted in the relationship table, a friend request or a relationship has been established between the two users. You can use the status and action_user_id to display the appropriate options and to block the user.

INSERT INTO `relationship` (`user_one_id`, `user_two_id`, `status`, `action_user_id`)
VALUES (1, 2, 0, 1)

Accept Friend Request

Updating the status of the friend request. Accepting friend request sent to user 2 by user 1.

UPDATE `relationship` SET `status` = 1, `action_user_id` = `2`
WHERE `user_one_id` = 1 AND `user_two_id` = 2

Checking Friendship

To check if any two users are friends use this query. Remember that always the user_one_id must be smaller than the user_two_id. If the result returns a row, then the users are friends.

SELECT * FROM `relationship`
WHERE `user_one_id` = 1 AND `user_two_id` = 2 AND `status` = 1

Friends List

Retrieve all the users’ friends. Here user 1 is the logged in user.

SELECT * FROM `relationship`
WHERE (`user_one_id` = 1 OR `user_two_id` = 1)
AND `status` = 1

Pending Request

Get the entire pending user request for the user from other users. Let’s assume user with id 1is logged in.

SELECT * FROM `relationship`
WHERE (`user_one_id` = 1 OR `user_two_id` = 1)
AND `status` = 0
AND `action_user_id` != 1

Friend request sent by the user

When the logged in user visits the profile of another user, we should be able to retrieve the friend request status. Here, user 1 is the logged in user. User 1 visits the profile of user7, the following query can be used to retrieve their status.

SELECT * FROM `relationship`
WHERE `user_one_id` = 1 AND `user_two_id` = 7

Friend Request

Here user1 has requested to be a friend to user7. Using the status we now know that they both are not friends. Use the following code template to show the appropriate status buttons.

<?php
// Database connection code, $con is the database connection var.
// Now $row contains the query result.
// Logged in user id is stored in, $current_user_id. Profile user id is shown in $profile_user_id

switch ($row['status']) {
    case 0:
        if ($row['action_user_id'] === $current_user_id) {
            // Show, "Friend request sent" button. Show options to cancel the friend request.
        } elseif ($row['action_user_id'] === $profile_user_id) {
            // Show, "Accept Friend request" button. Show options to block and reject friend request.
        }
    break;
    case 1:
        // Check and show options to unfriend the user.
    break;
    case 3:
        // Check and show options to unblock. If the other user's visit's this profile show "profile does'nt exists"
    break;
}

Here I have only provided queries only to select and update the relationship table alone.