title: thread messaging system database schema design
body: <p>I'm trying to achieve exactly what's explained here: 
  <a href="https://stackoverflow.com/questions/6420264/creating-a-threaded-private-messaging-system-like-facebook-and-gmail">Creating a threaded private messaging system like facebook and gmail</a>, 
  however i don't completly understand Joel Brown's answer. can any one please explain.</p>
  
  <p>This is what my db tables look like with sample data (I assume i filled it in correctly for demo purposes):
  <img src="https://i.stack.imgur.com/TSmlD.png" alt="enter image description here"></p>
  
  <ol>
  <li><p>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.</p></li>
  <li><p>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.</p></li>
  </ol>
  
  <p>Please help!
  thanks</p>
  
  <p><strong>EDIT</strong> -> continuation
  Joel, is this correct??</p>
  
  <p><img src="https://i.stack.imgur.com/rxWe8.png" alt="enter image description here"></p>
  
  <p>Joel, i'm a bit confused, can you please explain (comments/questions in bold):</p>
  
  <p>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.</p>
  
  <p><strong>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??)</strong></p>
  
  <p>this is what i have after that iteration:
  <img src="https://i.stack.imgur.com/F0Ml7.png" alt="enter image description here"></p>
  
  <p>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"??</p>
  
  <p><strong>EDIT2:</strong>
  Joe, I think I could do this:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Please correct me if i'm wrong :)</p>