QNimate

  • CoursesVideos
  • WP PremiumPlugins
  • DemosLab
  • Home
  • QIdea
  • QTrack
Home Carbon Ads Database Design For Storing Chat Messages

Database Design For Storing Chat Messages

chat-app-database-design

Storing chat messages and retrieving those messages at a very fast rate is much needed for an chat app. Developers spend days to decide which DBMS to choose and how to design tables for storing messages. Sometimes they endup with difficult and bad designs. Most bad table designs require a lot of sorting. After doing a lot of research and smashing my brain I finally came up with a awesome way of storing and retrieving messages in SQL tables. My method doesn’t require any sorting. And retrieving is very fast even if you have only one server with good configuration.


In this tutorial I will be using MySQL as my database management system. But this same design can me implemented using any SQL, No-SQL or any other kind of database management system.

Total Messages Table

1

2

In this table we keep the total number of messages between two users. The identifier column will have two usernames separated by a colon. Therefore usernames cannot have colons. The identifier column is the primary key so it has unique values and the column is indexed.

You need to construct the identifier while producing SQL statement. Format of the identifier is that, both the usernames are sorted in ascending order.

Messages table

5

4

In this table we store all the messages between all users. The identifier_message_number column has identifier appended with the message number between those two users. The identifier and message number is separated by a colon. Then we store the message in the message column.

identifier_message_number column is the primary key. Therefore it will have unique values and is indexed.

Inserting Messages

While inserting a message you need to increment the total_messages value in total_messages column. After increment you need to add the message to the messages table.

Suppose user1 sended a message to user2 then the SQL statements will be,

INSERT INTO total_messages (identifier, total_messages) VALUES ('user1:user2',  1)
ON DUPLICATE KEY UPDATE total_messages = total_messages + 1;

Here we are checking if row already exists or not. If exists then increment the value otherwise add a new row and make the total messages as 1.

Now to insert the message into messages table we need to retrieve the total_messages count from total_messages table and then construct this SQL statement.

INSERT INTO messages (identifier_message_number, message, from)
VALUES ('user1:user2:4','hello', 'user1');

Here we assume its the 4th message.

Retrieving Messages

To retrieve last 4 messages between user1 and user2 we run this SQL query statement:

SELECT * FROM messages WHERE identifier_message_number IN ('user1:user2:80', 'user1:user2:81', 'user1:user2:82', 'user1:user2:83');

Here we assumed there are total 83 messages for user1 and user2.

Here identifier_message_number column is indexed therefore selection will be faster.


Conclusion

This table designs and formats makes insertion and selection very fast. This design makes it possible to run all queries in indexed columns. You can add more features and expand this tables. For questions on this leave comments. Thanks for reading.

May 29, 2014Narayan Prusty
Sidebar for Phonegap AppLocal And National SEO Techniques
Comments: 10
  1. Isu
    4 years ago

    Is it good for performance not to set FK?

    ReplyCancel
  2. Veny
    4 years ago

    How to handle the scenario when a/few message are deleted; it forces us repeated querying?

    ReplyCancel
  3. lovenaa
    5 years ago

    lovenaa395@gmail.com

    ReplyCancel
  4. Nicholas
    5 years ago

    Thank you very much man….. I somehow used a single table to achieve this but the results depends greatly on SQL knowledge. Thanks man.

    ReplyCancel
  5. Tom Precious
    5 years ago

    As much as it sound flexible i’m so not, it’s much applicable. thanks anyways you did a lovely job, can you consider posting how to implement a chatting system using node.js and socket.io and storing values in SQL that should be very useful.

    ReplyCancel
  6. Barbie
    6 years ago

    late to this party but the food looks fa&;#sticna8230tthat sweet tater in a jar, come to mama got your email…going to reply…just been…slammed, in a good way

    ReplyCancel
  7. tsemi
    6 years ago

    Will this schema be applicable to group chats if we tweak it a little?

    ReplyCancel
    • gad
      5 years ago

      testing replay

      ReplyCancel
  8. yefrem
    7 years ago

    Hello.
    I like your idea of total messages count and local message ID (last part of your combo ID). Even though it’s denormalization it should really help so thanks for useful post. But I think I will modify the scheme a little. First why not use compound indexes instead of weird IDs? They would have the same advantages but more flexibility and nice looking. Another thing is that it’s not possible (or I don’t understand how) to get all conversations for particular user. I had similar problem when designing friendship feature and here’s what I did (inspired by this post) :

    made two fields for user IDs: user_id_1 and user_id_2, first always lower
    added compound index for these fields
    at this point I can get all row for a user using ‘WHERE user_id_1 = :id OR user_id_2 = :id’, the problem is that only first condition will use index so I added another index for the second id only and use UNION for select: SELECT * … WHERE user_id_1=:id UNION SELECT * … WHERE user_id_2=:id

    this works quite well on synthetic test table, unfortunately can’t check on real DB yet

    pros:

    seems to work fast and flexible enough on test DB and according to info on the web

    cons:

    weird queries everywhere
    these queries sometimes have nested queries but they are not real big as friendship table only have several int fields
    relatively heavy inserts but I don’t care as friend status is requested much more often than changed

    Hope it will also help someone. Will think of combining two tricks in my application

    ReplyCancel
  9. Toni Tegar Sahidi
    7 years ago

    thank you very much, it does help since I’m going to build the same application

    ReplyCancel

Leave a Reply Cancel reply

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax

Narayan Prusty

I am a software engineer specialising in Blockchain, DevOps and Go/JavaScript. This is my personal blog where I write about things that I learn and feel interesting to share.

Image8 years ago 10 Comments Cordova, Databases, Web Development
Share this
0
GooglePlus
0
Facebook
0
Twitter
0
Linkedin
  • Total Messages Table
  • Messages table
  • Inserting Messages
  • Retrieving Messages
Related Articles
  • Local Database Storage using Intel XDK
  • Database Design for Analytics
  • MySQL Architecture For Large Websites
  • Storing Binary Data in Redis
  • Playing Beep Sound using Intel XDK
Our Sponsor
My Books

2014 - 2015 © QNimate
All tutorials MIT license