Simple Chat Application ERD (With Database Structure)

Last Updated: November 10, 2023

Welcome to a sharing of an ERD of a simple chat application. So you want to create a chat application? Or curious about what goes on behind one? Well, here is a sharing of my simple version – Read on!

 

 

TABLE OF CONTENTS

 

ER DIAGRAM

First, here is the entire ER diagram and a quick overview of the system.

 

CHAT APPLICATION ERD

Chat Application ERD (click to enlarge)

To keep things simple, I have grouped this system into 3 sections.

  • Users – Don’t think this needs an explanation.
  • Groups – Chat groups.
  • Chat History – The messages and attachments.

 

FOR REFERENCE ONLY

I can hear “the true professionals” banging on their keyboards. “This is inaccurate”, “this is stupid”, “I can do better”. By all means, this is only a simple example for your reference.

  • A chat group must have at least one user. If there are none, it will automatically “self-destruct” and all related data will be deleted. If you want to keep the chat history of groups without users, go ahead and change it on your own.
  • Chat history will be recorded. If you value the privacy of your users, go ahead and remove this.
  • If you want to add more “chat messages” such as short audio clips, video clips, and images – Go ahead and do your own changes.

The point is, you are creating your own system. You have to decide your own rules, decide what works best for yourself.

 

 

TABLE STRUCTURE

Now that we are done with the overview, let us get into more details on the entities or tables.

 

PART 1) USERS

COLUMN DATA TYPE NOTES
user_id INTEGER Primary key.
user_name VARCHAR The user’s full name.
user_email VARCHAR The user’s email. Set this to unique to prevent duplicate registrations.
user_type VARCHAR Optional and up to you to decide. For example – admin, manager, staff, etc…
user_password VARCHAR User’s login password.

To address the elephant in the room once again, a chat application definitely has users.

 

PART 2) GROUPS

2A) CHAT GROUP

COLUMN DATA TYPE NOTES
group_id INTEGER Primary key.
group_name VARCHAR Chat group name.
group_description VARCHAR Chat group description, if any.

This should be self-explanatory as well, chat groups that the users create.

 

 

2B) USER TO GROUP

COLUMN DATA TYPE NOTES
user_id INTEGER Primary and foreign key.
group_id INTEGER Primary and foreign key.
user_role VARCHAR The user’s role in the group, up to you to decide – Administrator, moderator, etc…

Which users are added to which chat groups. The “user role” is kind of optional, but things will be very chaotic without it. How do you determine who has the authority to kick, ban and add users?

 

2C) CHAT BAN

COLUMN DATA TYPE NOTES
group_id INTEGER Primary and foreign key. Banned from this group.
user_id INTEGER Primary and foreign key. This user cannot access the specified group.
ban_by INTEGER Foreign key, administrator imposing the ban.
ban_start DATE TIME Timestamp, when the ban is imposed.
ban_end DATE TIME Timestamp, when the ban ends. If any.

Well, because conflict is bound to happen in one way or another.

 

 

PART 3) CHAT HISTORY

COLUMN DATA TYPE NOTES
group_id INTEGER Primary and foreign key. This is a chat message in this group.
user_id INTEGER Primary and foreign key. This message is posted by this user.
chat_time DATE TIME Primary key. Time of post.
chat_message VARCHAR The chat message or content.
chat_attach BLOB Attachment – Audio, picture, short video clip.

I know some people love to preach “privacy” and saving chat messages are against their “ethical values”. Well, it’s your system, you decide.

  • Create a group secret key kept on the users’ devices. Only the group members can decode the messages.
  • Don’t keep the messages at all, that is, a session-based chat.
  • Automatically delete old messages after a certain time.
  • Keep the messages on the server but give the users permission to delete their own messages.

 

EXTRAS

That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.

 

LINKS & REFERENCES

 

THE END

Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!