首页 > 微信 > 详细

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

时间:2018-11-25 21:36:30      阅读:278      评论:0      收藏:0      [点我收藏+]

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

技术分享图片 技术分享图片

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

 这里我先说下我的设计思路组表的意思和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

 

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

原文:https://www.cnblogs.com/lintaicheng/p/10017490.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!