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?

Devashish Mamgain · Accepted Answer · 2022-11-11T07:43:51.173

17

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 support@kommunicate.io

edited Nov 11 '22 at 07:43

answered Oct 01 '16 at 19:00

Devashish Mamgain

2,077
1
18
39

Thank-you very much – Abhishek Aggarwal Oct 02 '16 at 21:50
I still have one confusion.. the length of each message could vary from just a HI to a long message.. so what datatype should i take for the message content – Abhishek Aggarwal Oct 02 '16 at 21:52
1

if you are using mysql, you can use text http://boolean.co.nz/blog/max-length-for-mysql-text-field-types/135/ – Devashish Mamgain Oct 03 '16 at 06:42
By the way, I'm using oracle 10g – Abhishek Aggarwal Oct 03 '16 at 08:52
so, you're saying that I should use big size datatype, even if it stores small texts as well. This seems to be wastage of memory space given for each record. Will there be any solution to this problem or I have to take big size datatype ? – Abhishek Aggarwal Oct 03 '16 at 08:55
it depends on your requirement, if you want to restrict chat messages length then use varchar, if you want to allow users to post long stories then use text. – Devashish Mamgain Oct 03 '16 at 14:26

score -3 · Answer 2 · answered Feb 06 '19 at 10:40

Server Side Database for chat application

The data transmission between the client and server is processed with a single token key. Here the server-side scripts run on the server instead of the client-side server in order to deliver the content (message) in response to the action carried in the user device.

Server side database architecture

Client-side database for chat application

The illustration explains the message transmission flow where the servers serve transmission of data to the client device by accessing the token keys. Further, the client device sends the request to multiple servers to access the message with the client-side token key.

Client side database architecture

The pictures you linked are not showing anything related to database or database structure but rather show very-very high-level structure of the whole system. That doesn't answer the OP's question — The Godfather, May 16 '20 at 13:09

Storing messages of different chats in a single database table

2 Answers2

Linked