9lessons programming blog
Loading Search
9lessons Bring Friends
Wall Script
Follow Me:
Wednesday, May 1, 2013

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 at labs.9lessons.info, take a quick look at this post, how I have implemented database design tables and SQL queries. Login at labs.9lessons.info and try this live demo.

Message Conversation Database Design.


Live Demo

Previous Tutorials:Database Design Create Tables and Relationships with SQL

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, conversation_reply R
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.c_id=R.c_id_fk
AND
(C.user_one ='13OR C.user_two ='13') ORDER BY C.c_id DESC

Message Conversation Database Design.

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

Message Conversation Database Design.

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());
?>
Comments
{ 78 comments }
Darwin Luague said...

Hi sir nice post...but I have one question is it ok to loop a mysql query?

Anonymous said...

nice

Denis sellu said...

I have been meaning to do something like this, and go even further by using APE(ajax push engine) to make have a full chat system implementation. Thanks for the post.

Anonymous said...

Awesome!!

Anonymous said...

thx!

Jignesh said...

Awesome....

Muhammad Mahtab Saleem said...

very nice ,,

LadyArch3r said...

very helpful thx!

Siva prabu said...

Nice dear ! !

Anonymous said...

Cool post..informative

Anonymous said...

Interesting, but getting the basics wrong like using mysql_query and md5() as "encryption" for passwords, makes me question everything you've written...

Balaji said...

This is awesome!

Anonymous said...

awesomely done.. Thnkz bro..

Anonymous said...

gud tutorial

Mark said...

Great post!!!

ELUX said...

this is coool...thenx !!!

pacific said...

very well

Subin Siby said...

Hey. Why are you not using mysqli ? It's safer

Saurabh Agarwal said...

This is pretty cool...Thanks a lot.
how can we add multiple users in a conversation?

Anonymous said...

You don't need three Table
just tow table work fine

Anonymous said...

Congratulations, but if I wanted to have a conversation with multiple users as facebook. ;)

sugarcrm development said...

I truly love your design and style of blog. I added this to my favorites blog checklist and will probably be coming again soon.

Lakshminarayanan Sampath said...

why do we have 2 users id in the conversation table?
is it better to have just 1 row with the conversation id and user id

Yashwant Chavan said...

Nice article...

Srinivas Tamada said...

Conversation List Query Update.

Rendra Kurniawan said...

u always share good article, i like it (y) :)
thanks

custom php application development said...

superb design. i like it too much. it's look like a new revolution in message system.

Anonymous said...

Tussi Great Ho!!!! :) ..Thanks :)

manish said...

nice design and a superb idea..wll done.

Anonymous said...

Nice article...

Anthony said...

Great post!!!

Juan Camaney said...

Excellente! :D a practicar!!!! thanks a lot!!

Juan Camaney said...

good!, interesting, but how it be to add more users to convertation?

deepak sharma said...

nice one

Sharath said...

Excellent Post. helped a lot

Manjeet said...

Nyc post...

Yasir Naeem said...

nice post.
But this chat script not a real time like a facebook chat system.

Anonymous said...

What tool you are using for designing a MYSQL database schema?

Anonymous said...

how i can delete my conversation withou deleting the recipient's messages?
If i delete mine then the messages will be also deleted from the receiver inbox.

Arunkumar said...

The post is fine but i have a question. when i Displaying username arun conversation results the sql query is not showing the correct username. how can i solve this?

Anonymous said...

nice work, but it isn't like facebook chat , you should have friend list and refresh it from time to time to check which one is offline or online now , and the message body should be refreshed every time to make it real chat

Vladimir Prokofiev said...

Best.

Anonymous said...

very good

Anonymous said...

Dont really like the fact that a conversation is only between to people. In my oppinion your conversations table should only have contained one column with relation to a users, so that you could add as many users to a conversation as youd like.

Alok Krishali said...

Great sir, thanx for providing such a great Knowledge...Thanx....U have bookmark your site..So impressed to you..

Fire crow said...

you rock dude !!

Admin said...

What if i delete one of the messages? If i delete my message, it will be non-visible for other user too. Isn't this a problem?

Anonymous said...

What if user_two deletes the conversation? then it will be deleted for user_one to?

sumit kumar pradhan said...

