私信基本功能数据库设计

上一篇写了基于resin4.0+websocket实现私信功能服务端消息推送文章,趁热打铁,在写一篇关于私信功能的数据库设计文章,非代码篇,希望想对第一次做设计并开发私信功能的同学有点帮助。

项目需求:私信功能,实现像对方发送私信消息后,在我的私信列表页面显示与发送或者接受消息的人列表,列表每条记录只显示与该对话的最新的一条消息。 点击列表中的任意一条,进入到消息对话详情页面,按照倒序显示该对话的详细内容。同时在这两个页面都可以进行删除对话,私信列表页面删除是与对方的所有会话,私信详情页面删除的是某一条对话,而且单方删除对话记录,不影响对方查看。(有点绕。。。)

软件环境: mysql

说了这么多,其实总结起来就那么几个重要的点,一是私信列表每条记录只显示最后一条记录,二是单方删除对话记录,不影响对方查看。先上数据表,然后在逐一解释下。

CREATE TABLE `private_message` (
  `id` bigint(20) NOT NULL auto_increment COMMENT '主键Id',
  `user_id` bigint(20) NOT NULL COMMENT '发送者Id',
  `friend_id` bigint(20) NOT NULL COMMENT '接受者Id',
  `sender_id` bigint(20) NOT NULL COMMENT '发送者id',
  `receiver_id` bigint(20) NOT NULL COMMENT '接受者Id',
  `message_type` tinyint(4) NOT NULL COMMENT '消息类型,1:普通消息 2:系统消息',
  `message_content` varchar(500) NOT NULL COMMENT '消息内容',
  `send_time` datetime NOT NULL COMMENT '消息发送时间',
  `status` tinyint(4) NOT NULL default '1' COMMENT '消息状态 1:未读 2:已读 3:删除',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

建立private_message表,字段说明: id:主键,自增长 user_id: 发送者id,非真实发送者id friend_id: 接受者id,非真实接受者id sender_id:发送者id,真实的发送者id receiver_id:接受者id,真实的接受者id message_type:消息类型,1:普通消息 2:系统消息,区分消息列表,可以发送不同类型的消息内容 message_content:消息内容 send_time:消息发送时间 status:消息状态 1:未读 2:已读 3:删除,标记不同消息状态,可以实现统计未读消息数,逻辑删除用户恢复等

看到这里大家该郁闷了,怎么弄两个发送者id,接受者id呢?

这里因为考虑到单方删除记录,不影响对方查看的功能,所以这里面我们需要在发送私信时,插入两份一样content内容的数据,但是在user_id,friend_id上面做点手脚了,在两次插入数据时,第二次插入的数据跟第一次插入的数据的user_id和friend_id对调。也就是:

INSERT INTO `private_message` VALUES ('1', '121', '127', '121', '127', '1', 'hello word', '2015-09-09 10:25:43', '2');
INSERT INTO `private_message` VALUES ('2', '127', '121', '121', '127', '1', 'hello word', '2015-09-09 10:26:41', '1');
INSERT INTO `private_message` VALUES ('3', '127', '121', '127', '121', '1', '你是程序猿吗?', '2015-09-11 10:30:16', '2');
INSERT INTO `private_message` VALUES ('4', '121', '127', '127', '121', '1', '你是程序猿吗?', '2015-09-11 10:30:59', '2');

这么一来,就可以满足我们的需求了。第一条和第四条记录是给121用户看,第二条和第三条记录是给127看的,121删除的时候删除第一条或者第四条记录,当然不会影响127看第二条和第三条记录啦!!!

好了,现在可以搞定其他的功能需求了。 1、我的私信列表

SELECT p.id, COUNT(p.id) AS message_count,p.user_id,p.friend_id,p.sender_id,p.receiver_id,p.send_time,p.message_content, u.`name` AS receiver_name,u.img_url AS receiver_image FROM (SELECT * FROM private_message ORDER BY id DESC) p INNER JOIN user u on u.id=friend_id WHERE p.user_id=121 and p.`status` !=3 GROUP BY p.friend_id ORDER BY p.id DESC limit 0,10

2、我的私信列表详情

SELECT p.id,p.message_content,p.sender_id,p.receiver_id,p.send_time,u.`name` AS sender_name,u.img_url AS sender_image,uu.`name` AS receiver_name FROM private_message p INNER JOIN user u on u.id=p.sender_id INNER JOIN user uu on uu.id=p.friend_id WHERE p.user_id=121 and p.friend_id=127 and p.`status` !=3 ORDER BY p.id DESC limit 0,10

3、我的私信列表页面删除整个会话

UPDATE private_message SETstatus=3 WHERE user_id=121 AND friend_id=127

4、我的私信列表详情删除单个对话

UPDATE private_message SET status=3 WHERE id=1

5、获取用户未读消息数量

SELECT COUNT(*) FROM private_message WHERE user_id=121 AND receiver_id=127 AND status=1

当然,还可以更新未读消息为已读,将已删除的用户从回收站中恢复过来,发送系统消息等等都可以实现的啦。这是,肯定有同学会说了,这个表设计的数据冗余,每条记录插入两遍,content内容多的话或者发送系统消息时,表数据太大,当然这个只是针对小型的私信功能,肯定跟大型专注于社交类网站不一样了,但是我们也可以将content内容拆分出去,新建一个content表,这里关联下id就可以减少数据冗余了。还有就是这个设计不涉及到高并发访问啦!涉及到高并发这个那就得更复杂的设计和方法途径解决啦!

最后,贴上项目私信功能截图,作为对上边的设计的体现吧!

我的私信列表页面:

我的私信列表详情页面:

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

MySQL中的Online DDL(第一篇)(r11笔记第3天)

记得有一天快下班的时候,一位开发同事找到我说,需要对一个表做变更,数据量据说有上千万,而当时是使用的MySQL版本是5.5,这可如何是好,对于在线业务要求高的情...

3349
来自专栏逸鹏说道

SQL Server 重新组织生成索引

概述 无论何时对基础数据执行插入、更新或删除操作,SQL Server 数据库引擎都会自动维护索引。随着时间的推移,这些修改可能会导致索引中的信息分散在数据库...

3398
来自专栏Java开发

Spring集成MyBatis 通用Mapper以及 pagehelper分页插件

这里送上楼主配置的Spring DataSource 和 TransactionManager 和MyBatis配置

1062
来自专栏MYSQL轻松学

MySQL系列优化(一)

MYSQL优化是一个非常大的课题,这篇文章主要介绍了跟MYSQL相关的4个方面,如果想深入研究可以查下相关资料。 ---- 一、服务器级别优化 二、操作系统级别...

2785
来自专栏杨建荣的学习笔记

通过shell定制ash脚本(r3笔记第33天)

ash是在10g以来一个很有用的特性,能够作为awr的补充,对于排查一些历史的问题能够提供更加详细和针对性的数据。 当然个人在使用ash的时候感觉最慢的地方就是...

3044
来自专栏维C果糖

史上最简单的 MySQL 教程(四十一)「触发器」

触发器:trigger,是指事先为某张表绑定一段代码,当表中的某些内容发生改变(增、删、改)的时候,系统会自动触发代码并执行。

42514
来自专栏腾讯云TStack专栏

五分钟聊T-SQL:数据压缩

传说中数据压缩能压缩到原始数据的1/10,但是... ... 但是至少目前为止我还没遇到过这样的情形,通常情况下能压缩到原始数据的1/5-2/5的样子。

2952
来自专栏Java爬坑系列

【MySQL疑难杂症】如何将树形结构存储在数据库中(方案二 Path Enumeration)

  今天来介绍把树形结构存入数据库的第二种方法——路径枚举法。   还是借用上一篇的栗子,为了方便大家查阅,我把图又原样搬过来了。 image.png   需...

2378
来自专栏眯眯眼猫头鹰的小树杈

树状结构存储与读取之Modified Preorder Tree

一直以来存储树状结构都采用经典的结构<id,pid>的组合,即每一个节点持有其父节点的ID,并由此构成完整的树状结构。但是这样的结构在遇到大量的查询时会成为严重...

461
来自专栏技术博文

从MyISAM转到InnoDB需要注意什么

转自 MySql中文网 http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ==&mid=200910426&idx=1...

34214

扫码关注云+社区