Creating a schema for a chat application requires careful thought to ensure scalability, clarity, and functionality.
Whether you are working on a simple chat app or a more complex group-based system, the database schema acts as the foundation. Here, I’ll share a friendly overview of how a schema can be structured, with room for your suggestions to make it even better!
Basic Tables for a Chat Application
1. User
- Purpose: To store user details.
-
Table Name:
user
- Columns:
Column Name | Data Type | Description |
---|---|---|
id |
UUID | Primary key. |
name |
VARCHAR(255) | User's display name. |
email |
VARCHAR(255) | Unique email address. |
phone_number |
VARCHAR(15) | Unique phone number. |
profile_url |
TEXT | Link to the user's profile picture. |
created_at |
TIMESTAMP | When the user was created. |
updated_at |
TIMESTAMP | Last update time. |
2. Chat Group
- Purpose: To manage chat group.
-
Table Name:
chat_group
- Columns:
Column Name | Data Type | Description |
---|---|---|
id |
UUID | Primary key. |
name |
VARCHAR(255) | Group name. |
description |
TEXT | Optional group description. |
created_at |
TIMESTAMP | When the group was created. |
updated_at |
TIMESTAMP | Last update time. |
3. Chat Group Member
- Purpose: To associate users with chat group.
-
Table Name:
chat_group_member
- Columns:
Column Name | Data Type | Description |
---|---|---|
id |
UUID | Primary key. |
group_id |
UUID | Foreign key to chat_groups . |
user_id |
UUID | Foreign key to users . |
role |
ENUM | Role in the group (e.g., Admin). |
is_active |
BOOLEAN | Active status in the group. |
joined_at |
TIMESTAMP | When the user joined the group. |
4. Chat Message
- Purpose: To store messages in chat group.
-
Table Name:
chat_message
- Columns:
Column Name | Data Type | Description |
---|---|---|
id |
UUID | Primary key. |
group_id |
UUID | Foreign key to chat_groups . |
sender_id |
UUID | Foreign key to users . |
message |
TEXT | Content of the message. |
is_edited |
BOOLEAN | Whether the message was edited. |
is_system |
BOOLEAN | True for system messages. |
created_at |
TIMESTAMP | When the message was sent. |
5. Message Reaction
- Purpose: To track reactions to message (likes, hearts, etc.).
-
Table Name:
message_reaction
- Columns:
Column Name | Data Type | Description |
---|---|---|
id |
UUID | Primary key. |
message_id |
UUID | Foreign key to chat_messages . |
user_id |
UUID | Foreign key to users . |
reaction_type |
VARCHAR(50) | Type of reaction (like, heart, etc.). |
created_at |
TIMESTAMP | When the reaction was made. |
6. Attachment
- Purpose: To store media or files attached to message.
-
Table Name:
message_attachment
- Columns:
Column Name | Data Type | Description |
---|---|---|
id |
UUID | Primary key. |
message_id |
UUID | Foreign key to chat_messages . |
file_url |
TEXT | Location of the attachment. |
file_type |
VARCHAR(50) | Type of file (image, video, etc.). |
created_at |
TIMESTAMP | When the attachment was uploaded. |
7. Notification
- Purpose: To manage notifications related to chat or other events.
-
Table Name:
notification
- Columns:
Column Name | Data Type | Description |
---|---|---|
id |
UUID | Primary key. |
status |
ENUM | Notification status (e.g., SEEN, UNSEEN). |
message |
TEXT | Content of the notification. |
device_id |
UUID | Related device ID, if applicable. |
is_visible_to_admin |
BOOLEAN | Visibility to admin users. |
admin_visible_status |
ENUM | Admin-specific visibility status. |
category |
ENUM | Notification category (e.g., ALERT, GENERAL). |
type |
ENUM | Type of notification. |
sos_alert_type |
ENUM | SOS alert type, if applicable. |
metadata |
VARCHAR(255) | Additional metadata about the notification. |
raised_by_id |
UUID | User who raised the notification. |
user_id |
UUID | Target user of the notification. |
created_at |
TIMESTAMP | When the notification was created. |
8. User Device
- Purpose: To manage devices associated with users for notifications.
-
Table Name:
user_device
- Columns:
Column Name | Data Type | Description |
---|---|---|
id |
UUID | Primary key. |
user_id |
UUID | Foreign key to users . |
token |
VARCHAR(255) | Unique device token. |
device_type |
ENUM | Type of device (e.g., MOBILE, WEB). |
arn |
VARCHAR(255) | Amazon Resource Name for notifications. |
Suggestions and Feedback
This schema is designed with a balance of simplicity and functionality in mind.
However, every project has its unique requirements, and I’d love to hear your thoughts:
- Would you add any additional fields for a better user experience?
- How would you optimize this schema for high-traffic applications?
- Are there any common edge cases you’ve encountered in chat applications that this schema doesn’t address?
Let’s make this a collaborative discussion to create something great! Feel free to share your insights.
I’ve been working on a super-convenient tool called LiveAPI.
It’s designed to make API documentation effortless for developers.
With LiveAPI, you can quickly generate interactive API documentation that allows users to execute APIs directly from the browser.
If you’re tired of manually creating docs for your APIs, this tool might just make your life easier.
Top comments (2)
1) don't use UUIDs for record keys. There are many reasons but the main ones are key sizes and index performance.
2) TIMESTAMP is poorly named. It is not actually anything to do with time but is a unique record incrementer in Hexadecimal that is applied each time a record is created or updated. You should definitely have one, especially in a REST protocol as you can pass it around and check if the source has been updated since you sent it to the client.
3) if you need create and update times use datetime and set them on a trigger.
4) metadata would be better as XML or JSON if your database support.
5) I prefer table names in the singular. User.id makes more sense than users.id.
6) chat group member probably needs an active status and leave date, or another table about membership events (join, leave, suspended, change of role etc..)
7) chat messages should probably have member_id rather than group_id and sender_id. These can be deferred from the member. There may be performance gains from having group and sender (e.g. list all my messages)
8) you have not specified any unique constraints: a user should probably only be a member of a group once, but you may also need to cater for them leaving and rejoining.
Please take this critique as me being supportive. As you have demonstrated, even a simple database design needs to have strong foundations or it will quickly become unweildy.
Also if you are looking at a high volume system, you may want to consider an event-driven methodology whereby actions are written to a queue and later processes into the 3NF schema.
Thank you so much for taking the time to provide such thoughtful and constructive feedback! 🙏 I genuinely appreciate your insights, as they highlight areas where the design can be refined.
You're absolutely right about the trade-offs with UUIDs
Thank you for pointing this out! I realize the confusion in the naming.
I see your point, and User.id does indeed sound more intuitive and readable.
Regarding the event-driven methodology, I’ll admit I’m not very familiar with it yet. It sounds powerful, for handling high-volume systems efficiently. I’ll definitely take the time to look into it and understand how it can complement a 3NF schema for better scalability and performance.
Your critique is not only supportive but also invaluable for refining this schema and building a stronger foundation. Thank you again for sharing your expertise—it’s feedback like this that helps transform a design from good to great! 😊