线程消息传递系统数据库模式设计

I'm trying to achieve exactly what's explained here: Creating a threaded private messaging system like facebook and gmail, however i don't completly understand Joel Brown's answer. can any one please explain.

我正在努力实现这里的解释:创建一个像facebook和gmail这样的线程化的私有消息系统，但是我并没有完全理解Joel Brown的答案。谁能解释一下。

This is what my db tables look like with sample data (I assume i filled it in correctly for demo purposes):

这就是我的db表的样例数据(我假设为了演示目的我正确地填写了它):

1. I need to display a list of threads based on LoginId (newest on top) what would the query look like in LINQ? (what i'm asking is in a a group of message threads, give me the 1 newest message in each thread) - just like this is done on facebook.

   我需要基于LoginId(最新的)显示一个线程列表，在LINQ中查询是什么样子的?(我要问的是在一组消息线程中，给我每个线程中的一个最新消息)——就像在facebook上做的那样。

2. I need to display ALL the messages in a message thread (LINQ) -> just like it's done on facebook where you click the message and you would see the whole "conversation" in a tread.

   我需要在一个消息线程(LINQ)中显示所有的消息——>，就像在facebook上做的那样，点击消息，你会看到整个“对话”。

Please help! thanks

请帮助!谢谢

EDIT -> continuation Joel, is this correct??

编辑->继续乔尔，对吗?

Joel, i'm a bit confused, can you please explain (comments/questions in bold):

Joel，我有点困惑，你能不能解释一下(评论/问题):

The idea here is that every time a user starts a brand new thread/message, it starts with a new record in the THREAD table. The user is then added as a THREAD_PARTICIPANT and the content of the message is added to MESSAGE which points back to the containing THREAD. The FK from MESSAGE to USER indicates the author of the message.

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

LoginId 1 sends a message to LoginId2 => new record is inserted to MessageThread table. Also a record is inserted to MessageThreadParticipant record with MessageThreadId = 1, LoginId = 1 (the sender). And a new record is inserted into Message table with MessageId =1, MessageThreadid =1, SenderLoginId = 1 (correct??)

LoginId 1向LoginId2 =>发送消息，新记录被插入MessageThread表。还将记录插入MessageThreadParticipant记录，其中MessageThreadId = 1, LoginId = 1(发送方)。并将新记录插入MessageId =1、MessageThreadid =1、SenderLoginId =1(正确??)

this is what i have after that iteration:

这是我在那个迭代之后得到的:

I think i'm confused because there is no way for Loginid 2 to know that there is a message for him. ?? OR maybe I need to insert 2 records into MessageThreadParticipant?? (the sender and the receiver)-> this way both can see the whole "conversation"??

我觉得我很困惑，因为Loginid 2没有办法知道他有消息。? ?或者我需要向MessageThreadParticipant插入两条记录?(发件人和收件人)->这样都能看到整个“对话”?

EDIT2: Joe, I think I could do this:

乔，我想我能做到:

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;

Please correct me if i'm wrong :)

如果我错了请纠正我

2 个解决方案

#1

Well why don't you just ask? :)

你为什么不问问呢?:)

Let me try to pin down my understanding of your requirement. It seems to me that you are looking at a thread being a linear list (not a tree) of messages between two people. I would think that you might want to allow more people in than just two. That would be like Facebook insofar as someone posts a message and then any number of people can read it and then start adding comments. When you add a comment it puts you into the thread and you start getting status updates and e-mails telling you about activity in the thread and so forth. Assuming that is what you're after, then the schema I suggested to Big Mike is not exactly what you're looking for.

让我努力把我对你的要求的理解说清楚。在我看来，线程是两个人之间的线性列表(而不是树)。我认为你可能想让更多的人进来而不仅仅是两个。这就像Facebook，只要有人发布了一条信息，然后所有人都可以阅读，然后开始添加评论。当你添加一个评论时，它会把你放到线程中，你会收到状态更新和邮件，告诉你线程中的活动等等。假设这是你想要的，那么我给大麦克建议的模式并不是你想要的。

Consider instead the following:

考虑以下:

The idea here is that every time a user starts a brand new thread/message, it starts with a new record in the THREAD table. The user is then added as a THREAD_PARTICIPANT and the content of the message is added to MESSAGE which points back to the containing THREAD. The FK from MESSAGE to USER indicates the author of the message.

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

