StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POthread messaging system database schema design
text
Body
copied!<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>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload