php - 创建一个像 facebook 和 gmail 这样的线程私有(private)消息系统

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

我现在正在做的是，当用户创建一条新消息时，它会在线程表中创建一个新线程，然后在消息表中创建一条新消息，如果用户响应一个线程，它会复制当前线程thread 表中的线程，除了它交换 to_id 和 from_id 然后基于它创建一个新消息。

此外，对于收件箱 View ，我可以根据 user_id 查询所有线程。所以像 SELECT * FROM thread WHERE to_id = 2 and to_keep = TRUE ORDER BY date DESC 或者如果我想查看发件箱中的消息，它会像 SELECT * FROM thread WHERE from_id = 2 和 from_keep = TRUE ORDER BY date DESC。

如果用户在有新消息时打开线程，则 to_read 将更新为 true UPDATE thread SET to_read = TRUE WHERE thread_id = 4。

我觉得我把这个过程复杂化了，应该有更好的方法来做到这一点。

如有任何帮助或想法，我们将不胜感激。

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

Answer 1

为什么不从用户对每条消息的看法中分离出消息关系？

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

我不确定我是否理解您为什么有一个“to_id”。消息是否针对个人用户？这似乎非常有限。我认为您要么没有收件人(即收件人是任何人都可以阅读的留言板)，要么您可以指定多个收件人，就像使用电子邮件一样。或许您可以解释更多关于如何使用该系统的信息。

假设(为简单起见)您要发布到版 block ，因此只有“发件人”很重要，那么您有消息表，具有线程的自引用关系，用户表，然后是交集表user 和 message 存储每个用户已阅读的消息。

这样，如果您想知道用户是否已阅读消息，只需尝试读取给定消息的交集表中的用户 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 中的最新消息”的查询与上面相同，但在 where 子句中使用 M.thread_root_message_id 而不是 M.reply_to_message_id。