-2
Conversations
--------------
id


Messages
--------------
id
conversation_id
user_id
reply_to_id
content
created_at

I was looking at this question and I had a question: How to build a table for a private messaging system that supports replies?

Let's say you have a chat and two users messaging each other back and forth, and then another person joins, so an event says Third User has joined the chat.

How would you represent the event in a schema? It's technically just another message without a user_id, but allowing user_id to be null seems to violate data integrity. Is it better to just create a separate table? How would you design that table to allow easy joining so that each even is in the proper time?

1
  • I got two ideas. Either have a junction table for the participants of a conversation with a timestamp when a user joined or left, or maybe see joining or leaving a special type of message and integrate it in the messages table.
    – sticky bit
    Jun 11, 2018 at 11:08

2 Answers 2

2

If you don't like user_id being NULL create a new one, call it "system_event" and use it for these messages.

1
  • This is my preferred approach too. Jun 11, 2018 at 18:30
0

MUC and Pubsub provide end-users to create N number of chat groups and channels where the group restriction is not a complex one.

It has some default function to send an acknowledgment to the server. Just retrieve the function & reply to the conversation_id

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.