CODEWITHSUNDEEP
phpmysqldatabase-design
Ron
Ron

Reputation: 1047

Database design for group messaging

Just a question about what the best way is to send a message to a group (Like whatsapp group messaging).

I save the members per group into a separate database table and once a user sends a message to the group it retrieves the users from this table. I'm a bit stuck on part two:

As I see it there would be three methods.

  1. Insert the message into a database for the entire group. Send the message back to the different users based on groups they are member of.
  2. Insert the message in the database per user of the group. So loop the members and insert for every member. This has the advantage over the first method that downloaded, read and deleted status can be tracked.
  3. Create a table for messages and one for the message-recipient link. But I'm not really sure how a would query in the most optimum way as to retrieve all the data (downloaded, read, etc) for a group of users. Is that possible in a single query?

Would anyone know what the best method would be? I can imagine that method 2 will fill up a database pretty fast, but method 1 doesn't have the ability of tracking status for a message.

At the moment I am using method 1, but I run into the problem that when a user deletes a message. It would still be returned on other devices because there would be no easy way of setting a certain deleted flag for group messages.

Does anyone know how apps like telegram, whatsapp and so on do this? And would method 2 give problems later on when millions of messages are sent?

Regards,

Ron

Upvotes: 2

Views: 3678

Answers (1)

Karl
Karl

Reputation: 833

I would seperate it like this.

Messages

id, message, owner_id, deleted

Groups

id, name

Message_Group

message_id, group_id

User_Group

user_id, group_id

Then you can don't need to add a message to all users in that group. When you insert a new message you first inserts it into Messages, catch the id and inserts it into Message_Group with the message id and group id. To get the messages of a group just join Message_Group and Messages and you are pretty much done. Of course you also needs to join the user table to catch who wrote that messages.

Upvotes: 2

Related Questions