Database Design for Chat Application

[@asim-gandu-phenchod]

The whole video was special. You are a TOP G. Keep it up bro

[@DatabaseStar]

Thanks!

[@codexperience7951]

nice erd for chat systems, will use yours and enhance it base to what i needed. thanks to your video

[@DatabaseStar]

Glad I could help!

[@ymahtab]

Hi, great video as always. One question: in the message table, why do we need to distinguish from_number and to_number from contacts? We could have from_contact_id and to_contact_id as FKs to the contact table. I feel like one table you're missing is a user's contact list (probably need a joining table) which would let the application figure out if the message was sent to a phone number or a saved contact. Does this make sense? EDIT: ok I think you covered this at around 4:30. We want to distinguish the contact's number from the recipient number the message was sent to at the time.

[@DatabaseStar]

Good question, yeah that's right!

[@gabrielcosta1159]

Hey man, nice video! I'm new to this database thing, so I got a question that might be a bit stupid hahaha. If I want to send a message to only one contact, how would that work since the table contact is only linked directly to the table "group_member"? I mean, I would have to use the "group_member" table even when the message is not sent to a group, right? I'm kinda confused :x

[@DatabaseStar]

Hi Gabriel, good question! Yes I think it would use the same concept, even if it is just to one contact. The group would have just the one contact.

[@gabrielcosta1159]

@@DatabaseStar Oh ok.. Thank you so much!

[@shankhashubhradas1586]

I also got the same doubt. Thanks @gabrielcosta1159 for asking and @DatabaseStar for answering 👍

[@NadjibSamsung]

I think this design is vulnerable. Because any authenticated user can alter the value of the sender id. The solution is to verify that the value of the sender id is the same value of the user id stored in the sessions store.

[@DatabaseStar]

Good point. This is just the database and there would be more involved in the application on authentication and ensuring people can only do the things they need to do. There would be no way for a user to edit IDs in a table.

[@johnnychin9598]

Sir, do you have a video were in the product item have different price depending on the customer. Say

[@DatabaseStar]

I don't have a video for that, but you could enhance an existing database to do this.

[@sidforreal]

Why contact and group_member is not a many to many relationship? One group can have multiple contacts, and one contact can be present in multiple groups

[@DatabaseStar]

That's a good point - it should be many-to-many actually. And therefore it would need a joining table between the two. Thanks for pointing it out.

[@Иван-д6ф6с]

Hi, could you please extend your tutorial to include case how do you go about handling "media" attachments, "last seen", "read by" please.

[@DatabaseStar]

Good idea!

[@idkanymoreman]

awesome stuff.. thanks for the clear explanation.. one question.. as the table grows, wont it be slower to query and fetch the chat messages if we store them in one table?

[@DatabaseStar]

Good question. It would get slower eventually, but databases are very good at handling large amounts of data. A table with a million rows is quite manageable, for example. You can also add indexes, and other performance improvements, to help your queries.

[@thebulletkin8393]

In the group_member table, I thought every record would need to have a primary key in order to be uniquely identifiable, but that table only has two foreign keys. Do those keys also serve the same purpose as a primary key then?

[@DatabaseStar]

Good point! Yes the combination of both columns should be unique. This could be done as a primary key (both columns would be included in the primary key) or as a unique constraint.

[@jatinsutaria7992]

Good stuff. However, how would you deal with figuring online users who need to be send messages to? Also, offline users, when they get online - how would this design help dispatch the missed messages to these users?

[@DatabaseStar]

I think regardless of whether they were online or offline the message would still be stored in the system. Perhaps there could be some code in the application to display all messages since the user last logged in and show them as new messages. I don’t know how you would determine if a user is online or offline though.

[@lost-mar-ble]

I dont understand this towards the end...how can contact and group member have one to many relationship?

[@DatabaseStar]

Good question. It’s because a contact can be in multiple groups in a chat application. A group for family and a group for their sports club friends and a group for other friends, for example.

[@acbusiness2023]

Really impressed... love this stuff...

[@DatabaseStar]

Thanks!

[@BaoNguyen-yg3vp]

With messages being stored persistently and the above relational data model. Which database is most suitable for chat app?

[@DatabaseStar]

I’m not sure. You could get started with any sql database and it should work, I think.

[@palspal2329]

Are u sure a sql db would be fine for this usecase?

[@DatabaseStar]

Yes it should be fine to use for this kind of design.

[@practicalbong1497]

if we want single user to send a message and or send to to a group how will i do that here ?

[@DatabaseStar]

To do that, you would add a record to the message table with the details of the message, with a link to the conversation. This conversation could have one or many people in it.

[@rockon7478]

I am wondering if this database design was for the on-device database like of watsapp or for the central hosted database ??

[@DatabaseStar]

The idea was that it would be the central hosted database, but the on-device database could be different.

[@thongnguyen7354]

How do i differentiate single chat and group chat? tks

[@DatabaseStar]

This design assumes that a chat is the same regardless of how many participants. It could have two people (a single chat between two people) or three or more (a group chat).

[@thongnguyen7354]

@@DatabaseStar Thank you for answering my question, so how can I tell the difference?

[@rahulraj94391]

