线程消息系统数据库架构设计

关键词：

【中文标题】线程消息系统数据库架构设计【英文标题】：thread messaging system database schema design 【发布时间】：2011-09-26 08:17:28 【问题描述】：

我正在努力实现此处所解释的内容： Creating a threaded private messaging system like facebook and gmail, 但是我不完全理解乔尔布朗的回答。谁能解释一下。

这是我的数据库表与示例数据的样子（我假设我出于演示目的正确填写了它）：

我需要显示基于 LoginId 的线程列表（最新的在顶部）查询在 LINQ 中会是什么样子？ （我要问的是在一组消息线程中，给我每个线程中的 1 条最新消息） - 就像在 facebook 上完成的那样。

我需要在消息线程 (LINQ) 中显示所有消息 -> 就像在 facebook 上完成一样，您在其中单击消息时会看到整个“对话”。

请帮忙！ 谢谢

编辑 -> 继续 乔尔，这样对吗？

Joel，我有点困惑，你能解释一下吗（cmets/questions 以粗体显示）：

这里的想法是，每次用户启动一个全新的线程/消息时，它都会从 THREAD 表中的一条新记录开始。然后将用户添加为 THREAD_PARTICIPANT，并将消息的内容添加到指向包含 THREAD 的 MESSAGE。从 MESSAGE 到 USER 的 FK 表示消息的作者。

LoginId 1 向 LoginId2 发送消息 => 将新记录插入到 MessageThread 表中。还将一条记录插入到 MessageThreadParticipant 记录中，MessageThreadId = 1，LoginId = 1（发送者）。并在 Message 表中插入一条新记录，MessageId =1，MessageThreadid =1，SenderLoginId = 1（正确？？）

这是我在那次迭代之后所拥有的：

我想我很困惑，因为 Loginid 2 无法知道有消息给他。 ??或者也许我需要在 MessageThreadParticipant 中插入 2 条记录？ （发送者和接收者）-> 这样都可以看到整个“对话”？？

EDIT2： 乔，我想我可以这样做：

SELECT
  Message.MessageId, Message.CreateDate, Message.Body, Login.Username, Message.SenderLoginId
, (SELECT MessageReadState.ReadDate 
   FROM MessageReadState 
   WHERE MessageReadState.MessageId = Message.MessageId 
     ) as ReadDate
FROM Message 
    INNER JOIN Login ON Message.SenderLoginId = Login.LoginId
    INNER JOIN MessageThreadParticipant mtp on mtp.MessageThreadId = Message.MessageThreadId 
AND ( Message.MessageId in 
        ( SELECT Max(Message.MessageId)
          FROM MessageThreadParticipant INNER JOIN Message 
            ON MessageThreadParticipant.MessageThreadId = Message.MessageThreadId
          GROUP BY MessageThreadParticipant.MessageThreadId
        )
      )
Where mtp.LoginId = 2
ORDER BY Message.CreateDate DESC;

如果我错了，请纠正我:)

【问题讨论】：

Shane，我想你明白了。查看我为扩展示例数据编辑的答案。 Shane，我认为您无法使用编辑 2 中的查询来使其正常工作。我看到了两个问题。一是您只会看到相关用户阅读的消息，因为您是在内部加入 MTP。另一个是你试图在你的 FROM (...Message.MessageId in ( SELECT Max(Message.MessageId) ...) 中进行子选择，如果它完全有效的话，这是非常非常规的，我不这样做'认为它不会，因为您不能加入多值相等。子选择属于 WHERE 子句。 【参考方案1】：

那你为什么不问问？ :)

让我试着确定我对您的要求的理解。在我看来，您正在查看的线程是两个人之间消息的线性列表（而不是树）。我认为您可能希望允许更多的人进入，而不仅仅是两个。这就像 Facebook，只要有人发布一条消息，然后任何数量的人都可以阅读它，然后开始添加 cmets。当您添加评论时，它会将您放入线程中，并且您开始收到状态更新和电子邮件，告诉您线程中的活动等等。假设这就是您所追求的，那么我向Big Mike 建议的架构并不是您想要的。

请考虑以下内容：

这里的想法是，每次用户启动一个全新的线程/消息时，它都会从 THREAD 表中的一条新记录开始。然后将用户添加为 THREAD_PARTICIPANT，并将消息的内容添加到指向包含 THREAD 的 MESSAGE。从 MESSAGE 到 USER 的 FK 表示消息的作者。

