Storing messages of different chats in a single database table

Question

I am working on a chatting website. How can I store messages of 2 different chats. Do I have to create a new table for each chat, or can I have a single table storing all the chats?

Would the later approach be affected in the long run (i.e. during searching), as all the messages will be retrieved from this table every time a user opens his chat?

Answer 1

Here is what I would recommend, use only one table for storing messages, you will need few more tables for maintaining other related data. Also treat one to one chat also as group chat only difference is for end user it is viewed as 1-1 only.

Below is just the basic structure to get you started, in actual you will have to add more columns or change the structure to support data syncing, read, delivered recipients, attachments, etc

Table: User
Columns: userId, name, image and other user info columns

Table: Group
Columns: groupId, name

Table: Group_User_X
Columns: groupId, userId

Table: Message
Columns: messageId, senderUserId, groupId, content

Now, to load messages for any given user, you can simply join Group_User_X and Message table and fetch messages for the groups in which the user belong.

If you need any further help you can reach out to me at [email protected]