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

Then we store the message in the message column. The identifier and message number is separated by a colon. The identifier_message_number column has identifier appended with the message number between those two users. In this table we store all the messages between all users. Format of the identifier is that, both the usernames are sorted in ascending order. You need to construct the identifier while producing SQL statement. The identifier column is the primary key so it has unique values and the column is indexed. The identifier column will have two usernames separated by a colon. In this table we keep the total number of messages between two users. But this same design can me implemented using any SQL, No-SQL or any other kind of database management system. In this tutorial I will be using MySQL as my database management system. And retrieving is very fast even if you have only one server with good configuration. 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. Most bad table designs require a lot of sorting.

Sometimes they endup with difficult and bad designs. Developers spend days to decide which DBMS to choose and how to design tables for storing messages. Storing chat messages and retrieving those messages at a very fast rate is much needed for an chat app.

Best er database for store