线程短信系统数据库架构设计 [英] thread messaging system database schema design

问题描述

我想达到什么是在这里解释说：
<一href=\"http://stackoverflow.com/questions/6420264/creating-a-threaded-private-messaging-system-like-facebook-and-gmail\">Creating螺纹私人的邮件系统，如Facebook和Gmail ，
但我不明白完全地乔尔·布朗的回答。任何一个可以请解释。

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. 我要显示基于登录ID线程的列表（最新的在上面）将查询看起来像在LINQ？ （什么我问的是在一组消息的线程，给我在每个线程1最新消息） - 就像这是在Facebook上做

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.

我需要显示在邮件主题中的所有消息（LINQ） - >就像它在Facebook上完成的，你点击该邮件，你会看到在胎面整个对话

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

这里的想法是，每一个用户开始一个全新的线程/消息时，它与在螺纹表的新记录开始。然后用户被添加作为THREAD_PARTICIPANT和该消息的内容被添加到消息指向回含螺纹。从消息到用户FK表示消息的作者。

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.

<强>登录ID 1发送一个消息到LoginId2 =>新记录被插入到MessageThread表。又一个记录插入到MessageThreadParticipant与MessageThreadId = 1，登录ID = 1（发件人）记录。和一个新的记录被插入到信息表的MessageId = 1，MessageThreadid = 1，SenderLoginId = 1（正确？）

这就是我的迭代后有：

this is what i have after that iteration:

我觉得我很困惑，因为没有办法为登录ID 2要知道，有他的消息。 ??也许我需要插入2条记录到MessageThreadParticipant？ （在发送者和接收者） - >这种方式既可以看到整个对话??

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

让我尽量拖住我的客户要求的理解。在我看来，你正在寻找一个线程中的消息两个人之间的线性表（不是树）。我倒觉得，你可能想，让更多的人比只有两个。这将是像Facebook只要有人发布一条消息，然后任意数量的人可以阅读它，然后开始添加注释。当您添加评论它使你进入线程，你开始的状态更新和电子邮件，告诉你有关在线程活动等等。假设是你以后，然后将模式我建议<一href=\"http://stackoverflow.com/questions/6420264/creating-a-threaded-private-messaging-system-like-facebook-and-gmail\">Big迈克是不是你要找的是什么。

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.

而不是考虑以下几点：

这里的想法是，每一个用户开始一个全新的线程/消息时，它与在螺纹表的新记录开始。然后用户被添加作为THREAD_PARTICIPANT和该消息的内容被添加到消息指向回含螺纹。从消息到用户FK表示消息的作者。

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.

当用户阅读邮件时，他们得到的MESSAGE_READ_STATE表中的条目，以表明他们有显着的密信，或隐或显，根据您的要求怎么走。

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.

当有人在线程初始消息评论，一个第二消息是与FK加回到原始线程和答复作者（用户）被添加到THREAD_PARTICIPANT表。如此这般作为消息由一个，两个或甚至更多的参与者加入到螺纹

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.

要在任何线程获得最新消息，只取消息前1排序降序上创建日期（或身份密钥），其中消息FK是感兴趣的主题。

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从消息的线程排序降序上创建日期，其中的信息是在哪个用户是THREAD_PARTICIPANT一个线程。

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.

我怕我不能说出在LINQ这些东西没有打出来LinqPad。如果您无法从以上懂了，我就可以充实与表定义和一些SQL答案。只要问问中的注释。

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.

编辑：需求和实施的澄清

明确要求：一开始我在想公开发布的消息与评论的机会，而巴蒂尔是经过直接留言功能。在这种情况下，最初的接收方需要被包括在一开始就THREAD_PARTICIPANT表

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.

对于一些清晰，让我们把几排在表中。下面是这种情况，（在加拿大国庆日荣誉）：用户1 DMS用户2询问关于啤酒的会议。用户2用在哪里，以满足和用户1回答问题的回答。该表将是这个样子：（可能过于简单）

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)

编辑＃2：访问SQL对于一个线程的所有信息列表中，具有读状态...

，此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.

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;

注意的伎俩，如果它是公平地称呼它，是一个在读状态回升与子选择。这是必要的，因为用于获取读取状态标准的一部分，需要一个where子句不能满足于外连接。因此，在使用子选择从MessageReadState子表拖住你想要的（可能丢失）值。

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.

修改3：SQL获取最新的具有消息的所有线程在每个给定的用户...

要获得所有在给定的用户参与，排序由最新的消息首先，与正在显示（每线程1消息）只有最新的消息线程的列表，你可以使用一个类似的查询到1以上，除了没有过滤其FK的消息感兴趣的主题，您可以通过在每个线程感兴趣的用户参与发现的最新消息，子查询筛选消息它是这样的：

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屋！