Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote -1 down vote favorite

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.

share|improve this question
I down-voted this because the question shows a lack of research into the question of SQL vs NoSQL, and is also asking for broad advice on database structure, without showing research completed. – Max Vernon Sep 5 '12 at 14:48

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
+1 this is excellent advice all round – Jack Douglas♦ Aug 27 '12 at 9:34
Thanks. I will look into those! One thing im sort of confused about is the user will make a request to start talking to a random person. Would this be good to do in the database? Then when a new user comes I would just search for all conversations that are waiting for another user to join? – Jonovono Aug 27 '12 at 16:06
@Jonovono Hard to say. Relational databases suck at randomness; queries for a random row across any significant sized structure can be inefficient. OTOH, if it saves you maintaining a potentially big data strucuture in memory it could be a real win. You usually land up doing these sorts of things one way - and being prepared to change how you do it if it turns out to perform poorly, or scales poorly in production. – Craig Ringer Aug 28 '12 at 0:02
Cool, thanks a lot! I guess the best way to pick a random user in this case could just be the one that has been waiting the longest to talk to someone. – Jonovono Aug 28 '12 at 4:55
@Jonovono ... in which case you'd be fine doing that with the DB, though it's a variant of the queueing problem where you'll get some unexpected results in concurrent access because many transactions may try to grab the same "oldest" row. Careful coding and a retry loop or a proper queuing system is required. – Craig Ringer Aug 28 '12 at 5:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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