Facebook Style Friend Request System Database Design

804

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.

Database Design
To build the friend request system, you have to create three tables such as UsersUpdates 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 01 and 2 values references to Pending Friend RequestConfirm Friend Request and You.

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 Uupdates Dfriends 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 Ufriends 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);
?>