线程消息系统数据库模式设计 [英] thread messaging system database schema design

查看：121
本文介绍了线程消息系统数据库模式设计的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我想尝试达到以下解释：
创建一个线程私人消息系统像Facebook和Gmail ，
然而我不完全理解乔尔布朗的答案。可以任何一个请解释。



这是我的db表看起来像样本数据（我假设我为演示目的正确填写）：
< img src =https://i.stack.imgur.com/TSmlD.pngalt =enter image description here>


  1. 我需要显示基于LoginId的线程列表（最新的）LINQ中的查询会是什么样子？ （我要求的是一组消息线程，给我每个线程中的最新消息1） - 就像这是在Facebook上完成。


  2. 我需要在消息线程（LINQ）中显示所有消息 - >就像在Facebook上完成的那样，您可以点击消息，您将看到整个对话。


请帮忙！
感谢



EDIT - >延续
Joel，这是正确的吗





Joel，我有点困惑，你能请解释（评论/问题以粗体显示）：



这里的想法是，每当用户开始一个品牌新线程/消息，它从THREAD表中的新记录开始。然后将用户添加为THREAD_PARTICIPANT，并将消息的内容添加到指向包含THREAD的MESSAGE。



LoginId 1发送一条消息到LoginId2 =>新记录插入到MessageThread表中。此外，一条记录被插入MessageThreadParticipant记录，MessageThreadId = 1，LoginId = 1（发送者）。并将新记录插入到MessageId = 1，MessageThreadid = 1，SenderLoginId = 1（正确的）

的消息表中



这是我的之后的迭代：



我想我很困惑，因为没有办法让Loginid 2知道有一个消息给他。 ??或者我可能需要插入2条记录到MessageThreadParticipant？ （发件人和收件人） - >这种方式都可以看到整个对话



EDIT2：
，我想我可以这样做：

  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 
）
）
其中mtp.LoginId = 2 
 ORDER BY Message.CreateDate DESC;

如果我错了，请更正我）

解决方案

好吧，为什么不要问？ ：）



让我试着说明我对你的要求的理解。在我看来，你正在看一个线程是两个人之间的消息的线性列表（不是树）。我想，你可能想要允许更多的人，而不仅仅是两个。这将像Facebook一样，有人张贴的消息，然后任何数量的人可以阅读它，然后开始添加评论。当你添加一个评论，它使你进入线程，你开始获取状态更新和电子邮件告诉你在线程中的活动等等。假设这是你的后，然后我建议的模式

请考虑改用下列方式：





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



当用户读取消息时，他们在MESSAGE_READ_STATE表中获取一个条目，表明他们已经标记了



当有人评论线程中的初始消息时，第二个MESSAGE将添加一个FK返回到原始THREAD，并且将回复作者（用户）添加到THREAD_PARTICIPANT表。因此，当消息被一个，两个或更多的参与者添加到线程时。



要获取任何线程中的最新消息，从MESSAGE按照创建日期（或身份密钥）排序，其中消息FK是感兴趣的线程。



要获取用户的最近更新的线程，获取与前1个相关的THREAD，消息按创建日期排序，消息在用户是THREAD_PARTICIPANT的主题中。



恐怕我永远不会在LINQ中说明这些东西，而不打破LinqPad。如果你从上面捕获我的漂移，我可以通过表定义和一些SQL来解决答案。




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



为了清楚起见，我们在表中放几行。这里是场景，（以纪念加拿大日）：用户1 DMs用户2问关于会议啤酒。用户2回答关于在哪里会见和用户1回答的问题。表格如下：（可能过于简单）






编辑＃2：访问SQL以查看线程中所有消息的列表，读取状态...



@ OP的模式，这个SQL将获得给定线程中的消息列表，指示给定用户是否已读取每个消息。 

  SELECT 
 Message.MessageId 
，Message.CreateDate 
，Message.Body 
，Login.Username 
，（SELECT MessageReadState.ReadDate 
 FROM MessageReadState 
 WHERE MessageReadState.MessageId = Message.MessageId 
和MessageReadState.LoginId = 2）as ReadState 
 FROM（Message INNER JOIN Login ON Message.SenderLoginId = Login.LoginId）
 WHERE（（Message.MessageThreadId）= 10））
 ORDER BY Message.CreateDate DESC ;

请注意，如果是公平的话，那就是读取状态带有子选择。这是必要的，因为获取读取状态的标准的一部分需要一个外部联接不能满足的where子句。因此，您可以使用子选择来确定您希望从MessageReadState子表中删除哪个（可能缺少）值。



编辑3：



要获取给定用户参与的所有主题的列表，请按最近的消息首先，只显示最近的消息（每个线程1个消息），那么您将使用类似于上面的查询，除了不是通过其FK将消息过滤到感兴趣的线程，而是通过子查询过滤消息它在每个线程中找到感兴趣的用户参与的最新消息。它将如下所示：

  SELECT 
 Message.MessageId 
，Message.CreateDate 
，Message.Body 
，Login.Username 
，（SELECT MessageReadState.ReadDate 
 FROM MessageReadState 
 WHERE MessageReadState .MessageId = Message.MessageId 
和MessageReadState.LoginId = 2）AS ReadState 
从消息INNER JOIN登录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;


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.

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

  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.

  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.

Please help! thanks

EDIT -> continuation Joel, is this correct??

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

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.

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??)

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"??

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 :)

解决方案

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.

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.

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.

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.

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.

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.

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.

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.

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)

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

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.

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.

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

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:

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;

这篇关于线程消息系统数据库模式设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
C#/.NET最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