Share the design of the database table of group-sent website emails

"Insite mail" is different from email, which is sent and saved by a dedicated email server. The "insite message" is a message in the system. To put it bluntly, the "insite message" is implemented by inserting records in the database.

"Insite email" has two basic functions. 1. Point-to-Point message transmission. The user sends an insite email to the user, and the Administrator sends an insite email to the user. 2. Send a point-to-plane message. The Administrator sends messages to a group of users (who meet certain conditions. Point-to-Point message transmission is easy to implement. The following describes how the mass mailing of "insite emails" is implemented based on different situations.

In the first case, there are a few users in the station. (Dozens to hundreds)

In this case, because the number of users is very small, there is no need to consider the database optimization too much, using a simple table, the system design is also simple, it is also relatively easy to maintain in the later stage, which is a typical practice of changing the space for time.

The database is designed as follows: Table Name: Message

ID: ID; sendid: sender ID; recid: receiver ID (if it is 0, the receiver is the owner); message: insite message content; statue: insite message viewing status; pdate: insite email sending time;

If an administrator wants to send insite emails to all users, traverse the user table and insert insite emails to the Message table in sequence according to all users in the User table. In this way, if there are 56 users, 56 insert operations will be performed for a group of insite emails. This is easy to understand and consumes a lot of space.

After a user logs in, the statement for viewing insite emails is:

Select * From message where recid = 'id' or recid = 0

In the second case, there are tens of thousands of users in the station ).

Follow the first case. The consequence of sending an insite message is that the background crashes. Because it is not the most important thing to send an insite email that has to repeat thousands of insert records. The key is that there are thousands or even tens of thousands of records, and the content of the message field is the same, message occupies a large amount of storage space. For example, if the message field contains 100 Chinese characters and occupies 200 bytes, then the Message Field occupies 50 thousand x 200 = 50000 bytes = 10 m. A simple insite message occupies 10 MB, which makes it impossible for people to live.

Therefore, the original table is split into two tables and the message body is placed in one table, saving space.

The database is designed as follows:

Table Name: Message

ID: ID; sendid: sender ID; recid: receiver ID (if it is 0, the receiver is the owner); messageid: insite email number; statue: insite email viewing status;

Table Name: messagetext

ID: Number; message: the content of the insite email; pdate: the time when the insite email is sent;

When the Administrator sends an insite email, perform two steps. Insert the content of the insite email in the messagetext table. Then, insert a record to all users in the message table to identify an insite message.

In this design, the entity information of repeated insite emails (content of insite emails and sending time) is put in one table, which saves a lot of storage space. However, the query is more complex than the first case.

In the third case, there are a large number of users (millions) on the site, and active users only account for part of them.

Everyone has had this experience. One day, I registered a user when I was in a bad mood. After a while, for various reasons, I forgot the user name and password at the time of registration and no longer logged on. This user is called inactive. In reality, inactive users account for a large proportion.

We register two million users, of which only 10% are active users.

Even in the second case, if you send an "insite email", you have to perform 2 million insert operations. However, the valid operation is only 10%, because the other 90% of users may never log on again.

In this case, we have to change our thinking.

The database is designed in the same way as the second scenario:

Table Name: Message

ID: ID; sendid: sender ID; recid: receiver ID (if it is 0, the receiver is the owner); messageid: insite email number; statue: insite email viewing status;

Table Name: messagetext

ID: Number; message: the content of the insite email; pdate: the time when the insite email is sent;

When an administrator sends an insite email, only the content of the insite email is inserted in messagetext. No records are inserted in the message.

After logon, the user first queries those records in messagetext that are not recorded in the message, indicating that they are unread insite emails. When viewing the content of the insite message, insert the related records into the message.

This method is compared with the second one. If the number of active users is 100%. The two are the same in efficiency. The lower the proportion of active users, the better the third advantage. Only valid records are inserted. inactive records no longer occupy space.

The above are my thoughts on how to implement the mass "insite emails.

Author: hichina yiyu
Source: http://grenet.cnblogs.com/
The copyright of this article is shared by the author and the blog. You are welcome to repost this article, but you must keep this statement without the author's consent andArticleThe original text connection is clearly displayed on the page. Otherwise, the legal liability is retained.