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
First, here is the entire ER diagram and a quick overview of the system.
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.
Now that we are done with the overview, let us get into more details on the entities or tables.
PART 1) USERS
|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
|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
|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
|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
|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.
EXTRA BITS & LINKS
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
- Entity-Relationship Diagram Symbols and Notation – Lucid Chart
- NodeJS Live Chat – Code Boxx
- PHP Live Chat – Code Boxx
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!