Design of SQL Server website email database

There are two tables:

Table 1: Message

ID: ID;

Sendid: the sender id;

Recid: Number of the recipient (if it is 0, the recipient is the owner );

Messageid: ID of the insite email;

Statue: view the insite email status,

Readtime: read time;

Table 2: messagetext

ID: ID;

Message: the content of the insite message;

Pdate: the insite message sending time;

Table 3: readglobalmessage

ID: group Message ID

Globalid: The group Message ID corresponds to the messageid in the message;

Userid: User ID;

Readtime: read time;

Status: group message status (only whether the message exists );

Two numbers are automatically increased, and messageid in message is the foreign key of the messagetext table, which corresponds to the ID in messagetext,

The Administrator sends a group of insite Emails:

Alter procedure DBO. newmsgsingle @ sendid int, @ recid int, @ title nvarchar (50), @ message nvarchar (50) asinsert into messgetext values (@ title, @ message, getdate ()) insert into message values (@ sendid, @ recid, @ identity, '', 0) Return
 

The recid here is set to 0, indicating that the receiver is the owner;

The MSG used below is the view:

 
Select DBO. message. sendid, DBO. message. recid, DBO. message. messageid, DBO. message. readtime, DBO. message. status, DBO. messgetext. title, DBO. messgetext. message, DBO. messgetext. createtimefrom DBO. message inner join DBO. messgetext on DBO. message. messageid = DBO. messgetext. ID
 

Displays the number of unviewed short messages:

Alter procedure DBO. getmsgcount @ userid intasselect count (messageid) from msgwhere (messageid not in (select globalid from readglobalmessage where (userid = @ userid) and (status = 0) and (recid = @ userid or recid = 0) Return
 
 
Get unread short message data:
 
 
Alter procedure DBO. getunreadmsglist @ userid int asselect * From msgwhere (messageid not in (select globalid from readglobalmessage where (userid = @ userid) and (status = 0) and (recid = @ userid or recid = 0) Return
 
 
 
 
View All Short Messages (including unread read messages, as long as they are not deleted by the number of users)
 
Alter procedure DBO. getallmsg @ userid int asselect * From msgwhere (messageid not in (select globalid from readglobalmessage where (userid = @ userid and status = 0) and (recid = @ userid) or (recid = 0) Return
 
 
View the details of a short message:
 
 
Alter procedure DBO. getmsgdetails @ msgid int, @ userid intasdeclare @ temprecid intselect @ temprecid = recid from message where id = @ msgidif @ temprecid! = 0 begin select * From messgetext where id = @ msgid update message set status = 1, readtime = getdate () where messageid = @ msgid and recid = @ userid end else begin select * From messgetext where id = @ msgid insert into readglobalmessage values (@ msgid, @ userid, getdate (), default) endreturn
 
 
View Short Message content: after entering the short message interface, click a short message to obtain the specific short message content based on the short message number. If the short message is a group message, insert data in readglobalmessage, if it is a point-to-point message
 
Update the data in the message table, such as statue and readtime;
 
 
Obtain
 
 
Sending Short Messages Between users is similar to managing group-sent short messages;
 
 
Key Point: Get the value of the added auto-increment column: @ identity;
 
 
Download insite email instances
 
 
Refer to blog 1
 
 
Refer to blog 2