创建一个线程私人消息系统，如 facebook 和 gmail

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

这是我的表的设置方式:

用户:用户身份用户名线:线程ID标题to_id保持读书from_idfrom_keep日期信息:message_id线程IDto_idfrom_id消息文本日期

我现在正在做的是当用户创建一条新消息时，它在线程表中创建一个新线程，然后在消息表中创建一个新消息，如果用户响应一个线程，它会复制当前线程表中的线程，除了它交换 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.

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

任何帮助或想法将不胜感激.

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

解决方案

为什么不从用户对每条消息的视图中分离出消息关系?

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

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

假设(为简单起见)您要发布到板上，因此只有发件人"很重要，那么您就有了消息表，具有线程的自引用关系，用户表，然后是两者之间的交集表user 和 message 存储每个用户已阅读的消息.

这样，如果您想知道用户是否已阅读消息，只需尝试读取给定消息的交集表中的用户 ID.如果不存在，则该用户未阅读该消息.

请注意，如果您希望此设计拥有单个收件人，并且您希望拥有多个收件人，则可以使用交集表来保存每条消息的收件人列表.如果您确实有收件人交集表，它可以作为您的读取状态表执行双重任务.

ERD 草图:

这是我正在谈论的内容的简要概述...

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

编辑 2:替代 ERD 和查询最新消息

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

如果您要运行的典型查询是线程中的最新消息是什么?"并且您的线程是扁平的，那么您可以像这样使用 SQL:

选择前1M.message_id, M.sent_datetime, M.title, M.message_text, S.user_id, S.user_name——以及任何你想要的……来自 MESSAGE M 内连接 USER SM.sender_user_id = U.user_id其中 M.reply_to_message_id = @ThreadRootMessageID订购方式M.sent_datetime desc

另一方面，如果您的线程是树形的并且这是一个您希望能够快速轻松地运行的查询，那么上面的 ERD 中的架构就不太容易使用.SQL 不擅长树.你可以通过一些非规范化来解决这个问题.请参阅下面的 ERD:

请注意，现在有一个 FK 显示直接父项，一个 FK 显示根.由于线程不受编辑 - 至少在消息的根被更改为指向不同线程的编辑中，这带来的非规范化并不意味着更新异常的风险，因此冗余不会太成问题.

如果您使用此 ERD，则线程 X 中的最新消息"的查询与上述相同，但在 where 子句中使用 M.thread_root_message_id 而不是 M.reply_to_message_id.

I am creating a threaded message system much like gmail and facebook where the inbox lists the most recent threads displaying the subject, the sender's name, and time-stamp of the most recent message.

Here's How my tables are set up:

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

What I'm doing right now is when a user creates a new message, it creates a new thread in the thread table and then a new message in the message table and if a user responds to a thread, it duplicates the current thread in the thread table except it swaps the to_id and from_id and then creates a new message based on that.

Also, for the inbox view, I'm able to just query all threads based on a user_id. so something like SELECT * FROM thread WHERE to_id = 2 and to_keep = TRUE ORDER BY date DESC or if I want to view messages in the outbox it would be something like SELECT * FROM thread WHERE from_id = 2 and from_keep = TRUE ORDER BY date DESC.

If a user opens a thread when there's a new message, then to_read is updated to true UPDATE thread SET to_read = TRUE WHERE thread_id = 4.

I feel like I'm over complicating this process and that there should be a better way to do this.

Any help or ideas would be appreciated.

This way let's me just select everything from the thread table and then do a join with the user table to display everything I need. However I feel like there should be a better way to do this.

解决方案

Why don't you separate out message relationships from user's view of each message?

I would do the threading by a self-referencing relationship on message. In other words, the message has a "responding_to_message_id" column.

I'm not sure I understand why you have a "to_id". Are messages directed to individual users? This seems very limited. I would think that you would either have no recipient (i.e. the recipient is a message board that anyone can read) or you would have the ability to specify multiple recipients, just like with an e-mail. Perhaps you can explain more about how the system is to be used.

Assuming (for simplicity) that you are posting to a board, so only the "from" is important, then you have your message table, with self-referencing relationship for threading, a user table, and then an intersection table between user and message that stores which messages have been read by each user.

That way, if you want to know if a user has read a message or not, just attempt to read the user ID in the intersection table for the given message. If it isn't there, then that message is unread by that user.

Note that if you want to have single recipients this design holds and if you want to have multiple recipients you can use an intersection table to hold the list of recipients for each message. If you do have a recipient intersection table, it can do double-duty as your read status table.

EDIT: ERD Sketch:

Here is a quick sketch of what I'm talking about...

Whether or not the sender has chosen to keep the message is flagged on the message itself. If the message is the start of a new thread, the reply_to_message_id column is NULL, otherwise it is the message_id of the parent message. There can be mulitple recipients, each of which have their own ability to keep the message or not, as well as the ability to track the date and time when the recipient reads the message.

EDIT 2: Alternate ERD and Querying for Most Recent Message

@OP asked how to query for the most recent message in a thread. The answer depends on the form of the thread. You can either have a flat thread where every message goes to the end of the linear stream of messages or you can have a tree-shaped thread where each message has a specific parent, unless it's the root of the thread. In the ERD above, the reply_to_message_id field could be used either way. If the thread is flat, then the FK is always to the root MESSAGE. If the thread is tree-shaped, then the FK is to the immediate parent of the reply MESSAGE.

If a typical query you want to run is "what is the most recent message in a thread?" and your threads are flat, then you can use SQL like this:

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

If, on the other hand, your threads are tree-shaped and this is a query you want to be able to run quickly and easily, then the schema in the ERD above is not very easy to work with. SQL is not good at trees. You can solve the problem with a little bit of denormalization. See the ERD below:

Note that there is now one FK to show the immediate parent and one FK to show the root. Since threads aren't subject to editing - at least to edits where the root of a message is changed to point at a different thread, the denormalization that this entails does not imply risk of update anomallies so the redundancy is not too problematic.

If you use this ERD then the query for "most recent message in thread X" is the same as above, but with M.thread_root_message_id in the where clause instead of M.reply_to_message_id.