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?

Irfan

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]

Addicted

Thank-you very much – Irfan

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 – Irfan

if you are using mysql, you can use text boolean.co.nz/blog/max-length-for-mysql-text-field-types/135 – Addicted

By the way, I'm using oracle 10g – Irfan

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 ? – Irfan

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. – Addicted

Answer 2

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

Pires

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 – Scrouge