​@@thongnguyen7354 get the count, if count. > 2 - it means it is a group else - it is a converrsation between only 2 people. Select Count(conversation_name) from conversation where conversation_id = "REQ ID".

[@sandiladhikari7903]

@@thongnguyen7354 for starters, you could see how many contact_id are there in group_member table, if its just 2, then its a single chat, if more than 2, the its actually what we generally understand as a group. Basically single chat between two people is also a group with only two participents. That is the concept. You could tell the difference only looking at participents. On a second approach, I don't see anything wrong with two table system for single chat and group chat feature.

[@aalzahrani9271]

I have three users and each user has a table. How will I do that here ?

[@DatabaseStar]

Is there a way you can use a single table for all of your users, and each of the users have separate IDs? If not, then you could relate each of the three user tables in the same way as you see the user table in this design. It wouldn't be as robust, because you would have three foreign keys, each to a different table, but it could work.

[@aalzahrani9271]

@@DatabaseStar What about creating users table as superclass and the other three tables that have been created will be subclasses?

[@ron-95]

hey can you make bridge table with 2 EAV data model?

[@DatabaseStar]

Yeah you probably can!

[@falseloop]

What about temporary like 24hr story system

[@DatabaseStar]

You could add a status for each message to store whether it has expired (past the 24hr timeframe) or permanent. Or you could have the application calculate it based on the time it was sent.

[@medilies]

In this case how to identify the sender?

[@DatabaseStar]

You can match the message.from_number to the contact.phone_number to see who the sender is.

[@john-zu3uo]

Thanks for this video sir!!

[@DatabaseStar]

Most welcome!

[@take_a_lunch]

Will searching or filtering messages slow down if more than a 100,000 messages are inserted into the database? What are the consequences of this, and how can it be resolved?

[@DatabaseStar]

I don't think it would slow down. Databases are designed to handle large amounts of data, and even if you had millions of rows, the performance would still be manageable. You could use indexes to ensure the data can be filtered if needed, and there are other database-specific features that could be used.

[@take_a_lunch]

@@DatabaseStar ohhhh....そか

[@fuddyduddy]

@@take_a_lunch Don't pretend to be a Japanese. Chinese.

[@nirajpatil2326]

@@fuddyduddy damn 😂😂

[@iremozen7172]

that is a great video!! thank you. could you make a video for a simple delivery application?

[@DatabaseStar]

Thanks! What do you mean by a “simple delivery application”? Like a food delivery service or something else?

[@iremozen7172]

@@DatabaseStar exactly, food delivery app would be awesome

[@Asurow]

what if Message table have message_id, message_text, from_contact_id , to_contact_id, send_datetime and Group_member_Id fk . Group_member table have Group_member_Id , message_id fk and Contact_id fk.

[@DatabaseStar]

Yeah, that could work, but I think it would only allow a message to be sent to a single contact.

[@juhairahamed5342]

Good explanation

[@DatabaseStar]

Thanks!

[@eugenefedorov3498]

But it will be a crazy query to get only 20 last msgs for specific conversations id, as it will need to filter a table from millions and millions other msgs. I dont know the right way right now, only know few with some pros and cons. Save all msgs as a json in field. But then it will be really hard to write new msgs each time. Other idea is to create for each conversations new table. But then it means we will have millions of tables in data base. But still i think this is the best aproach.

[@DatabaseStar]

That's good you're considering other options. The query itself won't be too crazy. It would be something like this (in MySQL): SELECT message_id, from_number, message_text, sent_datetime FROM message WHERE conversation_id = X ORDER BY sent_datetime DESC LIMIT 20; The database shouldn't have an issue looking through millions of messages if you have an index on the conversation_id column. If it does, you can then look into other performance strategies.

[@yertayyeras6160]

So sorry, but I think it is bad design for chat. Because you wanna say, If I wanna open message list who wrote me or I, I need to check only column from_contact? but if another person wrote me, how should I get it?

[@DatabaseStar]

Thanks for the feedback! If you want to see the list of messages that were sent to you, you can query the message table. If you want to display a list of your conversations, like the main screen in WhatsApp, you would query the conversation table and perhaps the contact table. If you want to see the messages in the conversation, you would query all tables but most of the data would be in the messages table.

[@yertayyeras6160]

@@DatabaseStar thank you for the explaining. But I have one question. What need to do, when one of the user deleted chat. It means this user deleted chat only for him/her. But second user should see old messages. Because first user deleted only for him/her sef

[@DatabaseStar]

Good question. It would depend on how you want the application to behave. If you want to have this functionality, you could add a field to indicate whether the chat is deleted or hidden for a particular user. The chat would still exist, so other users can see it, as you mentioned.

[@yertayyeras6160]

@@DatabaseStar ohh, I got it. You didn’t add this functionality? It is my bad, I thought you added also this part, and I tried to understand that part, because, in my project I need this lol

[@hanatw-gj5ei]

شكراا

[@DatabaseStar]

You're welcome!

[@mohammadrezarajabali7591]

Thanks...

[@DatabaseStar]

You're welcome!

[@yenegew-tech]

Great

[@DatabaseStar]

Thanks!