Hi! A few years ago I built the backend for a chat feature in a small application using a normal relational database(postgresql).
It was a small web app so we didn't worry about scalability nor concurrency nor any buzzword because there weren't too many users. We focused on making it work.
I built it in the Rails stack:
Initially, I designed the database like this:
Tables: Users, Messages
Relationships:
User has many Messages.Message belonged to a ReceiverMessage belonged to a SenderThis worked but had a major flaw. If either a receiver or a sender deleted a message, it was deleted for both. This wasn't desired as one cannot deleted messages sent from the other. They should only be able to delete their own messages.
So I complemented my DB design with this post
Then it was:
Tables: Users, Conversations, Messages
Relationships:
User has many Messages.User has many Conversations as ReceiverUser has many Conversations as SenderConversation has many Messages Message belonged to a ReceiverMessage belonged to a SenderNow both users have the same list of messages but they could delete messages in their conversation without affecting the other ones.