i hava a doubt on Conversation Reply Table
where user_id_fk refer to which attribute ?(user-one and user two)

Anonymous said...

Excellent Post. easy to understand and easy to implement..

GK

miraj said...

hey can u pls give a link to download files for this post?it would be a grt help...

Otisplus said...

great post, what happen with big data? query will slow? are you check it?

pixy said...

Awesome tutorial... I really don't understand what makes some people here question about the security ?? are they not aware that mysql 5 + has an integrated system which quells all the possibilities of hacking, including injections.. it's totally safe and secure! unless you use sessions instead of passing variables directly as a link

Anonymous said...

Facebook Style Messaging System Database Design with jsp... plzzzzz help

Stefan Froelich said...

So many things are wrong with this. Mysql extensions, no deletions, no seen/unseen tracking, complex query required to find conversations with recent replies.

This is certainly not Facebook method. It won't offer enough features or scale properly if u decide to implement them with the current schema.

You can fix many of your headaches by enforcing that user_one is always smaller than user_two.

Anonymous said...

Thank your for this post.
I do however would like to know, how would you manage reply (or conversation) being viewed (or not yet viewed) by the person receiving the message.

Narayan said...

This database model will not work with large number of messages.

Look at this code:

SELECT cr_id,time,reply
FROM conversation_reply
WHERE c_id_fk='2'
ORDER BY cr_id DESC LIMIT 1

c_id_fk is not indexed. so It will look for the whole table. A table can contain millions of messages. so millions of rows. This complete design is bad.

Ashok kumar said...

This conversation not like facebook, facebook conversation table structure is different check once in facebook , user1 deletes all conversation b/w user1 and user2 , if user2 can see conversation but user1 cannot see conversation but in your program anyonce can has the authority to delete msg , we do not know who is deleting message because anyone can delete so information may lost

Jose Apollo said...

The legendary Srinivas delivers again, you're a genius!!

Jose Apollo said...

To all the people complaining, Srinivas has taken the time out to deliver this tutorial as guide line on how a messaging system works. It's up to you to optimise and secure it. Goodness sakes at least do some work. Great tutorial!

Eyaaz Hosein said...

how can you post these conversation,so that the other user don't have to refresh the page to see it!!!!

Anonymous said...

Hi Srinivas. Thanks a zillion. To all others who are questioning/complaining, why don't you put an effort into resolving your own queries yourself ?

Alfonso M said...

Nice tutorial, It goes inline with what I've developed so far. One thing you are missing is a status for the conversation (read/unread/deleted), a status that needs to be set for each user.

Thai Phong said...

Hi there, I have read your confirmation. The big problem is one of them delete their conversation then the conversation will be non-visible for other user. We can add 2 columns are :
- user_on_status TINYINT(1)
- user_two_status TINYINT(1)
With this status equal 0 you can control the conversation. If user_one delete , We will set user_on_status = 0 instead of delete permanent.

Atif Ali Khan said...

gr8

German Enrique Sanchez Quintero said...

Consider this example, if the person "A" writes a message to the "B" person, and the person "A" deletes the message, the "B" person will also erase the message, as would be done in these cases

Anonymous said...

very nice.

Anonymous said...

please help me to add online chat in final year project on local host em confused which ip will use and how

Anonymous said...

Thanks!

Mustafa öztürk said...

I've developed it. This script is getting of the users message area only. Incoming messages can not see on your profile page ( Like Facebook ). You need to go to the messages.php page to see the messages. But i like srinivas database design.

Mohsin Khan said...

If i am going to make this project from where to start and how to start...please help me with some tutorial videos.

A.K Sarswati said...

its very helpful.. thanks a lot

erichagumimana said...

Sir if you want to count unread message can you help us plz!!!!

Anonymous said...

Curious about how u handle cancellations in this scenario...If A remove a message, B won't be able to see it anymore. Everybody should be able to delete messages in their "inbox" without permit the interlocultor to know about this action ;) Start from real situations instead of build db schemes drawing tables

Anonymous said...

How check Unread Messages ?

joske said...

if i test the php for showing messages evreything stays blank?
can somebody help with the html is need to use?

himanshu said...

any body help to find all messages sent by other user of status 0 when user 2 want to show his replies.

Dado said...

One of the best tutorials about messaging, saved my ass and time :D

Post a Comment