当用户阅读消息时，他们会在 MESSAGE_READ_STATE 表中获得一个条目，以表明他们已将消息标记为已读，无论是显式还是隐式，具体取决于您的要求。

当有人在线程中的初始消息上遇到问题时，第二个 MESSAGE 会添加一个 FK 返回到原始 THREAD，并且回复作者（用户）被添加到 THREAD_PARTICIPANT 表中。当消息被一个、两个甚至更多的参与者添加到线程中时，情况也是如此。

要获取任何线程中的最新消息，只需从 MESSAGE 中取出按创建日期（或身份密钥）降序排序的前 1 条消息，其中消息 FK 是发给感兴趣线程的。

要获取用户最近更新的线程，请从消息中获取与前 1 个相关的线程，该消息按创建日期降序排序，其中消息位于用户是 THREAD_PARTICIPANT 的线程中。

恐怕我永远无法在 LINQ 中陈述这些事情而不打破 LinqPad。如果您无法从上面理解我的偏差，我可以用表定义和一些 SQL 来充实答案。在 cmets 中询问即可。

编辑：需求和实施说明

澄清要求：最初我考虑的是公开发布消息并有机会发表评论，而 Shane 则更多的是直接消息功能。在这种情况下，最初的收件人需要一开始就包含在 THREAD_PARTICIPANT 表中。

为了清楚起见，让我们在表格中放置几行。这是场景，（为了纪念加拿大国庆日）：用户 1 向用户 2 发送消息，询问关于开会喝啤酒的事情。用户 2 回答了一个关于在哪里见面和用户 1 回答的问题。表格看起来像这样：（可能过于简单）

编辑 #2：访问 SQL 以获取线程中所有消息的列表，具有读取状态...

使用@OP 的模式，此SQL 将获取给定线程中的消息列表，并指示给定用户是否已阅读每条消息。消息是最新的第一顺序。

SELECT 
  Message.MessageId
, Message.CreateDate
, Message.Body
, Login.Username
, (SELECT MessageReadState.ReadDate 
   FROM MessageReadState 
   WHERE MessageReadState.MessageId = Message.MessageId 
     and MessageReadState.LoginId = 2) as ReadState
FROM (Message INNER JOIN Login ON Message.SenderLoginId = Login.LoginId) 
WHERE (((Message.MessageThreadId)=10))
ORDER BY Message.CreateDate DESC;

请注意，如果可以公平地说，诀窍是读取状态是通过子选择获取的。这是必要的，因为获取读取状态的部分标准需要外连接无法满足的 where 子句。因此，您可以使用子选择从 MessageReadState 子表中确定您想要哪个（可能缺少）值。

编辑 3：SQL 用于获取给定用户的所有线程以及每个线程的最新消息...

要获取给定用户参与的所有线程的列表，首先按最新消息排序，仅显示最新消息（每个线程 1 条消息），然后您可以使用与上面的一个，除了不是通过他们的 FK 过滤消息到感兴趣的线程，您通过子查询过滤消息，该子查询在感兴趣的用户参与的每个线程中找到最新消息。它看起来像这样：

SELECT
  Message.MessageId
, Message.CreateDate
, Message.Body
, Login.Username
, (SELECT MessageReadState.ReadDate 
   FROM MessageReadState 
   WHERE MessageReadState.MessageId = Message.MessageId 
     and MessageReadState.LoginId = 2) AS ReadState
FROM Message INNER JOIN Login ON Message.SenderLoginId = Login.LoginId
WHERE ( Message.MessageId in 
        ( SELECT Max(Message.MessageId)
          FROM MessageThreadParticipant INNER JOIN Message 
            ON MessageThreadParticipant.MessageThreadId = Message.MessageThreadId
          WHERE MessageThreadParticipant.LoginId=2
          GROUP BY MessageThreadParticipant.MessageThreadId
        )
      )
ORDER BY Message.CreateDate DESC;

【讨论】：

