创建一个类似于Facebook和Gmail的线程私信系统

Question

我正在创建一个类似于Gmail和Facebook的线程消息系统，其中收件箱列出了最近的线程，显示主题、发件人姓名和最新消息的时间戳。

以下是我的表设置：

users:
    user_id
    user_name

thread:
    thread_id
    title
    to_id
    to_keep
    to_read
    from_id
    from_keep
    date

message:
    message_id
    thread_id
    to_id
    from_id
    message_text
    date

我正在做的是，当用户创建一条新消息时，在线程表中创建一个新线程，然后在消息表中创建一条新消息；如果用户回复某个线程，则会在线程表中复制当前线程，但会交换“to_id”和“from_id”，然后基于此创建一条新消息。

对于收件箱视图，我可以根据“user_id”查询所有线程。所以可以使用类似于SELECT * FROM thread WHERE to_id = 2 and to_keep = TRUE ORDER BY date DESC这样的语句查询所有与该用户有关的线程；如果要查看发件箱中的消息，则可以使用类似于SELECT * FROM thread WHERE from_id = 2 and from_keep = TRUE ORDER BY date DESC这样的语句。

如果用户打开一个有新消息的线程，则将“to_read”更新为true：UPDATE thread SET to_read = TRUE WHERE thread_id = 4。

我觉得我可能过于复杂化了这个过程，应该有更好的方法来实现。如果有任何帮助或想法，都会被欢迎。

这种方式使我只需从线程表中选择所有内容，然后与用户表进行连接以显示所需的所有内容。但是，我觉得可能有更好的方法来做到这一点。

- bigmike7801

---

为什么线程需要一个 to_id 和一个 from_id？消息是将发送者和接收者联系在一起的东西。线程只是消息的容器。或者我理解错了吗？ - tom redfern

不，你说得对。我只是在查询选择应该在收件箱中的所有线程以及仅来自线程中最新消息的信息时遇到了困难。这样说清楚了吗？ - bigmike7801

Answer 1

为什么不将消息关系与用户对每个消息的视图分开呢？

我会通过消息上的自引用关系来进行线程处理。换句话说，消息有一个“responding_to_message_id”列。

我不确定你为什么要有一个“to_id”。消息是否针对个别用户？这似乎非常有限。我认为你要么没有收件人（即收件人是任何人都可以阅读的留言板），要么你可以像发送电子邮件一样指定多个收件人。也许你可以更多地解释一下系统的使用方式。

假设（为简单起见）你正在发布到一个留言板上，所以只有“from”很重要，那么你就有了你的消息表，用于线程处理的自引用关系，一个用户表，然后是用户和消息之间的交集表，存储哪些消息已被每个用户阅读。

这样，如果你想知道一个用户是否已经阅读了一条消息，只需尝试在给定消息的交集表中读取用户ID。如果它不存在，则该用户未读取该消息。

请注意，如果您想要单个收件人，则此设计适用，如果您想要多个收件人，则可以使用交集表来保存每个消息的收件人列表。如果您确实有一个收件人交集表，它可以兼顾您的阅读状态表。

编辑：ERD草图：

这是我所说的一个快速草图...

发送者是否选择保留消息在消息本身上被标记。如果消息是新线程的开始，则reply_to_message_id列为NULL，否则它是父消息的message_id。每个收件人都可以有自己保留或不保留消息的能力，以及追踪收件人阅读消息的日期和时间的能力。

编辑2：备用ERD和查询最新消息

@OP询问如何查询线程中最新的消息。答案取决于线程的形式。您可以拥有一个平面线程，其中每条消息都进入线性消息流的末尾，或者您可以拥有一个树形线程，其中每个消息都有一个特定的父级，除非它是线程的根。在上面的ERD中，reply_to_message_id字段可以以任一方式使用。如果线程是平面的，则FK始终为根MESSAGE。如果线程是树形的，则FK为回复MESSAGE的直接父级。

如果您要运行的典型查询是“线程中最新的消息是什么？”，并且您的线程是平面的，则可以使用此类SQL：

select top 1
  M.message_id
, M.sent_datetime
, M.title
, M.message_text
, S.user_id
, S.user_name
-- and anything else you want...
from MESSAGE M inner join USER S
  on M.sender_user_id = U.user_id
where M.reply_to_message_id = @ThreadRootMessageID
order by
  M.sent_datetime desc

如果你的线程是树形结构，而且想要能够快速轻松地运行此查询，那么上面的ERD模式并不容易使用。 SQL不擅长处理树形结构。 你可以通过一些去规范化的方法来解决这个问题。请参考下面的ERD：

请注意，现在有一个FK用于显示直接父级，另一个FK用于显示根。由于主题不受编辑的影响 - 至少在将消息的根更改为指向不同主题的编辑中，这种范式化并不意味着更新异常的风险，因此冗余不太有问题。

如果您使用此ERD，则“X线程中最新消息”的查询与上述相同，但将M.reply_to_message_id更改为M.thread_root_message_id放在where子句中。

- Joel Brown

---

消息 ID 1 没有回复到消息的 ID。消息 ID 2 回复了消息 1。消息 ID 3 回复了消息 2，以此类推。每个子消息都指向其父消息，从而定义了线程。线程中的第一条消息没有父消息，但是每个回复消息都有。发件人收件人接收到的内容要么是指向他们本身的消息（MESSAGE.sender_user_id），要么是通过 RECIPIENT 进行连接发送的。sender_keep/recipient_keep 是“标记为已读”的功能。消息之间的关系用于将它们在视觉上分类成线程。 - Joel Brown

在您的示例中，我该如何运行查询以仅选择每个线程的最后一条消息？ - bigmike7801

感谢 @JoelBrown - 关于这个问题有很多非常好的数据，这些数据拓宽了我的思路，让我更好地设计消息数据库。 - Walker Farrow

@chovy 线程根是用作线程上所有内容的任意分组属性，而无需沿着每条边一直遍历整个树。虽然您可以通过程序推导出它，但在某些情况下，将其作为“当您在附近时”的提示可能会很方便。 - Joel Brown

@user1107173，我回答中的模型支持一对一和一对多消息，其中定义了单个收件人。向群组发送消息有点棘手，因为一个群组是多个收件人的一个地址。我的答案中的模型可以通过以下几种方式来处理群组：1）您可以将群组替换为群组中的各个成员。这会隐藏使用群组的事实，因此可能不是理想的选择。2）您可以将群组和所有成员都放入收件人表中，并使用代码指示它是群组、群组成员还是单个收件人。 - Joel Brown