创建一个线程化的私人消息系统，如Facebook和Gmail [英] Creating a threaded private messaging system like facebook and gmail

问题描述

我正在创建一个线程化的邮件系统，很像gmail和Facebook，其中收件箱列出了显示主题的最新线程，发件人的姓名和最近邮件的时间戳。

以下是我的表格设置方式：

  users：
 user_id 
 user_name 
 
线程：
 thread_id 
标题
 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和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线程SET to_read = TRUE WHERE thread_id = 4 。

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

任何帮助或想法都不胜感激。

这样让我只需从线程表和然后与用户表进行加入，以显示我需要的所有内容。但是我觉得应该有一个更好的方式来做到这一点。

解决方案

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

我将通过消息的自引用关系来执行线程。换句话说，消息具有respond_to_message_id列。

我不知道我明白为什么你有一个to_id。消息是针对个人用户吗？这似乎非常有限。我会认为你没有收件人（即收件人是任何人都可以阅读的留言板），或者您可以使用电子邮件来指定多个收件人。也许你可以解释一下如何使用系统。

假设（为了简单起见）你发布到董事会，所以只有从是重要的，那么你有你的消息表，用于线程的自参考关系，用户表，然后用户和消息之间的交集表，用于存储每个用户读取哪些消息。

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

请注意，如果您希望拥有此设计的单个收件人如果您想拥有多个收件人，您可以使用交叉表来保存每个邮件的收件人列表。如果你有一个收件人交叉表，它可以做你的阅读状态表的双重职位。

编辑：ERD素描： p>

以下是我正在谈论的快速草图...

发件人是否选择保留邮件被标记消息本身。如果消息是新线程的开头，则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 
  - 还有其他任何你想要的。 .. 
来自MESSAGE M inner join USER S 
 on M.sender_user_id = U.user_id 
其中M.reply_to_message_id = @ThreadRootMessageID 
 order by 
 M.sent_datetime desc 
  

另一方面，如果你的线程是树状的，这是一个你想要的查询能够快速，轻松地运行，那么上面的ERD中的模式不是很容易使用。 SQL不善于树。你可以用一点点的非规范化来解决这个问题。请参阅下面的ERD：

请注意，现在有一个FK显示直接父项和一个FK来显示根。由于线程不受编辑 - 至少编辑消息的根被改变为指向不同的线程时，这导致的非规范化并不意味着更新异常的风险，因此冗余不是太麻烦。 p>

如果您使用此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.

这篇关于创建一个线程化的私人消息系统，如Facebook和Gmail的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！