@Spock - OP 的要求没有说明用户删除消息的任何内容，但可以轻松增强模型以适应选择“删除”消息的个人用户，同时将消息留给其他用户看法。这可以在线程级别通过删除该用户的THREAD_PARTICIPANT 记录来完成，或者在消息级别通过向MESSAGE_READ_STATE 添加代码或标志来指示用户已软删除该消息。 @user1066133 - 我认为代码首先有它的位置，尤其是作为原型工具或用于构建小型或小众系统。我个人认为代码优先是对一些不以数据为中心的程序员的让步。根据我的经验，一个经过深思熟虑（即刻意设计）的数据库是任何规模或重要性的应用程序的坚实基础。如果您的项目允许，我会先从模型开始。如果您必须先编写代码，则只需确保不会跳过数据设计步骤而将自己描绘成一个非常糟糕的角落。 @compguy24 根据缺少的信息排除某些内容的常用方法是使用 sub-select 和 WHERE NOT IN ...。因此，例如，您可以获取基本查询并将其添加到其 where 子句 AND WHERE Message.MessageID NOT IN (select clause that pulls out the message ids of the thing that you're waiting for) @DaveHarding - 是的，如果您想区分消息 read 和消息 deleted，您可以扩展 MESSAGE_READ_STATE 表以包括一个已删除的标志，或者最好是一个已删除的日期。这将提供软删除功能（以便您可以取消删除，如果您愿意），它允许线程中的一个参与者隐藏某些内容，而不会破坏其他线程参与者的数据。 如何检查两个用户之间是否已经存在线程，假设用户 1 和用户 2 已经存在线程 id=10，因此无需创建新线程。【参考方案2】：

根据 Joel Brown 的回答，您可以将 LAST_MESSAGE_ID 列添加到 THREAD 表中，然后获取所有线程的最后一条消息 SQL 变得非常简单。您必须在每条消息发送时更新此列。

获取给定用户的所有线程以及每个线程的最新消息

SELECT *
FROM THREAD T
INNER JOIN MESSAGE M ON T.LAST_MESSAGE_ID=M.MESSAGE_ID
INNER JOIN USER SENDER ON M.USER_ID=SENDER.USER_ID
LEFT JOIN MessageReadState MRS ON M.MESSAGE_ID=MRS.MESSAGE_ID AND MRS.USER_ID=2

【讨论】：

如何获取给定两个用户的 thread_id ？我需要这种查询吗 -SELECT * FROM message_thread_has_user as t1 join message_thread_has_user as t2 on t1.message_thread_id=t2.message_thread_id where (t1.user_id = 1 || t2.user_id = 1) and (t1.user_id = 2 || t2.user_id = 2) 按 t1.message_thread_id 分组 @Vivek 你应该在 MessageThread 表中添加一个唯一的 char 字段 =“unique_field”。它存储从最小用户 ID 到最大用户 ID 排序的用户 ID，并使用分隔符连接。示例：user1=15, user2=9 和 unique_field 将是“9_15”，sql 将是 select * from MessageThread where unique_field="9_15" @Alaxander 确实有意义，这将避免连接。谢谢。 @Vivek 你也可以使用 ThreadName 字段而不是为一对一的消息线程创建“unique_field”

分布式公布订阅消息系统kafka架构设计

我们为什么要搭建该系统Kafka是一个消息系统，原本开发自LinkedIn，用作LinkedIn的活动流（activitystream）和运营数据处理管道（pipeline）的基础。如今它已为多家不同类型的公司 作为多种类型的数据管道（datapipeline）和消息系... 查看详情

消息系统架构设计演进

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 查看详情

架构设计：系统间通信（19）——mq：消息协议（上）

...述从本文开始，我们介绍另一类型的系统间通讯及输：MQ消息队列。首先我们将讨论几种常用消息队列协议的基本原理和工作方式，包括MQTT、XMPP、Stomp、AMQP、OpenWire等。然后在这个基础上介绍两款MQ产品：ActiveMQ和RabbitMQ，它们是... 查看详情

50架构实战：架构设计文档模板

...、目标、范围等]2.需求分析[5W1H8C]Who：需求利益干系人。消息队列系统主要是业务子系统来使用，子系统发送消息或者接收消息。When：需求使用时间。当子系统需要发送异步通知的时候，需要使用消息队列系统。What：需求的产... 查看详情

系统逻辑架构设计

...述用例的主题，并显示一组对象间随着时间变化所交换的消息。消息包括异步信号和过程调用。顺序模型擅长显示系统用户所观察到的行为顺序。顺序模型有两种：场景和顺序图。顺序图具有更加结构化的形式。 3.设计与需... 查看详情

