Facebook Style Friend Request System Database Design.

{ 84 comments }

I received few tutorial requests from my readers that asked to me, how to design Facebook style friend request system using MySQL. I had posted few tutorials about twitter style friend follow and follower concept designs, Facebook friend system is involved with few pending steps such as sending a request and accepting a request. Here I have discussed how to design a database and implementation of SQL queries, hope this post will help you to understand the friend system.

Previous Tutorials:Facebook Style Messaging System Database Design.

Database Design
To build the friend request system, you have to create three tables such as Users, Updates and Friends.

Users Table

User table contains all the users registration details.

CREATE TABLE `users` (
`user_id` INT(11) NOT NULL AUTO_INCREMENT ,
`username` VARCHAR(45) ,
`password` VARCHAR(100) ,
`email` VARCHAR(45) ,
`friend_count` INT(11) ,
`profile_pic` VARCHAR(150),
PRIMARY KEY (`user_id`));

Data will store in following way, here the password data encrypted with MD5 format.

Updates Table

This table contains user status updates data. Here user_id_fk is the FOREIGN KEY to REFERENCES users.user_id

CREATE TABLE `updates` (
`update_id` INT(11) AUTO_INCREMENT ,
`update` VARCHAR(45),
`user_id_fk` VARCHAR(45),
`created` INT(11) ,
`ip` VARCHAR(45),
PRIMARY KEY (`update_id`),
FOREIGN KEY (user_id_fk) REFERENCES users(user_id));

Friends Table

This table contains user friends relation data. Here friend_one and friend_two are the FOREIGN KEYs to REFERENCES users.user_id

CREATE TABLE `friends` (
`friend_one` INT(11) ,
`friend_two` INT(11) ,
`status` ENUM('0','1','2') DEFAULT '0',
PRIMARY KEY (`friend_one`,`friend_two`),
FOREIGN KEY (friend_one) REFERENCES users(user_id),
FOREIGN KEY (friend_two) REFERENCES users(user_id));

Here status 0, 1 and 2 values references to Pending Friend Request, Confirm Friend Request and You.

Friend Request System Database Design.

User Registration
This statement is required for user registration, here status 2 represents to you. This helps while retrieving friend updates along with your updates.

INSERT INTO friends
(friend_one,friend_two,status)
VALUES
('$user_id','$user_id','2');

Add Friend
Use the following insert statement for adding a friend.

INSERT INTO friends
(friend_one,friend_two)
VALUES
('$user_id','$friend_id');

Confirm Friend Request
Here confirming the friend request, updating the status 0 to 1

UPDATE friends
SET status="1"
WHERE
(friend_one="$user_id" OR friend_two="$user_id")
AND
(friend_one="$friend_id" OR friend_two="$friend_id");

Checking Friend
Here friend_one represents the friend request owner.

SELECT 'friend_one','friend_two','status' FROM friends
WHERE
(friend_one="$user_id" OR friend_two="$user_id")
AND
(friend_one="$friend_id" OR friend_two="$friend_id")

PHP Code
This contains simple PHP code, this helps you to display the proper friend request related buttons.

<?php
include 'db.php';
$user_id=user session value;
$friend_id=frined id value;
$result=mysqli_query($connection,"SELECT 'friend_one','friend_two','status' FROM friends WHERE (friend_one="$user_id" OR friend_two="$user_id") AND (friend_one="$friend_id" OR friend_two="$friend_id")");
$row=mysqli_fetch_array($result,MYSQLI_ASSOC);

if($row['friend_one']=='$user_id' && $row['status']=='0')
{

}
else
{

}
?>

Friends Updates List
Data relations between users, friends and updates tables for friend feed results. The following SQL statement users table object as U, updates table object as D and friends table object as F . Here $user_id reference to user session value and $friend_id is reference to friend user row id value.

SELECT U.username, U.email, D.update_id, D.update, D.created
FROM users U, updates D, friends F
WHERE
D.user_id_fk = U.user_id
AND

CASE
WHEN F.friend_one = '$user_id'
THEN F.friend_two = D.user_id_fk
WHEN F.friend_two= '$user_id'
THEN F.friend_one= D.user_id_fk
END

AND
F.status > '0'
ORDER BY D.update_id DESC;

Friends List
Data relations between users and friends tables for displaying user friends. Take a look at the following SQL statement users table object as U and friends table object as F . Here user_id is the login user session value.

SELECT F.status, U.username, U.email
FROM users U, friends F
WHERE
CASE

WHEN F.friend_one = '$user_id'
THEN F.friend_two = U.user_id
WHEN F.friend_two= '$user_id'
THEN F.friend_one= U.user_id
END

AND
F.status='1';

db.php
Database configuration file, modify username, password and database values.

<?php
define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'username');
define('DB_PASSWORD', 'password');
define('DB_DATABASE', 'database');
$connection = mysqli_connect(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE);
?>