-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?

Strawberry
  • 113
  • 2

2 Answers2

2

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

Michael Green
  • 25,255
  • 13
  • 54
  • 100
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

David Michael
  • 9
  • 1