kafka设计解析-kafka背景及架构介绍

摘要　　Kafka是由LinkedIn开发并开源的分布式消息系统，因其分布式及高吞吐率而被广泛使用，现已与ClouderaHadoop，ApacheStorm，ApacheSpark集成。本文介绍了Kafka的创建背景，设计目标，使用消息系统的优势以及目前流行的消息系统对... 查看详情

高并发大访问量架构设计演进之路归纳总结

...心要素：性能，可用，安全架构设计方案：系统，框架，数据库架构师的成长：搞定问题的攻略大型分布式架构优秀架构师必备的技能： 强烈的好奇心， 敏锐的业务嗅觉，工程技术，产生价值赚钱（科学研究不同） 扎实的软件... 查看详情

使用 socket.io node.js 和传入消息的通知系统的架构实现和设计

】使用socket.ionode.js和传入消息的通知系统的架构实现和设计【英文标题】：ArchitectureImplementationandDesignforaNotificationSystemusingsocket.ionode.jsandincomingmessages【发布时间】：2011-11-0305:11:21【问题描述】：免责声明我之前没有使用过node.js... 查看详情

软件设计要素初探：架构模式

...：将应用划分为多个层次，定义各层的接口、任务抽象及消息格式，以及各层之间的通信与交互。业务系统通常会划分为业务逻辑层、 查看详情

基于http协议订阅发布系统设计

...er)    订阅发布模式最典型的应用场景就是消息系统的设计。在消息系统的架构中，消息的发送者称作(publisher)，消息的接收者称作(subscriber)，参见wikipedia:Publish–subscribepatte 查看详情

高并发网站架构的设计方案是怎样的？

...s搭建前端服务系统！3，静态化处理:将页面，后台枚举，数据库定义表等使用静态处理方式做处理！4，文件服务器剥离:采用单独的文件服务器，防止页面加载的阻塞！5，缓存:使用redis，memcache等将运行时数据缓存，代替频繁的... 查看详情

ued视觉交互设计与流程介绍

...建高效的研发与自动化运维某大型电商云平台实践互联网数据库架构设计思路IT基础架构规划方案一(网络系统规划)餐饮行业解决方案之客户分析流程餐饮行业解决方案之采购战略制定与实施流程餐饮行业解决方案之业务设计流... 查看详情

智能系统中间件性能测试

...建高效的研发与自动化运维某大型电商云平台实践互联网数据库架构设计思路IT基础架构规划方案一(网络系统规划)餐饮行业解决方案之客户分析流程餐饮行业解决方案之采购战略制定与实施流程餐饮行业解决方案之业务设计流... 查看详情

im即时通讯综合消息系统的架构

如何设计一款高性能、高并发、高可用的im综合消息平台是很多公司发展过程中会碰到且必须要解决的问题。比如一家公司内部的通讯系统、各个互联网平台的客服咨询系统，都是离不开一款好用且维护的方便im综合消息系统... 查看详情

im即时通讯综合消息系统的架构

如何设计一款高性能、高并发、高可用的im综合消息平台是很多公司发展过程中会碰到且必须要解决的问题。比如一家公司内部的通讯系统、各个互联网平台的客服咨询系统，都是离不开一款好用且维护的方便im综合消息系统... 查看详情

系统架构聊聊开源消息中间件的架构和原理

说到消息中间件，身在互联网的童鞋们肯定下意识的就是高并发，高性能io调度等浮现在脑海，但是对应用来说，可能他的作用远不止性能这么简单，尤其是对与交易，金融打交道的业务平台来说。ok,下面给大家介绍一下金融交... 查看详情

实时消息rtm|多活架构中的数据一致性问题

一，容灾方案之前介绍过RTM的系统架构设计，其中有说到我们的容灾设计的是双活和多活。有很多小伙伴问到为什么不采用主从架构设计，以及多活容灾怎么做到数据一致性等问题。这里我们针对容灾和多活设计进行... 查看详情

架构师之路系列文章

...目录企业数字化转型架构师之路业务架构应用架构API经济数据库设计设计模式系统架构分布式系统RPC远程调用分布式消息队列分布式任务队列微服务架构ServiceComb部署架构高可靠、高可用、高并发、高性能、高可扩展集群高性能... 查看详情