Database Design for Chat Application

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

[asim-gandu-phenchod]

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

[codexperience]

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

[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.

[gabrielcosta]

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

[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.

[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?

[BcomingHIM]

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

[acbusiness]

Really impressed... love this stuff...

[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

[jatinsutaria]

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?

[iremozen]

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

[eugenefedorov]

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.

[thebulletkin]

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?

[BaoNguyen-ygvp]

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

[rockon]

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

[practicalbong]

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

[palspal]

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

[medilies]

In this case how to identify the sender?

[falseloop]

What about temporary like 24hr story system

[ron-]

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