类似bbs的message功能的数据库设计
功能描述:
功能主要包括两部分
A部分 可以实现用户间Message的收发;
B部分 可以让系统向所有用户发信息(这个操作有可能是经常进行的).
功能实现
A部分 功能数据库设计比较简单
B部分 .我想主要有两种方法去实现:
一是一个用户增加一条记录,这样实现很方便,如果在用户不是很多的情况下可以考虑,但是如果用户很多的话,缺点就显示出来了,太浪费数据库资源,不太现实.
还有一种方法就是把收Message的用户Sender_User_ID 和 Receiver_User_ID设置成0,表示是系统的信息,可是这样的问题是怎么区分一个用户有没有 读这个消息.可以解决的方法是增加一张表MessageIsRead,如果用户已经读则添加一条记录,select数据查一下MessageIsRead,如果已经有记录了就表示已经读了~
具体实现过程
表结构
存储过程
其他说明:
这样的设计已经破坏了数据的完整性,因为Sender_User_ID和Receiver_User_ID 为0时,User_ID = 0的数据在用户表中并不存在,如果要解决这个问题可以把系统向用户发的数据单独放在另外一张新的表格里.
在这里还是没有从根本上解决数据的完整性问题,比如当用户向系统管理员发Message和管理员向用户发Message时Sender_User_ID,Receiver_User_ID怎么填?因为系统管理员在用户表里是不存在的,所以没有真正的User_ID.如果您知道这个问题请您在回复里告诉我,谢谢.
功能主要包括两部分
A部分 可以实现用户间Message的收发;
B部分 可以让系统向所有用户发信息(这个操作有可能是经常进行的).
功能实现
A部分 功能数据库设计比较简单
B部分 .我想主要有两种方法去实现:
一是一个用户增加一条记录,这样实现很方便,如果在用户不是很多的情况下可以考虑,但是如果用户很多的话,缺点就显示出来了,太浪费数据库资源,不太现实.
还有一种方法就是把收Message的用户Sender_User_ID 和 Receiver_User_ID设置成0,表示是系统的信息,可是这样的问题是怎么区分一个用户有没有 读这个消息.可以解决的方法是增加一张表MessageIsRead,如果用户已经读则添加一条记录,select数据查一下MessageIsRead,如果已经有记录了就表示已经读了~
具体实现过程
表结构
1
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MessageIsRead]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
2drop table [dbo].[MessageIsRead]
3GO
4
5if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Message]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
6drop table [dbo].[Message]
7GO
8
9CREATE TABLE [dbo].[MessageIsRead] (
10 [User_ID] [bigint] NOT NULL ,
11 [Message_ID] [bigint] NOT NULL
12) ON [PRIMARY]
13GO
14
15CREATE TABLE [dbo].[Message] (
16 [Message_ID] [bigint] IDENTITY (1, 1) NOT NULL ,
17 [Message_Title] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
18 [Message_Content] [nvarchar] (4000) COLLATE Chinese_PRC_CI_AS NOT NULL ,
19 [Receiver_User_Id] [bigint] NOT NULL ,
20 [Sender_User_ID] [bigint] NOT NULL ,
21 [Send_Time] [datetime] NOT NULL ,
22 [Is_Read] [bigint] NOT NULL
23) ON [PRIMARY]
24GO
25
26
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MessageIsRead]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)2
drop table [dbo].[MessageIsRead]3
GO4
5
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Message]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)6
drop table [dbo].[Message]7
GO8
9
CREATE TABLE [dbo].[MessageIsRead] (10
[User_ID] [bigint] NOT NULL ,11
[Message_ID] [bigint] NOT NULL 12
) ON [PRIMARY]13
GO14
15
CREATE TABLE [dbo].[Message] (16
[Message_ID] [bigint] IDENTITY (1, 1) NOT NULL ,17
[Message_Title] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,18
[Message_Content] [nvarchar] (4000) COLLATE Chinese_PRC_CI_AS NOT NULL ,19
[Receiver_User_Id] [bigint] NOT NULL ,20
[Sender_User_ID] [bigint] NOT NULL ,21
[Send_Time] [datetime] NOT NULL ,22
[Is_Read] [bigint] NOT NULL 23
) ON [PRIMARY]24
GO25
26
存储过程
1--检查同名的SP是不是已经存在,若存在则删除.
2IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[usp_Message_Select]') AND type='P')
3DROP PROCEDURE [dbo].[usp_Message_Select]
4GO
5
6--创建SP
7CREATE PROCEDURE [dbo].[usp_Message_Select]
8(
9 @User_ID bigint
10)
11AS
12SELECT * FROM
13(
14
15--以下为普通的用户间对发的Message
16 SELECT
17 Message_ID,
18 Message_Title,
19 Message_Content,
20 Is_Read
21 FROM
22 Message
23 WHERE
24 Receiver_User_ID = @User_ID
25
26 UNION
27
28--以下为系统Message
29 SELECT
30 Message.Message_ID,
31 Message_Title,
32 Message_Content,
33 CASE ISNULL(MessageIsRead.[User_ID],0) WHEN 0 THEN 0 ELSE 1 END AS Is_Read --如果用户没有阅读,MessageIsRead.[User_ID]为 null
34 FROM
35 Message
36 Left JOIN
37 MessageIsRead
38 ON
39 MessageIsRead.[User_ID] = @User_ID
40 AND
41 MessageIsRead.[Message_ID] = Message.Message_ID
42 WHERE
43 Sender_User_ID = 0
44 AND
45 Receiver_User_ID = 0 --Sender_User_ID = 0 and Receiver_User_ID = 0 表示是系统信息
46
47 ) a
48GO
49
--检查同名的SP是不是已经存在,若存在则删除.2
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[usp_Message_Select]') AND type='P')3
DROP PROCEDURE [dbo].[usp_Message_Select]4
GO5
6
--创建SP7
CREATE PROCEDURE [dbo].[usp_Message_Select]8
(9
@User_ID bigint10
)11
AS12
SELECT * FROM13
(14
15
--以下为普通的用户间对发的Message16
SELECT17
Message_ID,18
Message_Title,19
Message_Content,20
Is_Read21
FROM22
Message23
WHERE24
Receiver_User_ID = @User_ID25
26
UNION 27
28
--以下为系统Message29
SELECT30
Message.Message_ID,31
Message_Title,32
Message_Content,33
CASE ISNULL(MessageIsRead.[User_ID],0) WHEN 0 THEN 0 ELSE 1 END AS Is_Read --如果用户没有阅读,MessageIsRead.[User_ID]为 null34
FROM35
Message36
Left JOIN37
MessageIsRead38
ON39
MessageIsRead.[User_ID] = @User_ID40
AND41
MessageIsRead.[Message_ID] = Message.Message_ID42
WHERE43
Sender_User_ID = 0 44
AND45
Receiver_User_ID = 0 --Sender_User_ID = 0 and Receiver_User_ID = 0 表示是系统信息46
47
) a48
GO49
其他说明:
这样的设计已经破坏了数据的完整性,因为Sender_User_ID和Receiver_User_ID 为0时,User_ID = 0的数据在用户表中并不存在,如果要解决这个问题可以把系统向用户发的数据单独放在另外一张新的表格里.
在这里还是没有从根本上解决数据的完整性问题,比如当用户向系统管理员发Message和管理员向用户发Message时Sender_User_ID,Receiver_User_ID怎么填?因为系统管理员在用户表里是不存在的,所以没有真正的User_ID.如果您知道这个问题请您在回复里告诉我,谢谢.
