一个群发站内信的设计

开始做发送消息的时候没意识到群发很复杂，草草写了一个消息/公告表，然后在前台用两个菜单“个人消息”，“系统消息”取出不同的信息，然后两边用同样的逻辑做“已读，未读，删除”的操作，然后继续后面的流程，今天突然发现我用私信的方式设了公共消息为未读那也太搞了，才发现做群发还是值得仔细考虑番的。
开始的表为：

create table wgi_notice(
    id int identity primary key,--主键id 
    title nvarchar(100) null,--公告标题
    notice ntext null,--公告内容
    pubdate datetime default getdate(),--发布时间
    unread int default 0,    --默认未读
    publisher int default -1, --默认值表示未知系统管理员
    objid int default -1 --默认值表示公告消息，否则跟上接收人ID
    objtype int default -1 --表示用户组（不同的用户表）
)

这样，主表就是这一张了，用objid表示接收消息的对象，objtype表示接收消息的用户组（此系统有几个用户表，纯粹是系统需要，跟群发无关，可略过）。鉴于上面的考虑，unread就不能再用来作群发消息的个人阅读与否的状态了。

但是显然我不会有多少用户就插入多少条消息纪录，因此我建了一个关系表，专门用来保存个人与消息是否阅读与删除的状态。

create table wgi_noticestat (
   id     int     identity    primary key ,
   noticeid     int    null,
   usertype     int    null,
   userid        int     null,
   unread        int    null default 0,
   deleted      int     null default 0
)

不好意思，建表的时候发现usertype,userid更容易理解，但是前面的代码不想改了，先这样吧。

思维是：用户类型+用户ID+消息ID 对应一条具体的消息的“状态”，不用我的自增id为主键的方案的话，可把那三个字段作为联合主键，以下就用“联合主键”来指代这三个字段吧。
但是此表初始是空的，也就是说查不到这个表有数据，那么证明这条纪录用于此用户是未读的，显然也是未删除的（显然，我的设计是删除只是打个标，当然要这么设计，事实上全站就这么一条，你不能真正删掉它）。
而假如查询到“联合主键”存在，那么更改状态就简单多了。

所以前期只是需要查出所有未标为“删除”的公共消息，然后来操作状态表，那么对于消息的各种打标（已读，未读，删除）就都能操作了，所以显然，想要正确地“查询”出想要的表，那就是：
显示所有群发消息（还可以更复杂点，查询出属于该用户组的群发消息），
过滤掉消息状态表中标记该消息为删除的记录
假如消息状态表中没有此条记录，显然不需要被过滤
把该条记录的已读未读状态查询到主表中，如果状态表中无此条记录，则记录成未读（此条主要是为了前台给已读未读加不同的样式）
所以查询复杂了点，直接写sql的话就是

select a.*, 
 isnull((select d.unread from wgi_noticestat d where d.usertype=0 and d.userid=1 and d.noticeid=a.id),0) readed  --从状态表读出已读未读状态
from wgi_notice a 
where objid=-1 and --查询公共消息，如果要分用户组，在此加条件
((select (select top 1 deleted from wgi_noticestat b where b.usertype=0 and b.userid=1 and b.noticeid=a.id))<>1 --条件：删除状态不为真
 or
  (select count(c.noticeid) from wgi_noticestat c where c.usertype=0 and c.userid=1 and c.noticeid=a.id)=0) --条件：或在状态表中没有记录
order by a.pubdate desc

一个表就查询出来了，修改状态的时候怎么做呢？
只需要根据“联合主键”查询状态库，假如没有记录，则新增一条记录，设状态值为需要的值；假如查到了这条记录，则update状态值。
另外删除操作会简单些，只需要简单地新增/修改为1就行，因为一旦你”删除“了，你就没机会对它”恢复删除“的，所以你写后台sql的时候可以少送一个状态参数进去，当然，这个不重要了。

最后，结果就是，不管这个系统有多少用户，不管这个系统有多少沉默用户，只有用户在”操作“某条公共消息的时候，才会insert一条公共消息的记录，这样数据库消耗是最少的。既不需要把一条消息重复插N次，也避免了沉默用户而产生的冗余信息，不操作则无记录。
不知道朋友们看明白没有，只是初步设计，希望能提出改进建议。