Simple Chat Application ERD (With Database Structure)

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

 

 

CHAT APPLICATION ERD

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.

 

 

 

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.

 

 

LINKS & REFERENCES

• Entity-Relationship Diagram Symbols and Notation – Lucid Chart
• NodeJS Live Chat – Code Boxx
• PHP Live Chat – Code Boxx