SQL or NoSQL Database for a chat application?

Question

currently I am working on a kind of "Chat" Application.

The app consists of "threads". Each of this threads consist of "subthreads" in wich a user can send a message (like a group). But there also should be an option to send private messages.

Firstly I have to say that I have basically no knowlege about NoSQL-Databases. I only know some of it ́s dis- or advantages. I also noticed that NoSQL is not NoSQL. In my case I mean a json (or similar) based NoSQL-Database.

So now my question is if I should use a SQL(RDBMS) or NoSQL Design. I saw a lot of "tutorials" using NoSQL-Databases.

In my understanding a SQL Datatbase would be more practicable and efficient, because there would be a lot of Selecting and joins depending on ids, etc... With a a NoSQL you would end in a giant Json for every entry that has to be read every time.

My SQL Scheme should be something like this

User:

id
username
firstname
lastname
....
password

Token:

....

Categories

id
parent
child
title
description

Messages

id
sendin
author
readby <- this would be a json/array. Not optimal for SQL.
senddate
text
attachmentids <- this would be a json/array. Not optimal for SQL.

• This is not the finished naming or rows, but just an example how it could look like. Nevertheless I would be pleased about improvement suggestions

If you recommend a NoSQL Database, can you please provide an example on how I could efficiently save this data?

Thank you guys

Edit: I plan to fetch the messages quite regulary from the client. But because it should work offline, there will be also an storage of messages to the client locally (therefor I probably need to work with JSON). But only after the data was correctly stored on the server. So the client only has the data, the server has, locally.

• Rik D: Can you expand a little about how your application will work? I assume it’s an asynchronous protocol where user A sends a message to 1 or more other users? Do you want to keep messages stored after they have been delivered/read?
• Vito: Updated second question. An yes. It should be an asynchronous protocol, where users can send independent of each other. But how I will manage this, I can't say exactly at the moment, because I want to build a database structure first. (The previous times I built the database around the app. Most of the time this led to problems when I thought about some functions too late).
• Ewan: if you want to scale it has to be NoSql
• jwenting: @Ewan decades of scalable SQL databases tend to disagree
• Ewan: @jwenting explain?

Answer 1

It's not possible to lump all NoSQL databases into one category. For example, your application might be an acceptable fit for graph databases, but a poor fit for document databases (such as MongoDB).

I think you're on the right track with your table design.

The essential observation is that your proposed datamodel features relations, for example message–author, message–readby, message–attachment. This seems to be a very good fit for a relational DB, if you model those relations not as JSON arrays but as separate tables. For example, you might have another table

CREATE TABLE readby (
 message_id REFERENCES messages(id),
 user_id REFERENCES user(id),
 UNIQUE (message_id, user_id)
);

That said, that a DB is relational doesn't imply that it can't use JSON data. At this point, all major relational DBs have rudimentary JSON support, though there are significant differences in how you can use JSON columns in queries, constraints, and indexes.

You could just as well model your database with a document database, where you have separate collections like "users" and "messages". The drawback is that it becomes more difficult to ensure referential integrity. In particular, MongoDB is known for having surprising default settings for transaction isolation levels, and has more limited support for references across collections.