.DB TEXT MESSAGE FILE SOFTWARE

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. In this case, the direct user_id will be logged under the recipient_id column. 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, a total of 10 records, one for each recipient_group_id of the group, will be inserted into the message_recipient table. Suppose a message is sent to a group of 10 people. Let me illustrate it further with an example. 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. This column will hold the value of the user-group for which the message is sent. I add a reference to the primary key of the user_group table into the message_recipient table. Let’s get back to the message_recipient table. a user can be part of more than one group, I will create another table called user_group.įor example, if a group is formed with 25 users, there would be 25 records, one for each user, in the user_group table. Since there is a many-to-many relationship between the user and group tables, i.e. I need another table, namely group, to hold group details. Here, I should first explain how this model can be extended to a requirement of sending messages to multiple recipients at once. Now you may be wondering what the recipient_group_id column signifies in this 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. Thus the recipient_id column in this table signifies recipients’ ids, and this column refers to the id column of user table. One of the main objectives behind creating this table is to hold the mapping between messages and their recipients. I would say the whole data model revolves around this table only. The most important table in this data model is message_recipient. I will explain more about this column later in this article. I have added this column to manage reminders on a message. You might be wondering why there is an expiry_date column in the table. Since there would always be one creator for a message, I keep this column in the message table only. As its name suggests, it signifies the id of the creator of a message. I also add a foreign key column called creator_id in this table that refers to the id column of user table. Some self-explanatory columns in the message table would be subject, message_body, create_date and expiry_date. In this data model, user and message are the main entities to store users’ and messages’ details.Ĭolumns in the user table would be user related attributes like first_name, last_name, etc. 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. It also keeps track of who has read the message and who has not. This system allows receivers to respond to the messages. User groups can obviously be formed on some parameters like access privileges, geographical location of users, etc. Our system also allows one to send messages to a user group. The core functionality of a messaging system in an application is to send notifications/messages to a user or a set of users. This article will explain the system requirements and step by step approach to design a data model for a messaging system.

.DB TEXT MESSAGE FILE SOFTWARE

We often joke that any software system always evolves into a messaging system.

.db text message file