关于 OSCHINA 留言改进牵涉的表结构调整思路

红薯 发布于 2012/02/19 15:14
阅读 1K+
收藏 19

OSCHINA 现有点对点留言记录包括两个表,分别是接收留言表和已发送留言表,表结构分别为:

接收留言表:

/*==============================================================*/
/* Table: osc_msgs                                              */
/*==============================================================*/
create table osc_msgs
(
   id                   int unsigned not null auto_increment,
   sender               int unsigned not null,
   receiver             int unsigned not null,
   type                 tinyint not null,
   content              text not null,
   send_time            timestamp not null default CURRENT_TIMESTAMP,
   read_time            datetime,
   status               tinyint not null,
   primary key (id)
);

/*==============================================================*/
/* Index: idx_msg_user                                          */
/*==============================================================*/
create index idx_msg_user on osc_msgs
(
   receiver
);

/*==============================================================*/
/* Index: idx_msg_sender                                        */
/*==============================================================*/
create index idx_msg_sender on osc_msgs
(
   sender
);

已发送留言表:

drop table if exists osc_msg_sents;

/*==============================================================*/
/* Table: osc_msg_sents                                         */
/*==============================================================*/
create table osc_msg_sents
(
   id                   int unsigned not null,
   sender               int unsigned not null,
   receiver             int unsigned not null,
   type                 tinyint not null,
   content              varchar(1000) not null,
   send_time            timestamp not null default CURRENT_TIMESTAMP,
   read_time            datetime,
   status               tinyint not null,
   primary key (id)
);

/*==============================================================*/
/* Index: idx_msg_sender                                        */
/*==============================================================*/
create index idx_msg_sender on osc_msg_sents
(
   sender
);

但现在想对留言功能进行改造,改成类似微博私信的方式,进入留言箱显示的是最新留言的人以及他的最新留言内容,当点击留言进去后会按时间列出与该人的所有留言记录。

如果按照现有的表结果要完成这个功能会很复杂,而且效率奇差。

因此初步设想是删除“已发送留言表”,将已发送留言表的所有记录迁移到接收表中,同时将接收表中增加两个字段:user, friend 分别代表我的帐号ID以及对方的ID,不管是我发送对方,还是对方发给我的,user 和 friend 字段都是我和对方的ID,而 sender 和 receiver 分别表示这条消息是谁发给谁的。

现在加入我发一条留言给小编辑,那么在新的留言表中会有两条记录,只是其中 user 和 friend 以及 sender 和 receiver 字段的值对调一下即可。

我要查询跟某人的留言记录只需要

SELECT  * FROM osc_msgs WHERE user = <me> AND friend = <other> ORDER BY id DESC;

然后显示数据的时候再根据 sender 和 receiver 的不同来辨别是谁发给谁的。

而留言箱首页显示所有往来的用户也很简单:

SELECT friend, MAX(id) AS msg_id FROM osc_msgs WHERE user = <me> GROUP BY friend ORDER BY msg_id DESC;

你有更好的解决办法吗???

备注,很多人误解此贴的意义,请看10楼回帖 :)

加载中
1
ValueError
ValueError

这样一条留言不就有两条记录的数据了吗,为啥不把发送关系表拆出来呢……

== message
   - id
   - content
   - status

== send_message
   - id
   - message_id
   - sender_id
   - receiver_id

这样不是好些吗……

Jeff_Lau_NUAA
Jeff_Lau_NUAA
@红薯 : 删除当然不能物理删除,只是状态对某人状态变化而已
一号男嘉宾
一号男嘉宾
用一个字段来存储接收人,比如你删除了,那么就update接收人,发送的人还是可以看见,只是你看不见而已。
红薯
红薯
@无知的TonySeek : 我觉得存放两条记录完全没有问题啊,多一点空间而已,但会让程序变得简单
ValueError
ValueError
@红薯 : 拆出来的 send_message 表可以放两条记录哇,这样 message 就不用重复了。或者用软删除也行。
红薯
红薯
你想下,如果我发给你的留言,你删除了,那岂不是我看不到我已发送的记录了?
1
xmdeepdata
xmdeepdata

如此说来,sender与receiver只要一个就可以了。既可以放user,又可以放friend。

判断是发出的,还是接收的,只要用sender=user,或者sender!=user即可

红薯
红薯
这个有道理,倒是省了一个字段
1
mark35
mark35
红苕可以参考DZ7.2+里面ucenter库的pm表结构:一个表的一条记录同时包含收发信息。有个字段表示删除状态:比如0:正常;1:接收方删除;2:发送方删除;
mark35
mark35
@仰望星空 : 程序上你可以选择物理删除记录,也可以增加第4个状态值——双方都删除
th小米粥
th小米粥
如果双方都删除了呢》数据库里边的数据不会删除呀?只是改变状态咯。
mark35
mark35
@红薯 : 是的。除非你不想物理删除此记录,那么就需要再一个值来表示双方都删除
红薯
红薯
如果两个人都标识了删除,这条记录就会删除吗
0
雷志伟
雷志伟
老大, 是时候上 Redis 啦!
红薯
红薯
这里讨论的是设计思路,就算引入 Redis 也有同样的问题啊:)
ValueError
ValueError
在一个已经很稳定的生产环境中引入一个新东西是一件多么菊紧的事情啊……
0
Jeff_Lau_NUAA
Jeff_Lau_NUAA
CREATE TABLE IF NOT EXISTS osc_msgs
(
   MsgID                      INT                            NOT NULL AUTO_INCREMENT,
   FolderName                 VARCHAR(30)                    NOT NULL,
   UserID                     INT                            NOT NULL,
   MsgSenderID                INT                            NOT NULL,
   MsgSenderName              VARCHAR(30)                    NOT NULL,
   MsgToList                  VARCHAR(250)                   NOT NULL,
   MsgCcList                  VARCHAR(250),
   MsgBccList                 VARCHAR(250),
   MsgTopic                   VARCHAR(250)                   NOT NULL,
   MsgBody                    TEXT                           NOT NULL,
   MsgType                    INT                            NOT NULL,
   MsgOption                  INT                            NOT NULL,
   MsgStatus                  INT                            NOT NULL,
   MsgReadStatus              INT                            NOT NULL,
   MsgNotify                  INT                            NOT NULL,
   MsgCreationDate            DATETIME                       NOT NULL,
   PRIMARY KEY (MsgID)
);

CREATE INDEX Msg_1_idx on osc_msgs
(
   FolderName,
   MemberID
);

CREATE INDEX Msg_2_idx on osc_msgs
(
   MsgSenderID
);

红薯
红薯
你这个表是一个最基本的留言表,跟我的类似,但我要显示两个人的对话就没法用简单的查询搞定,用到或查询对性能损害非常大,而且这个表的记录数很大
Jeff_Lau_NUAA
Jeff_Lau_NUAA
@红薯 : 这张表可以解决你的问题啊,一条信息只一条记录,不管是谁发给谁,但接收人为UserID, 发送人为MsgSenderID,两个人的记录也很容易查询,我们一直用这张表来解决这个问题的
红薯
红薯
没看出你这个表有什么特点,而且应该无法简单解决我提出的问题
0
一号男嘉宾
一号男嘉宾
<script type="text/javascript">
alert("Hello红鼠");
</script>
雷志伟
雷志伟
呵呵.
0
雷志伟
雷志伟
用户有一个 "对话列表", 
按最近的留言 MsgID 保存对方的 UserID 和 MsgID,  再通过 MsgID 取得对话内容.
( MsgID 为自增, 所以 MsgID 的排序也是 时间 的排序.  )

发送留言时, 更新发送方 和接收方 两个列表.

OK, 搞定.
雷志伟
雷志伟
@红薯 : 呵呵, 我说是的 Redis
雷志伟
雷志伟
@红薯 : 下班回家, 晚上再跟你聊哈.
红薯
红薯
然后列表的时候要关联两个表,你说系统能不慢吗?
0
红薯
红薯
我不喜欢“标识为删除”这样的设计,纯粹个人喜好,尽管彻底删除有时候会有不少问题,有时候会让自己很辛苦的从备份库里恢复被误删的数据。
th小米粥
th小米粥
如果在留言表中增加一个字段 留言一次增加二条数据怎么样?
0
Inside
Inside
-- 便笺
create table note(
    oid serial primary key
    , state enum_common_state default 'normal'
    , title varchar(128)
    , title_segment tsvector
    , content text
    , content_segment tsvector
);
comment on table note is '用来保存大大小小的消息,作为其它服务的支持而存在。';
--------------------------------------------------------------------------------
-- 站内信
create type enum_dm_state as enum('normal', 'unread', 'deleted');
create table direct_message(
    oid serial primary key
    , sender int not null
    , receiver int not null
    , note int not null
    , sender_state enum_dm_state not null
    , receiver_state enum_dm_state not null
    , create_on timestamp default current_timestamp
);

我之前做过的一个项目,也是选择一条记录同时保存发送方和接收方状态的方案。双方都删除之后我选择的是物理删除。

各种关系只查direct_message这个表就可以了,目前你描述的业务都可以满足,具体私信的内容则是在dm这个表找到id(或id集合)后再去查note表,用不着join操作,而note表是有缓存的,所以查询效率不用担心。

0
红薯
红薯

其实我发这个帖子的目的不是探讨留言表怎么设计,而是要实现如下图的聊天记录展示方式

返回顶部
顶部