Requirements in nutshell

The core functionality of a messaging system in an application is to send notifications/messages to a user or a set of users. Our system also allows one to send messages to a user group. User groups can obviously be formed on some parameters like access privileges, geographical location of users, etc.

This system allows receivers to respond to the messages. It also keeps track of who has read the message and who has not.

In addition, the system has a built-in reminder mechanism that allows a sender to create a reminder, and then sends a reminder to all receivers accordingly.

Entities and relationships
In this data model, user and message are the main entities to store users’ and messages’ details.

Columns in the user table would be user related attributes like first_name, last_name, etc.

Data model for a messaging system, table 'User'

Some self-explanatory columns in the message table would be subject, message_body, create_date and expiry_date. I also add a foreign key column called creator_id in this table that refers to the id column of user table. As its name suggests, it signifies the id of the creator of a message. Since there would always be one creator for a message, I keep this column in the message table only. You might be wondering why there is an expiry_date column in the table. I have added this column to manage reminders on a message. I will explain more about this column later in this article.

Data model for a messaging system, table 'Message'

The most important table in this data model is message_recipient. I would say the whole data model revolves around this table only. One of the main objectives behind creating this table is to hold the mapping between messages and their recipients. Thus the recipient_id column in this table signifies recipients’ ids, and this column refers to the id column of user table.

When a message is sent to one recipient, one record will be inserted into this table with the recipient’s id in the recipient_id column.

Let’s get back to the message_recipient table. I add a reference to the primary key of the user_group table into the message_recipient table. I name it recipient_group_id. This column will hold the value of the user-group for which the message is sent.

Now whenever a message is sent to a group, multiple records will be inserted into the message_recipient table based on the number of users in the group, and the recipient_group_id will be logged accordingly against all those records.

Let me illustrate it further with an example. Suppose a message is sent to a group of 10 people. In this case, a total of 10 records, one for each recipient_group_id of the group, will be inserted into the message_recipient table.

Please note that if the message is sent to a user, not a group, then the recipient_group_id column remains empty. In this case, the direct user_id will be logged under the recipient_id column.

I will add one more column called is_read into the table to hold a flag against a message-user that signifies whether or not the message is read by the user.

Unique key in message_recipient table – There should be a composite unique key on the columns message_id, recipient_id and recipient_group_id, to ensure that only one record exists for a unique combination of these columns.

I keep the is_active column in all tables, except the message and message_recipient tables, in order to enable a ‘soft delete’ of records. Since I have added an expiry_date column in the message table, an is_active column is not needed. Moreover, this column is not needed in the message_recipient table because a message cannot be reverted directly once it is sent. However one can make it inactive by updating the expiry_date for the message to a date in the past.

Replying to a message

Now suppose the system allows users to respond to received messages. I extend the same table message to cater this requirement instead of creating a new table for replies. I will add one column called parent_message_id to establish a hierarchical relationship between messages. I will insert a new record for reply message, and update the parent_message_id column for reply messages. This model supports n-level of hierarchical relationship, i.e. reply on reply message can also be tracked through this model.

Full article...