Facebook Style Friend Request System Database Design
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 Users, Updates and Friends.
User table contains all the users registration details.
`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.
This table contains user status updates data. Here user_id_fk is the FOREIGN KEY to REFERENCES users.user_id
`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));
This table contains user friends relation data. Here friend_one and friend_two are the FOREIGN KEYs to REFERENCES users.user_id
`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.
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.
(friend_one,friend_two,status)
VALUES
(‘$user_id‘,’$user_id‘,’2’);
Add Friend
Use the following insert statement for adding a friend.
(friend_one,friend_two)
VALUES
(‘$user_id‘,’$friend_id‘);
Confirm Friend Request
Here confirming the friend request, updating the status 0 to 1
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.
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.
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.
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.
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.
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);
?>