When a user reads a message, they get an entry in the MESSAGE_READ_STATE table to indicate that they have marked the message read, either explicitly or implicitly, depending on how your requirements go.

当用户读取一条消息时，他们会在MESSAGE_READ_STATE表中获得一个条目，以表明他们已经标记了所读取的消息，无论显式还是隐式，这取决于您的需求。

When someone comments on the initial message in the thread, a second MESSAGE is added with an FK back to the original THREAD and the reply author (user) gets added to the THREAD_PARTICIPANT table. And so it goes as messages are added to the thread by one, two or even more participants.

当有人对线程中的初始消息进行注释时，第二个消息将与FK一起添加到原始线程，并将应答作者(用户)添加到THREAD_PARTICIPANT表。当消息被一个、两个甚至更多的参与者添加到线程中时，它就这样进行了。

To get the most recent message in any thread, just take the top 1 from MESSAGE sorted descending on create date (or an identity key) where the message FK is to the thread of interest.

要在任何线程中获取最新的消息，只需从按创建日期(或标识键)降序排序的消息中选取顶部1，其中消息FK位于感兴趣的线程。

To get the most recently updated thread for a user, get the THREAD related to the top 1 from message sorted descending on create date where the message is in a thread in which the user is a THREAD_PARTICIPANT.

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

I'm afraid I can never state these things in LINQ without breaking out LinqPad. If you are having trouble catching my drift from the above, I could flesh out the answer with table definitions and some SQL. Just ask in the comments.

恐怕在LinqPad中，我永远都不会说出这些事情。如果您无法理解上面的意思，我可以使用表定义和一些SQL充实答案。你可以在评论中提问。

EDIT: Clarification of Requirements and Implementation

编辑:澄清需求和实现

Clarifying the requirements: Initially I was thinking about publicly posted messages with the opportunity for commenting, whereas Shane is after more of the direct message feature. In which case the initial recipient needs to be included in the THREAD_PARTICIPANT table at the outset.

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

For some clarity, let's put a few rows in tables. Here is the scenario, (in honour of Canada Day): User 1 DMs User 2 to ask about meeting for a beer. User 2 replies with a question about where to meet and User 1 answers. The tables would look something like this: (probably oversimplified)

为了清晰起见，让我们在表中放置一些行。这里是场景，(为了纪念加拿大日):用户1 DMs用户2询问关于见面喝啤酒的事情。用户2回答关于在哪里见面的问题，用户1回答。表看起来是这样的:(可能过于简化了)

EDIT #2: Access SQL for list of all messages in a thread, with read state...

编辑#2:访问一个线程中所有消息列表的SQL，具有读取状态……

Using @OP's schema, this SQL will get a list of messages in a given thread with an indication of whether a given user has read each message or not. Messages are in most recent first order.

使用@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;

Note that the trick, if it's fair to call it that, is that the read state is picked up with a sub-select. This is necessary because part of the criteria for getting the read state requires a where clause that can't be satisfied with an outer join. Therefore you use the subselect to pin down which (possibly missing) value you want from the MessageReadState child table.

注意，如果这个技巧是公平的，那就是读取状态是通过子选择来获取的。这是必要的，因为获取读取状态的部分标准要求在where子句中不能满足外部连接。因此，您可以使用subselect来确定您想要从MessageReadState子表中获取哪个值(可能缺失)。

EDIT 3: SQL for getting all threads with latest message in each for a given user...

编辑3:SQL为每个用户获取每个线程的最新消息…

To get a list of all of the threads in which a given user has participated, sorted by most recent message first, with only the most recent message being displayed (1 message per thread) then you would use a similar query to the one above, except instead of filtering messages by their FK to the thread of interest, you filter the messages by a subquery that finds the latest message in each thread that the user of interest participated in. It would look like this:

让所有的线程的列表一个给定的用户参与,按最新消息排序第一,仅显示最近的消息(每个线程1消息),那么你将使用一个类似的查询上面,而不是过滤信息的除外颗感兴趣的线程,你过滤信息的子查询找到的最新消息在每个线程,用户参与的兴趣。它看起来是这样的:

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;

#2

According to Joel Brown'answer, you can add LAST_MESSAGE_ID column into THREAD table then getting all threads with last messages SQL is become very simple. You must update this column when every message send.

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

Getting all threads with latest message in each for a given user

为给定的用户获取每个线程的最新消息

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