Facebook Style Messaging System Database Design.
Wall Script
Wall Script
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);
?>
web notification

120 comments:

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

    ReplyDelete
    Replies
    1. Nice post, but how to manage nested level reply on a single post.

      Delete
  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. Hey. Why are you not using mysqli ? It's safer

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

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

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

    ReplyDelete
  10. 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
  11. 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
  12. u always share good article, i like it (y) :)
    thanks

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

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

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

    ReplyDelete
  16. Nice article...

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

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

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

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

    ReplyDelete
  21. 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
  22. 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
  23. 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
  24. 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
  25. Great sir, thanx for providing such a great Knowledge...Thanx....U have bookmark your site..So impressed to you..

    ReplyDelete
  26. 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
  27. What if user_two deletes the conversation? then it will be deleted for user_one to?

    ReplyDelete
  28. 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
    2. It's dont know which content belong to user_on , user_two

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

    GK

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

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

    ReplyDelete
  32. 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
  33. Facebook Style Messaging System Database Design with jsp... plzzzzz help

    ReplyDelete
  34. 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
  35. 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
  36. 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
  37. 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
  38. The legendary Srinivas delivers again, you're a genius!!

    ReplyDelete
  39. 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
  40. how can you post these conversation,so that the other user don't have to refresh the page to see it!!!!

    ReplyDelete
  41. 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
  42. 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
  43. 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
  44. 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
  45. please help me to add online chat in final year project on local host em confused which ip will use and how

    ReplyDelete
  46. 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
  47. If i am going to make this project from where to start and how to start...please help me with some tutorial videos.

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

    ReplyDelete
  49. 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
  50. How check Unread Messages ?

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

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

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

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

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

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

    ReplyDelete
  57. I need to download the code

    ReplyDelete
  58. 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
  59. Create complete CSS and HTML pages.
    By this anybody with basic knowledge can understand this script. GREAT JOB DUDE KEEP GOOD WORK.

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

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

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

    ReplyDelete
  63. how we can download source code..

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

    ReplyDelete
  65. excellent post , easily understand schema

    ReplyDelete
  66. Hello,

    any examples on 'group chat'?

    ReplyDelete
  67. 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
  68. 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
  69. 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
  70. Can You please attach the source code to download

    ReplyDelete
  71. How can we maintain deleted the conversation ?

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

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

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

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

    ReplyDelete
  76. 1. Why is time an int(11), should not that be a timestamp?
    2. What value should we insert in the ip field of the table conversation?
    IP of user_one or IP of user_two?

    ReplyDelete
  77. 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 ='13' OR C.user_two ='13') ORDER BY C.c_id DESC

    This query shows duplication conversation. why??

    ReplyDelete
  78. Sir you just ultimate, awesome work.

    ReplyDelete
  79. thank you my friend :) this is awesome :)

    ReplyDelete
  80. Thanks, great tutorial! Ignore those who are complaining they don't know anything, this is basic tutorial so it wont be exactly look like facebook but gives info on how it goes, like the concept. Thanks again sir

    ReplyDelete
  81. Hie I'm trying to built a system which use the same concept and I'm having trouble designing the database.can I ask when you put user_1 and user_2 is that not against first normal form

    ReplyDelete
  82. This comment has been removed by a blog administrator.

    ReplyDelete

mailxengine Youtueb channel
Make in India
X