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
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
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,
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.
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:
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.
@narayanprusty
