Database Design: private chat, group chat, and emails

Question

The communication between Facebook users seem to be stored in one long "conversation." So, emails sent and private chat messages exchanged all seem to be part of one long ongoing conversation.

I think this implementation works well for users (at least it does for me). I assume the table design for this part could be implemented this way:

TABLE: message
 - message_id
 - timestamp
 - from_user_id
 - to_user_id
 - message

What if I wanted to support group chat? Would I do something like this:

TABLE: message
 - message_id
 - timestamp
 - from_user_id
 - message

TABLE: message_recipient

 - message_recipient_id
 - message_id
 - to_user_id

I think it'll work. However, I'm wondering if it would make sense to the user if I displayed every single things that user has ever messaged anyone in one long conversation. It probably wont. Imagine a conversation with Person A mixed with group conversation with Person A, B, C, D mixed with conversation with Person E and so on ....

Any suggestion on what would be a usable concept to implement?

Answer 1

I believe a message should be an entity, regardless of platform or sender/receiver, with id,message,timestamp fields, and a message relation table - like you suggested - with id,message_id,from_id,to_id. Then, if you are showing a single user to user conversation, you can show every message between them. For group chats, you should have a table with id,title,timestamp that holds the group chat main record, and another table that holds the users that are part of that group chat, with id,group_chat_id,user_id fields.

Just my opinion and how I would implement it.

Edit: Maybe it would make sense to have from_id on the message entity itself, as a message has to have a singular sender id.