Facebook Style Messaging System Database Design.
Wall Script
Follow Me:
Wednesday, May 01, 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= mysqli_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=mysqli_fetch_array($query,MYSQLI_ASSOC))
{
$c_id=$row['cid'];
$user_id=$row['user_id'];
$username=$row['username'];
$email=$row['email'];
$cquery= mysqli_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=mysqli_fetch_array($cquery,MYSQLI_ASSOC);
$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= mysqli_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(mysqli_error());
while($row=mysqli_fetch_array($query,MYSQLI_ASSOC))
{
$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=mysqli_real_escape_string($db,$_GET['user_session']);
$user_two=mysqli_real_escape_string($db,$_GET['user_two']);
if($user_one!=$user_two)
{
$q= mysqli_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 = mysqli_query("INSERT INTO conversation (user_one,user_two,ip,time) VALUES ('$user_one','$user_two','$ip','$time')") or die(mysql_error());
$q=mysqli_query("SELECT c_id FROM conversation WHERE user_one='$user_one' ORDER BY c_id DESC limit 1");
$v=mysql_fetch_array($q,MYSQLI_ASSOC);
return $v['c_id'];
}
else
{
$v=mysqli_fetch_array($q,MYSQLI_ASSOC);
return $v['c_id'];
}
}
?>

PHP Code - Inserting Reply
<?php
$reply=mysqli_real_escape_string($db,$_POST['reply']);
$cid=mysqli_real_escape_string($db,$_POST['cid']);
$uid=mysqli_real_escape_string($db,$uid_session);
$time=time();
$ip=$_SERVER['REMOTE_ADDR'];
$q= mysqli_query("INSERT INTO conversation_reply (user_id_fk,reply,ip,time,c_id_fk) VALUES ('$uid','$reply','$ip','$time','$cid')") or die(mysqli_error());
?>

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');
$db = mysqli_connect(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE);
?>
Was this article helpful?
Thanks! Your feedback helps us to improve 9lessons.info


107 comments:

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

    ReplyDelete
  2. 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.

    ReplyDelete
  3. Cool post..informative

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

    ReplyDelete
  5. awesomely done.. Thnkz bro..

    ReplyDelete
  6. this is coool...thenx !!!

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

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

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

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

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

    ReplyDelete
  12. 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

    ReplyDelete
  13. Conversation List Query Update.

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

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

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

    ReplyDelete
  17. nice design and a superb idea..wll done.

    ReplyDelete
  18. Nice article...

    ReplyDelete
  19. Excellente! :D a practicar!!!! thanks a lot!!

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

    ReplyDelete
  21. Excellent Post. helped a lot

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

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

    ReplyDelete
  24. 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.

    ReplyDelete
  25. 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?

    ReplyDelete
  26. 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

    ReplyDelete
  27. 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.

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

    ReplyDelete
  29. 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?

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

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

    ReplyDelete
    Replies
    1. no it is refer to current user who send reply.

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

    GK

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

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

    ReplyDelete
  35. 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

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

    ReplyDelete
  37. 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.

    ReplyDelete
  38. 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.

    ReplyDelete
  39. 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.

    ReplyDelete
  40. 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

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

    ReplyDelete
  42. 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!

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

    ReplyDelete
  44. 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 ?

    ReplyDelete
  45. 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.

    ReplyDelete
  46. 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.

    ReplyDelete
  47. 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

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

    ReplyDelete
  49. 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.

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

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

    ReplyDelete
  52. 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

    ReplyDelete
  53. How check Unread Messages ?

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

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

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

    ReplyDelete
  57. hi
    how to check if the message is new. like new message notification

    ReplyDelete
  58. Nice tutorial. I only wanted was database structure.

    ReplyDelete
  59. i think its not right. what happened if user1 delete his conversation ?

    ReplyDelete
  60. I need to download the code

    ReplyDelete
  61. very Great work keep it up and plz sir tell me how to store link of videos in data base and retrieve and play video in webpage when required!

    ReplyDelete
  62. Create complete CSS and HTML pages.
    By this anybody with basic knowledge can understand this script. GREAT JOB DUDE KEEP GOOD WORK.

    ReplyDelete
  63. How will you then figure out the read/unread status in above implementation

    ReplyDelete
  64. How would you take one long list of messages and insert it into a database like this?

    ReplyDelete
  65. rarely a blog helps out, but this one did..thanks!

    ReplyDelete
  66. but how to manage individual delete of conversation and conversation replies for individual user ??

    ReplyDelete
  67. excellent post , easily understand schema

    ReplyDelete
  68. Hello,

    any examples on 'group chat'?

    ReplyDelete
  69. hi its pretty cool but I need a little twisted thing I need one to many chat as well is there any link or help?
    thanks

    ReplyDelete
  70. Hi Sir
    I was astonishing looking into your blog. Its really good..
    But according to this ERD conservation possible between TWO users. So please help me ERD how to design DB for chat in group ?

    I hv developed RTC system having same ERD approach but I am looking for ERD that support :
    Chat b/w 2 users
    Chat among groups

    Waiting for your kind answer
    Cheers :)

    ReplyDelete
  71. Hi

    The ERD work perfect for 1 to 1 chat.. It will be great to have an example on Group Chat.. Or Any Idea ??

    Waiting for kind reply..

    Thanks :)

    ReplyDelete
  72. Can You please attach the source code to download

    ReplyDelete
  73. How can we maintain deleted the conversation ?

    ReplyDelete
  74. very very thanks
    how to create conversation group?

    ReplyDelete
  75. Can I get the full source code of the project please

    ReplyDelete
  76. Can we have full source code of the project please?

    ReplyDelete
  77. There is possibility to change avatar image ?
    Have a user level ?
    Thnaks

    ReplyDelete

Make in India