[Reprint] Station message DB design ideas

[Reprint]: Click to open a link to the original title: Two years later, the implementation of the "station letter" note: only because in the work of similar things, so learn from the author's articles, if there is inconvenience please inform, immediately delete

Two years ago, Vancan a millet in the blog Park sent two on the station letter design to achieve blog, "mass" station letter "realization", "mass" in the station letter "realization (continued)", which elaborated his on the station letter mass design idea, very has the reference significance. He was designed with issues such as user volume and storage space occupancy in mind. Of course, in his two blog posts stressed that the station letter design to consider the specific situation, there is no ideal design, his design is only for the mass (point to face) solution. Here is a brief description of his design, detailed can step Vancan a millet blog.

The design scheme of Vancan Millet:

In the station, the letter is divided into "point-to-points" and "dot-to-face", "point-to" belongs to the private message, the information passed between users. "Point-to-face", which belongs to a system message or public information, belongs to a one-to-many send.

The design of the station letter should take into account the accuracy of the delivery (that is, the person receiving the information), but also to consider the issue of persistent storage space, in his first blog post detailed introduction.

We only take the third situation here to illustrate, that is, the user volume is millions, active users are only part of it.

Design of the database:

Table Name: Message

ID: number; Sendid: Sender number; RecID: Recipient number (if 0, the recipient is all); MessageID: the station letter number; Statue: The View status of the letter in the station;

Table Name: MessageText

ID: number; message: The content of the letter in the station; PDate: The time of sending the message within the station;

Divides a message into two parts, one for storing content and the other for storing the user's viewing state. It also solves the problem of storage space consumption of mass information, and does not need to insert relevant data for each user.

In addition, considering the millions user volume, active users are only part of it, it is not possible to send a system message, in the message table for each user to insert a state (marked as unread), if 1 million users, then send a message, It is clearly not feasible to insert data in the message table with 1 million marked states. So Vancan a millet put forward the idea:

Retrieve the message and Messgaetext when the user logs in, matching the Messgaetext ID with the MessageID of Messgae, in two cases:

One, the recid= own ID is not found and the message ID in MessageText is not included in the MessageID of Messgae

This part of the message is taken out, displayed as the user unread, when the user clicks to read, the message reading State is written to the Messgae table, status= read.

Second, find recid= yourself and the message ID in MessageText is contained in the MessageID of Messgae, the status is marked as read

This part of the message is shown in advance, displayed as the user has read, if you want to "delete" (of course, a logical deletion, not a physical database delete), set the status= delete.

For the above design scheme, the design system message mass All users, is very suitable. But the smaller the audience (that is, the smaller the "point-to-face" faces), the less appropriate, so we need to expand on this design.

Only a subset of the user millions and active users mentioned above is explored. or the use of separate message content and reading State design.

We integrate point-to-point and dot-to-polygon considerations, and refine this "polygon", no longer a general user. Polygons can be users with a role, users of a user group, or even hashed users who do not have any public characteristics.

Design ideas

This is summarized as follows: We divide the message into Private, public, or system messages (Global) (or merge public and system messages into public messages), as appropriate.

1. To-point: one-to-one delivery, private
2. Point to individual: (Receive polygons for hundreds of users) one-to-many (hundreds of) send, using Private messages
3. Point-to-part: (Receive polygons have some common features, such as user groups, user roles), belonging to public messages
4. Point to all: a one-to-all send, which belongs to the system message (Global)

Database design

Table Name: Message

ID: number; RecID: Recipient number; MessageID: Station letter number; Statue: View status of the station letter

Table Name: MessageText

ID: number; Sendid: Sender number; message: The contents of the message; Type: information types; Group: User groups ID; Postdate: Message delivery time in the station

Where status state is unread, read, deleted

Type has private (direct message), public (common), Global (System message)

First-to-point

Dot-to-point transmission is a private message, such as a user sent to a B user, first insert the content of the messages in the MessageText table and set type=private, while inserting a record in the message table settings recid=b,status= Unread

User B finds Recid=b, and Staus is unread, type=private, displayed as a private messages unread, click Read to change status= read

User B finds Recid=b, and Staus is read, type=private, displayed as a private messages read, delete settings status= deleted

The second point to the individual

In the same way as private messages, when a message is sent, the message content is inserted in the MessageText table and the type=private is set, and multiple record settings are inserted in the message table recid= each recipient id,status= Unread

Each receive is read processing in the same way as private messages.

The third point to the local

Point-to-part is a pair of roles or a group of users sent, such as an administrator to a normal user group, insert message content in the MessageText table, and set Type=public and group as user group ID

There are two situations when a user logs in:

1. The message ID of recid= ID not found and MessageText (Type=public and group= own group) is not included in Messgae MessageID

Extracted to show the user public message unread, when the user clicks to read, the message reading State is written to the Messgae table, status= read.

2, find recid= own ID and MessageText (type=public and group= own group) message ID contained in Messgae MessageID

This part of the message is extracted, displayed as a user public message read, if you want to "delete" (of course, a logical deletion, not a physical database delete), set the status= delete.

Note: You can not verify Group= 's own group at this time

Fourth point to all

The point-to-all and point-to-local use a similar approach. For example, an administrator sends to a normal user group, inserts the message content in the MessageText table, and sets the Type=global

There are two situations when a user logs in:

1. recid= own ID not found and the message ID in MessageText (type=global) is not included in Messgae MessageID

Extracted to display as user system messages unread, when the user clicks to read, the message reading State is written to the Messgae table, status= read.

2. Find recid= own ID and the message ID in MessageText (type=global) is included in MessageID of Messgae

This part of the message is extracted, displayed as a user system message is read, if you want to "delete" (a logical deletion, not a physical database delete), set the status= delete.

Processing flow

Let's look at the whole process, how the system extracts and displays the information after the user logs in.

After the user login, using AJAX asynchronous loading, statistics in the user station letter

• recid= own ID in messgae table and status= unread, displayed as private messages unread
• recid= own ID in messgae table and status= read and type=private, displayed as private messages read
• recid= own ID is not found in the Messgae table and message IDs in MessageText (Type=public and group= own group) are not included in Messgae MessageID, and are displayed as public messages unread
• The recid= own ID is found in the Messgae table and the message ID in MessageText (type=public) is included in Messgae MessageID, which is displayed as public message read
• recid= own ID is not found in the Messgae table and the message ID in MessageText (type=global) is not included in Messgae, which is displayed as System message unread
• The recid= own ID is found in the Messgae table and the message ID in MessageText (type=global) is included in Messgae MessageID, which is displayed as system message read

Perhaps you will think that this solution also does not solve the problem of physical deletion or a more refined reception of the "face", when the user displays the message requires a lot of complex judgments affecting the user experience and performance, and so on. In fact, we can also set the expiration date of the message, the expired message is not displayed. or design a message cleanup mechanism yourself. Of course, these need to be further refined and improved in the future, this article is not involved in the moment. The above is just my letter on the site of some superficial view, limited capacity inevitably exist all kinds of problems, I implore the park you can put forward criticisms, suggestions. If you feel this article is very good can click on the recommendation, let more people participate in the discussion. At the same time to Vancan a millet to thank him for sharing his valuable ideas.

[Reprint] Station message DB design ideas