Thursday 9 May 2013
Facebook StumbleUpon Twitter Google+ Pin It

Facebook Style Messaging System Database Design

This post explains you how to design the Facebook Style message conversation system using PHP and MySQL. I have been working with messaging system, take a quick look at this post, how I have implemented database design tables and SQL queries. 

Database Design
To build the message conversation system, you have to create three tables such as Users, Conversation and Conversation_Reply. This following image generated by using Mysql Workbench tool.

Users Table
User table contains all the users registration details.
CREATE TABLE `users` (
`user_id` int NOT NULL PRIMARY KEY AUTO_INCREMENT ,
`username` varchar(25) NOT NULL UNIQUE,
`password` varchar(50) NOT NULL ,
`email` varchar(100) NOT NULL
);

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

Conversation Table
This table contains conversation relation data between registered users. Here user_one and user_two are FOREIGN KEY to REFERENCES users.user_id
CREATE TABLE   `conversation` (
`c_id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`user_one` int(11) NOT NULL,
`user_two` int(11) NOT NULL,
`ip` varchar(30) DEFAULT NULL,
`time` int(11) DEFAULT NULL,
FOREIGN KEY (user_one) REFERENCES users(user_id),
FOREIGN KEY (user_two) REFERENCES users(user_id)
);

Message Conversation Database Design.

Conversation Reply Table
Contains all user conversation replys data. Here user_id_fk is FOREIGN KEY to REFERENCES users.user_id and c_id_fk is FOREIGN KEY to REFERENCES conversation.c_id
CREATE TABLE `conversation_reply` (
`cr_id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`reply` text,
`user_id_fk` int(11) NOT NULL,
`ip` varchar(30) NOT NULL,
`time` int(11) NOT NULL,
`c_id_fk` int(11) NOT NULL,
FOREIGN KEY (user_id_fk) REFERENCES users(user_id),
FOREIGN KEY (c_id_fk) REFERENCES conversation(c_id)
);
Message Conversation Database Design.

Conversation List
Data relations between users and conversation tables. Take a look at the following SQL statement users table object as U and conversation table object as C . Here user_one = '13' and user_two='13' refers to users table user_id value.
SELECT U.user_id,C.c_id,U.username,U.email
FROM users U,conversation C
WHERE 
CASE

WHEN C.user_one = '13'
THEN C.user_two = U.user_id
WHEN C.user_two = '13'
THEN C.user_one= U.user_id
END

AND
(C.user_one ='13OR C.user_two ='13') ORDER BY C.c_id DESC

Conversation Last Message
Getting the reply results for conversation c_id='2' from conversation_reply table.
SELECT cr_id,time,reply
FROM conversation_reply
WHERE c_id_fk='2'
ORDER BY cr_id DESC LIMIT 1

PHP Code
Contains PHP code. Displaying username arun conversation results
<?php
$query= mysql_query("SELECT u.user_id,c.c_id,u.username,u.email
FROM conversation c, users u
WHERE CASE 
WHEN c.user_one = '$user_one'
THEN c.user_two = u.user_id
WHEN u.user_two = '$user_one'
THEN c.user_one= u.user_id
END 
AND (
c.user_one ='$user_one'
OR c.user_two ='$user_one'
)
Order by c.c_id DESC Limit 20") or die(mysql_error());

while($row=mysql_fetch_array($query))
{
$c_id=$row['cid'];
$user_id=$row['user_id'];
$username=$row['username'];
$email=$row['email'];
$cquery= mysql_query("SELECT R.cr_id,R.time,R.reply FROM conversation_reply R WHERE R.c_id_fk='$c_id' ORDER BY R.cr_id DESC LIMIT 1") or die(mysql_error());
$crow=mysql_fetch_array($cquery);
$cr_id=$crow['cr_id'];
$reply=$crow['reply'];
$time=$crow['time'];
//HTML Output. 

}
?>

Message Conversation Database Design.

Conversation Updates
Data relations between users and conversation_reply tables. The following SQL statement users table object as U and conversation_reply table object as R . Here c_id_fk = '2' refers to convesation table c_id value.
SELECT R.cr_id,R.time,R.reply,U.user_id,U.username,U.email
FROM users U, conversation_reply R
WHERE R.user_id_fk=U.user_id AND R.c_id_fk='2'
ORDER BY R.cr_id DESC



PHP Code
Contains PHP code, displaying conversation c_id=2 reply results.
<?php
$query= mysql_query("SELECT R.cr_id,R.time,R.reply,U.user_id,U.username,U.email FROM users U, conversation_reply R WHERE R.user_id_fk=U.user_id and R.c_id_fk='$c_id' ORDER BY R.cr_id ASC LIMIT 20") or die(mysql_error());
while($row=mysql_fetch_array($query))
{
$cr_id=$row['cr_id'];
$time=$row['time'];
$reply=$row['reply'];
$user_id=$row['user_id'];
$username=$row['username'];
$email=$row['email'];
//HTML Output

}
?>

Message Conversation Database Design.

Conversation Check
Following query will verify conversation already exists or not.
SELECT c_id
FROM conversation
WHERE
(user_one='13' AND user_two='16')
OR
(user_one='16' AND user_two='13')

Creating Conversation
//Creating Conversation
INSERT INTO conversation
(user_one,user_two,ip,time)
VALUES
('13','16','122.3.3.7','122.3.3.7');

//Conversation Reply Insert
INSERT INTO conversation_reply
(user_id_fk,reply,ip,time,c_id_fk)
VALUES
('13,'How are you','122.3.3.7','122.3.3.7','2');

PHP Code Creating Conversation.
<?php
$user_one=mysql_real_escape_string($_GET['user_session']);
$user_two=mysql_real_escape_string($_GET['user_two']);
if($user_one!=$user_two)
{
$q= mysql_query("SELECT c_id FROM conversation WHERE (user_one='$user_one' and user_two='$user_two') or (user_one='$user_two' and user_two='$user_one') ") or die(mysql_error());
$time=time();
$ip=$_SERVER['REMOTE_ADDR'];
if(mysql_num_rows($q)==0)
{
$query = mysql_query("INSERT INTO conversation (user_one,user_two,ip,time) VALUES ('$user_one','$user_two','$ip','$time')") or die(mysql_error());
$q=mysql_query("SELECT c_id FROM conversation WHERE user_one='$user_one' ORDER BY c_id DESC limit 1");
$v=mysql_fetch_array($q);
return $v['c_id'];
}
else
{
$v=mysql_fetch_array($q);
return $v['c_id'];
}
}
?>

PHP Code - Inserting Reply
<?php
$reply=mysql_real_escape_string($_POST['reply']);
$cid=mysql_real_escape_string($_POST['cid']);
$uid=mysql_real_escape_string($uid_session);
$time=time();
$ip=$_SERVER['REMOTE_ADDR'];
$q= mysql_query("INSERT INTO conversation_reply (user_id_fk,reply,ip,time,c_id_fk) VALUES ('$uid','$reply','$ip','$time','$cid')") or die(mysql_error());
?>
-By Parthiv Patel
Parthiv Patel
Bhaishri Info Solution
Sr. PHP Developer
Limdi Chowk, AT PO. Nar, Di. Anand
Nar, Gujarat
388150
India
pparthiv2412@gmail.com
7383343029
DOB: 12/24/1986

No comments: