会话消息系统的SQL表设计

Question

在我的MySQL数据库支持的web应用程序中，我想提供一个消息系统，将消息分组为多个用户之间的对话，但我一直在设计一个满足我一些需求的表结构：

• 多个用户可以参与一个对话
• 用户可以加入、阅读、离开和删除对话
• 收件箱视图应生成尽可能少的查询

现在，多对多关系的第一个要求可以通过使用连接表来解决。但事实证明，在为收件箱视图编写选择查询时，它会产生很多问题。

第二项要求也被证明是一项挑战。如果用户离开了一个对话，他应该仍然可以阅读旧消息。对话中其余用户之间的新消息不应与离开的用户共享。我首先想到的是使用树状结构进行对话。每次用户加入或离开会话时，都会创建一个新的会话，其中引用父会话，并创建与连接表中其余参与者的新关系。

第三个要求似乎也并非微不足道。收件箱视图应显示与作为参与者的特定用户的对话列表。此外，还应为每个对话显示附加信息：所有当前参与者的姓名、对对话的最后回复以及该回复的作者。将收件箱视图视为一个邮件列表，其中包含有关所属对话的附加信息。

我目前的方法是这样的：

CREATE TABLE `conversation` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `parentId` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `parentId` (`parentId`),
  CONSTRAINT `conversation_ibfk_1` FOREIGN KEY (`parentId`) REFERENCES `conversation` (`id`)
);
CREATE TABLE `participant` (
  `userId` int(11) unsigned NOT NULL,
  `conversationId` int(11) unsigned NOT NULL,
  `readAt` datetime DEFAULT NULL,
  PRIMARY KEY (`userId`,`conversationId`),
  KEY `conversationId` (`conversationId`),
  CONSTRAINT `participant_ibfk_2` FOREIGN KEY (`conversationId`) REFERENCES `conversation` (`id`),
  CONSTRAINT `participant_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
);
CREATE TABLE `reply` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `conversationId` int(11) unsigned NOT NULL,
  `userId` int(11) unsigned NOT NULL,
  `text` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `conversationId` (`conversationId`),
  KEY `userId` (`userId`),
  CONSTRAINT `reply_ibfk_2` FOREIGN KEY (`userId`) REFERENCES `user` (`id`),
  CONSTRAINT `reply_ibfk_1` FOREIGN KEY (`conversationId`) REFERENCES `conversation` (`id`)
);
CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
);

我在这里遇到了困难，找不到一个能满足我所有需求的解决方案。也许这里有人可以给我一些关于如何进行数据库设计的建议。

Answer 1

用户活动

我仍然会制作一个连接表，但要在元组上添加索引。

USER_CONV   
---------------
id_user 
id_conversation
active_flag
begin_flag              - flag indicating if id_first_reply == id_first_visible_reply
id_first_reply          - first reply in the conversation
id_first_visible_reply  - first visible for a given user
id_last_reply           - last visible for a given user, user if active_flag = false, otherwise NULL
index (id_user, id_conversation, active_flag) 

USER_REPLY
---------------
id_user
id_conversaion
id_reply
index (id_user, id_conversation, id_reply) 

USER_CONV应该可以减少创建收件箱的痛苦。不过，您还需要使用USER_REPLY，然后使用REPLY来加入它。

收件箱

如果您想让它变得快速，那么创建一种CACHED_INBOX，并创建一个具有MOST_RERECENT_USER_ACTIVITY的表。CACHED_INBOX表将包含所有收件箱数据，您不需要进行任何联接来获取相关数据，但您只需要对MOST_REQUENT_USER_ACTIVITY中的数据进行UNION。MOST_RERECENT_USER_ACTIVITY应该很小（工作速度很快），CACHED_INBOX将是"静态"的。然后每天一次，或者在服务器不太可能使用的时候每隔几天进行一次批处理CACHED_INBOX更新。除非你的用户决定永远保持对话，否则它会起作用。

优化

当然，您需要使用explain来了解查询优化器是如何使用索引的（如果它使用索引的话）。我想你需要一个像ACTIVE_CONVERSATIONS这样的表，它不会有任何索引，这会影响性能。但是，您需要对USER_CONV进行一些批量更新，这将具有更广泛的索引。你必须尝试一下，看看你对用户行为的期望是什么，这应该是架构驱动因素之一。

注意：关于索引，有一个很好的网站专门介绍索引使用的主题-e-index-luke.com