DB schema for user conversations

Question

I've been thinking about how to build a db schema for user conversations within a website I'm building up.

By "conversations" I mean a similar system to a plain-old private messaging one, but similar to Facebook's offline chat.

So far I have this :

id
from_id
to_id
content
created_on

It works, but I wasn't able to find a way to get all the last exchanged messages of each conversation into a list, which made me think that maybe this db schema isn't the way to go.

Assuming there's a users table with a primary numeric id, what would be the best db schema to handle user conversations?

Cheers!

What was the problem with getting all messages from a conversation (user to user) with this schema? What did you try? — Marcus Adams, Jun 22 '12 at 19:55
My question there is how do I group the results to show the list with only the last message exchanged between each user. — yoda, Jun 22 '12 at 20:29

score 0 · Answer 1 · answered Jun 22 '12 at 19:47

I'll do it kind of this way:

enter image description here

Table User have the User Id + other info that you have.
Relational table to store user speak with user, but this is a 1 chat with 1 user, if you want N user speak with M users at the same time it should be different design.

for Now I think this is pretty ok, because you can save who send the message, to who, what's the message, the TIME, and Status (read, delete, unread, hidden, etc).

I'm pretty sure there is more than one way to do it.

Marcus Adams · Answer 2 · 2012-06-25T14:00:51.833

I don't think you want a from_id and to_id, just a from_id. You'll determine the first and last message in the conversation by the date.

However, for simplicity and performance sake, I'd make a junction table that's user centric:

user_id  with_user_id conversation_id  last_read_id
-------  ------------ ---------------  --------------
1        2            1                1
1        3            2                0
2        1            1                2
3        1            2                1

Then, for the conversations:

id  conversation_id  from_id  content  created_on
--  ---------------  -------  -------  ----------
1   1                1        hello    2012-06-01 12:00:00 
2   1                2        hi       2012-06-01 12:10:01
3   2                3        howdy    2012-06-01 12:40:10

So, when someone wants to begin a conversation, you check the junction table to see if they're already in a conversation with that person. If they are, resume it.

If it's a new conversation, then make sure to add the record in the junction table for each user. The last_read_id column in the junction table refers to id in the conversations table.

To get all the unread messages for user 1:

SELECT c.* FROM junction j
JOIN conversation c
ON c.conversation_id = j.conversation_id
AND c.id > j.last_read_id
WHERE j.user_id = 1
ORDER BY c.created_on DESC

Results in:

id  conversation_id  from_id  content  created_on
--  ---------------  -------  -------  ----------
3   2                3        howdy    2012-06-01 12:40:10
2   1                2        hi       2012-06-01 12:10:01

To get all messages between user 1 and user 2 (from user 1's point of view):

SELECT c.* FROM junction j
JOIN conversation c
ON c.conversation_id = j.conversation_id
WHERE j.user_id = 1 AND j.with_user_id = 2
ORDER BY c.created_on DESC

Results in:

id  conversation_id  from_id  content  created_on
--  ---------------  -------  -------  ----------
2   1                2        hi       2012-06-01 12:10:01
1   1                1        hello    2012-06-01 12:00:00

DB schema for user conversations

2 Answers2

Linked