仿微信实时聊天简单数据库设计

  废话不多说博主先上图效果

 

此次实时聊天功能参考了资料，以及请教了师父给出设计，只设计了四张表，分别是，组表，组成员表，聊天记录表，好友移除表。

       这里我先说下我的设计思路组表的意思和QQ 群一样，一个组就代表一个群，一个房间，两个人要想聊天，必须有一方发起聊天的同时先创建一个组，并且把你要聊天的目标拉到组里，这样他们就可以在这个组（房间）聊天，我们把一对一好友聊天当成由两个人组成组，这样只需在组表里加个标识，是好友聊天即可。默认这个组是群聊，   

      组成员表的意思就记录这个房间有多少人，多少人可以接受到这个房间的消息，

      聊天记录表顾名思义。

       好友移除表的话，这个是为了记录，拉黑，单向删除，双向删除，比如我和小米是好友，我把小米删除了，但是小米的好友里还是有我，这个情况我们就不能把整个好友关系删除了，所以在这边要记录，我删除了小米，我的好友列表里没有小米，小米有我，当小米也把我删除时候，我们两人的关系才彻底删除，此时就要把我们之间组成的组，房间号给彻底删除。

以下是数据库

组表：

CREATE TABLE [dbo].[Groups](  [Id] [bigint] IDENTITY(1,1) NOT NULL,  [GroupTitle] [nvarchar](250) NULL,  [GroupType] [int] NULL,  [FinalPost] [nvarchar](250) NULL,  [ChatType] [int] NULL,  [LastUserId] [bigint] NULL,  [UserId] [bigint] NULL,  [Status] [int] NULL,  [Created] [datetime] NULL,  CONSTRAINT [PK__Groups__3214EC0738D1CDA8] PRIMARY KEY CLUSTERED (  [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Groups] ADD  CONSTRAINT [DF__Groups__GroupTyp__5649C92D]  DEFAULT ((0)) FOR [GroupType] GO

ALTER TABLE [dbo].[Groups] ADD  CONSTRAINT [DF__Groups__LastUser__573DED66]  DEFAULT ((0)) FOR [LastUserId] GO

ALTER TABLE [dbo].[Groups] ADD  CONSTRAINT [DF__Groups__UserId__5832119F]  DEFAULT ((0)) FOR [UserId] GO

ALTER TABLE [dbo].[Groups] ADD  CONSTRAINT [DF__Groups__Status__592635D8]  DEFAULT ((0)) FOR [Status] GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'自增序列' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Groups', @level2type=N'COLUMN',@level2name=N'Id' GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'组标题' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Groups', @level2type=N'COLUMN',@level2name=N'GroupTitle' GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'组类型1是单聊天2是群聊' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Groups', @level2type=N'COLUMN',@level2name=N'GroupType' GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最后发表内容' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Groups', @level2type=N'COLUMN',@level2name=N'FinalPost' GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'消息类型' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Groups', @level2type=N'COLUMN',@level2name=N'ChatType' GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最后发表者' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Groups', @level2type=N'COLUMN',@level2name=N'LastUserId' GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建者' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Groups', @level2type=N'COLUMN',@level2name=N'UserId' GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'状态' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Groups', @level2type=N'COLUMN',@level2name=N'Status' GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Groups', @level2type=N'COLUMN',@level2name=N'Created' GO

EXEC sys.sp_addextendedproperty @name=N'Groups', @value=N'组表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Groups' GO

组成员表：

CREATE TABLE [dbo].[GroupMembers](  [Id] [bigint] IDENTITY(1,1) NOT NULL,  [GroupId] [bigint] NULL,  [GroupUserId] [bigint] NULL,  [Status] [int] NULL,  [Created] [datetime] NULL, PRIMARY KEY CLUSTERED (  [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

GO

ALTER TABLE [dbo].[GroupMembers] ADD  DEFAULT ((0)) FOR [GroupId] GO

ALTER TABLE [dbo].[GroupMembers] ADD  DEFAULT ((0)) FOR [GroupUserId] GO

ALTER TABLE [dbo].[GroupMembers] ADD  DEFAULT ((0)) FOR [Status] GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'自增序列' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'GroupMembers', @level2type=N'COLUMN',@level2name=N'Id' GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'组ID关联组表ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'GroupMembers', @level2type=N'COLUMN',@level2name=N'GroupId' GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'组成员ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'GroupMembers', @level2type=N'COLUMN',@level2name=N'GroupUserId' GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'状态' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'GroupMembers', @level2type=N'COLUMN',@level2name=N'Status' GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'GroupMembers', @level2type=N'COLUMN',@level2name=N'Created' GO

EXEC sys.sp_addextendedproperty @name=N'GroupMembers', @value=N'组成员表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'GroupMembers' GO

聊天记录表：

CREATE TABLE [dbo].[Messages](  [Id] [bigint] IDENTITY(1,1) NOT NULL,  [ChatContent] [ntext] NULL,  [ChatType] [int] NULL,  [SendingStatus] [int] NULL,  [GroupId] [bigint] NULL,  [UserId] [bigint] NULL,  [Created] [datetime] NULL, PRIMARY KEY CLUSTERED (  [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[Messages] ADD  DEFAULT ((0)) FOR [ChatType] GO

ALTER TABLE [dbo].[Messages] ADD  DEFAULT ((0)) FOR [SendingStatus] GO

ALTER TABLE [dbo].[Messages] ADD  DEFAULT ((0)) FOR [GroupId] GO

ALTER TABLE [dbo].[Messages] ADD  DEFAULT ((0)) FOR [UserId] GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'自增序列' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Messages', @level2type=N'COLUMN',@level2name=N'Id' GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'消息内容' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Messages', @level2type=N'COLUMN',@level2name=N'ChatContent' GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'消息类型' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Messages', @level2type=N'COLUMN',@level2name=N'ChatType' GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'发送状态' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Messages', @level2type=N'COLUMN',@level2name=N'SendingStatus' GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'组Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Messages', @level2type=N'COLUMN',@level2name=N'GroupId' GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建者ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Messages', @level2type=N'COLUMN',@level2name=N'UserId' GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Messages', @level2type=N'COLUMN',@level2name=N'Created' GO

EXEC sys.sp_addextendedproperty @name=N'Messages', @value=N'聊天记录表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Messages' GO

好友移除表

CREATE TABLE [dbo].[FriendsRemove](  [Id] [bigint] IDENTITY(1,1) NOT NULL,  [GroupId] [bigint] NULL,  [UserId] [bigint] NULL,  [Status] [int] NULL,  [Created] [datetime] NULL, PRIMARY KEY CLUSTERED (  [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

GO

ALTER TABLE [dbo].[FriendsRemove] ADD  DEFAULT ((0)) FOR [GroupId] GO

ALTER TABLE [dbo].[FriendsRemove] ADD  DEFAULT ((0)) FOR [UserId] GO

ALTER TABLE [dbo].[FriendsRemove] ADD  DEFAULT ((0)) FOR [Status] GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'自增序列' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FriendsRemove', @level2type=N'COLUMN',@level2name=N'Id' GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'组Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FriendsRemove', @level2type=N'COLUMN',@level2name=N'GroupId' GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'删除记录人ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FriendsRemove', @level2type=N'COLUMN',@level2name=N'UserId' GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'状态' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FriendsRemove', @level2type=N'COLUMN',@level2name=N'Status' GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FriendsRemove', @level2type=N'COLUMN',@level2name=N'Created' GO

EXEC sys.sp_addextendedproperty @name=N'FriendsRemove', @value=N'临时好友移除表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FriendsRemove' GO

次博客本人原创  如果各位有伙伴有不懂之处，或者发现博主的设计缺点 欢迎指出   转载  林啊铖  QQ 964878912