Group-sending "insite emails"-personal thoughts

Note: This article only looks at workshop!

Today I read an article on group-based insite emails linked to the above link. This is exactly what I have mentioned in my recent project. Here are my personal thoughts!

In the second case of the original article, the author puts forward the following solutions,

"Split the original table into two tables and put the message body in one table to save space. The database design is 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;

"

In the message table, when recid is set to zero, it indicates that the receiver is the owner. In this case, the viewing status of insite emails is a problem. In my opinion, when recid is set to zero, it cannot be in the same table as the Status column, my solutions are as follows:

Solution 1:

There are also two tables:

Messagetext: messagetextid (Message Text ID), recid (receiver ID, when it is zero, the recipient is the owner), messagecontent (message text), date (time)

Message table: messagetextid, recid, status (Message status 0 indicates unread, 1 indicates read), (messagetextid, recid is the primary key)

There are two scenarios for this method:

First, insert a record in messagetext when sending a message, and then insert a record to all users in the message.

Select T. messagecontent

From message m inner join messagetext t on M. messagetextid = T. messagetextid

Where M. recid = userid and status = 0

However, considering that active users only account for a portion of them,

Method 2:

When sending a message, insert a record in messagetext without inserting a record in the message. Insert a record into the message table when a user logs on (or first prompt when the user logs on and insert it during viewing)

Select messageid

From messagetext

Where (recid = userid or recid = 0) and messagetextid not in (select messagetextid from message where recid = userid)

Insert again

Insert

Into message (messagetextid, recid, status)

Values (messageid, userid, 0)

// Can it be directly written like insert
Into deptage (sdept, avgage)
Select sdept, AVG (SAGE)
From student

Because the ID of the currently logged-on user needs to be inserted, I don't know if it works?

You can use

Select T. messagecontent

From message m inner join messagetext t on M. messagetextid = T. messagetextid

Where M. recid = userid and status = 0

You can.

Solution 2: group and point-to-point distribution are differentiated. adding a new table stores point-to-point information. For the group part, it is the same as solution 1 in the second case. You can view the Union at the time.

One question: how many unread messages will be prompted during login. for statistics on the number of unread messages, the second case of solution 1 is directly accumulated based on the original quantity, for solution 2, I want to add one to the number of point-to-point messages, and then accumulate unread group information after logon.