0

I want to build a messaging system and looking for best approach for DB schema.

Here's the system that i want to build.

There will be registered users. They can send message to each other. These users will belong to one or more organizations. So admin can send msg to organizations. These users may also belong to one or more groups and admin or users can send msg to groups.

Also i want to add status for messages like new, old, deleted.

Can someone recommend me table schema ?

Important note : one user can be in 2 groups so if sender wants to send a msg to those 2 groups, the user should get only one message not two. It's the same for organizations.

2 Answers 2

1

My advice to you would be to download a few (3 - 5 of the most popular) apps which perform similar functionality to what you want to achieve.

Then, I would adopt and adapt [some | a few | various] ideas from these to obtain what you want - or as close as you can reasonably come with reasonable effort. Particularly, check out their schemas - no matter what the database.

Just a word about database selection - if you haven't started this project yet, then permit me to recommend PostgreSQL instead of MySQL. It has windowing (analytic) functions, CTEs (Common Table Expressions - AKA WITH clauses), proper SET operators and CHECK constraints. This is just IMHO, but it is my firm belief that having this functionality integral to the database will save you a lot of time, grief and debugging from having to write all this functionality into your app yourself - or having to choose restricted functionality.

0
1

You will have the following tables:

  1. Organizations or Companies
  2. Users or Employees
  3. Groups
  4. Department if you wish

Users can belong to more than one group by adding a new table:

  1. User_Groups

which will have user_id from Users and group_id from Groups

  1. Messages

with message_id, message_status, created_date, created_ip, message_title, message_text

  1. Threads

This will have the message replies and it will have the following: message_id from Messages, User_id from Users, thread_text which will have the user reply, created_ip

You can continue the rest :)

Not the answer you're looking for? Browse other questions tagged or ask your own question.