So here is the situation, I'm working on a chat application (internal chat application) with abilities to handle too many messages and maybe scale it further. What I'm working on is User with multiple private channels (chat rooms one to one), and create groups as well. but database selection part is taking away a lot of time which I should invest in developing the app instead of wasting it. Thats why i'm here to ask professionals what you guys really think about selection of right database for this particular type of web app / app
tech stack: Angular (front) ... Node.js (backend), Socket.io , DATABASE(pending)
So my questions is Which one you would choose and why you would choose it? Only if possible for you to take out a little time. Can you provide a little example of written schema of Relation as such:
User Channels (chat rooms) , connected to above user Document / Table Messages (messages connected to chat room along with sender reference), connected to user and channel document / table
For Mysql I'm clear, I've used MongoDB but I'm a little confused regarding it. Would be awesome to have my mind cleared with your learnings and help me out to choose the best.
Let me know if you need further clarifications :)
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.Messagebelonged to aReceiverMessagebelonged to aSenderThis worked but had a major flaw. If either a
receiveror asenderdeleted 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 ConversationsasReceiverUser has many ConversationsasSenderConversation has many MessagesMessagebelonged to aReceiverMessagebelonged to aSenderNow both users have the same list of messages but they could delete messages in their conversation without affecting the other ones.