Database Design for Chat Application
Vložit
- čas přidán 30. 06. 2024
- 📝 Get my free SQL Cheat Sheets: www.databasestar.com/get-sql-...
🎓 Learn how to design an effective database and create it using SQL: databasestar.mykajabi.com/edd...
Designing a database for an application is a good way to learn and practice your database design skills.
In this video, we have a fictional “chat application”, similar to WhatsApp or Messenger. We have a few requirements and we want to design a database that could store the required information.
This video shows you how to design a database for this chat application from scratch, starting with the first table, adding more tables and fields, and making adjustments along the way.
It includes the concept of a many-to-many relationship, which I have a video about here:
So if you’re interested in a database design for a chat application, either as a project or just to learn, this video will be useful to you.
Timestamps:
00:00 Introduction
00:48 Requirements
02:16 Message table
03:14 Contact table
04:56 Group messages
05:34 Members joining groups
06:57 Refactoring the design
08:46 Enhancements and conclusion - Věda a technologie
The whole video was special. You are a TOP G. Keep it up bro
Thanks!
nice erd for chat systems, will use yours and enhance it base to what i needed. thanks to your video
Glad I could help!
Really impressed... love this stuff...
Thanks!
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?
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.
Thanks for this video sir!!
Most welcome!
that is a great video!! thank you. could you make a video for a simple delivery application?
Thanks! What do you mean by a “simple delivery application”? Like a food delivery service or something else?
@@DatabaseStar exactly, food delivery app would be awesome
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
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.
@@DatabaseStar Oh ok.. Thank you so much!
I also got the same doubt. Thanks @gabrielcosta1159 for asking and @DatabaseStar for answering 👍
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?
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.
Good explanation
Thanks!
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.
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.
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.
Good question, yeah that's right!
Great
Thanks!
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?
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.
Thanks...
You're welcome!
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.
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.
With messages being stored persistently and the above relational data model. Which database is most suitable for chat app?
I’m not sure. You could get started with any sql database and it should work, I think.
شكراا
You're welcome!
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
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.
I am wondering if this database design was for the on-device database like of watsapp or for the central hosted database ??
The idea was that it would be the central hosted database, but the on-device database could be different.
if we want single user to send a message and or send to to a group how will i do that here ?
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.
Are u sure a sql db would be fine for this usecase?
Yes it should be fine to use for this kind of design.
hey can you make bridge table with 2 EAV data model?
Yeah you probably can!
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?
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.
@@DatabaseStar ohhhh....そか
@@user-be3iv4qx9k Don't pretend to be a Japanese. Chinese.
@@fuddyduddy damn 😂😂
In this case how to identify the sender?
You can match the message.from_number to the contact.phone_number to see who the sender is.
I have three users and each user has a table. How will I do that here ?
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.
@@DatabaseStar What about creating users table as superclass and the other three tables that have been created will be subclasses?
What about temporary like 24hr story system
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.
How do i differentiate single chat and group chat? tks
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).
@@DatabaseStar Thank you for answering my question, so how can I tell the difference?
@@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".
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.
Yeah, that could work, but I think it would only allow a message to be sent to a single contact.
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?
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.
@@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
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.
@@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