Question :
I am making an app that is similar to Chatroulette.
When a user starts the app it finds someone that is not currently talking to anyone and have them talk to each other. At any point a user could skip that person and get a new partner. I want to store the conversation history so every message will be saved.
Is this well suited for a relational database like PostgreSQL or should I be using a NoSQL system like MongoDB?
Right now I have it modeled as follows. Suggestions on the schema would be helpful as well!
`User`: has basic information
`Conversation`: `user1_id`, `user2_id`, `end_date`, `status` (ended or waiting or active)
`Message`: `message`, `conversation_id`, `sent`
So the user has many conversations and a conversation has many messages.
This is just in PostgreSQL. I just want to get some feedback before I model it in case something could be improved.
Answer :
Pretty much any database you choose will work for this.
You need to think carefully about concurrency issues no matter which DB you’re using, as you’ll have many concurrent workers inserting new records, sending messages and flagging them as sent, etc. You’re highly likely to need a queueing system like PgQ, because queueing is harder than you think and any implementation you come up with will be buggy.
Personally I’d want to think about having the chat system communicate within its self, not via the database, and just log messages to the database once they’re sent. That will be immensely more efficient than inserting them then updating them when they’re sent. The only real upside I see to keeping the state in the database is that your app its self can be pretty much stateless, making it easier to update and redeploy.