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?

Solution

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]

Comments

[Abhishek Aggarwal]

Thank-you very much

[Abhishek Aggarwal]

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

[Devashish Mamgain]

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

[Abhishek Aggarwal]

By the way, I'm using oracle 10g

[Abhishek Aggarwal]

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 ?

[Devashish Mamgain]

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.

[The Godfather]

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