DEV Community

Cover image for Designing a Schema for a Chat with Notification Application
Athreya aka Maneshwar
Athreya aka Maneshwar

Posted on

17 4 5 5 5

Designing a Schema for a Chat with Notification Application

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!

Image description

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:

  1. Would you add any additional fields for a better user experience?
  2. How would you optimize this schema for high-traffic applications?
  3. Are there any common edge cases you’ve encountered in chat applications that this schema doesn’t address?

Image description

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.

Image description

If you’re tired of manually creating docs for your APIs, this tool might just make your life easier.

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read more

Top comments (2)

Collapse
 
aaronre16397861 profile image
Aaron Reese

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.

Collapse
 
lovestaco profile image
Athreya aka Maneshwar

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! 😊

Postmark Image

Speedy emails, satisfied customers

Are delayed transactional emails costing you user satisfaction? Postmark delivers your emails almost instantly, keeping your customers happy and connected.

Sign up