When it comes time to pick the database you'd like to use for the back-end of your particular application you have two choices for the type you'd like to use. Relational, or SQL, databases and Document, or noSQL databases.
In general terms, a SQL database is any that uses Structured Query Language and a noSQL database is well...those that do not. So, lets get into what exactly that entails.
SQL/Relational
As mentioned above, SQL databases use Structured Query Language which was developed by IBM in the 1970's. SQL stores data in a schema that uses tables with columns and rows. In the most basic example, you can think of a SQL database like a phone book: each column would be the phone-number, name, and address while each row would be relation for each particular person. You can use one of of those three data-points to find the others, if you know a person's name you can find their address and number. (Especially if you can scan through the entire phonebook in a matter of seconds like a computer.)
 |
|---|
Each of these tables can also cross-reference other tables where those data-points might intersect. These relationship are either a one-to-many relationship (one users can have many blog posts), or a many-to-many relationship (each of those blog posts can have many comments). Below you can see an example of a schema for a chat application using dbdiagram.io.
 |
|---|
CREATE DATABASE chat;
USE chat;
CREATE TABLE `messages` (
`id` int PRIMARY KEY AUTO_INCREMENT,
username varchar(20),
`id_user` int,
`room_id` int,
`text` varchar(250),
roomname varchar(20)
);
CREATE TABLE `rooms` (
`id` int PRIMARY KEY AUTO_INCREMENT,
`name` varchar(20)
);
CREATE TABLE `users` (
`id` int PRIMARY KEY AUTO_INCREMENT,
`username` varchar(20)
);
Relational databases are very useful for situations where you might have a lot of data that intersects with each others, though it takes a fair bit of planning as each field must have it's data-type specified, and in the case of strings you also need to declare the maximum character limit.
Document/NoSQL
Document databases don't require the careful planning of a SQL database but are more limited in their use. They instead store data in collections consisting of documents (something like a JSON). You can picture a document database like a filing cabinet and the collections as folders. The folders/collections will tell you what type of document is inside them but you can't access the information inside those documents until you pull them out of the folder.
 |
|---|
This offers a lot more flexibility as you don't have to meticulously plan out your data into tables but this comes at the cost of speed and size. Since you're just storing full on objects you'll have to pull them out of the database and into your application to get any specif data instead of just querying what information you're looking for in particular like in a SQL database.
Which should you use?
That depends entirely on what kind of data you're working with and what you plan to do with it. If you're working with lots of unorganized data then a document database might suit you better, if your data is more structured and you application needs to access specific information and how it related to other data-points then a relational database is a better fit.
Latest comments (7)
Just to be clear, document-oriented databases are not the same as NoSQL databases. Document-oriented databases are a subtype of NoSQL databases. Other such subtypes are Graph databases, Key-value pairs, wide-column databases. All of them have their own specifications and unique things. The advantages of flexibility you mentioned are applicable to document-oriented databases and all NoSQL ones. So, it means that after choosing between SQL and NoSQL DBs, in case you decided NoSQL, you still have to select the subtype you want to use. Of course, document-oriented DBs are the most popular now, however, the others also have the use cases where they shine and outperform the document-oriented DBs.
For anyone wanting to combine the two approaches I strongly recommend Postgres. It has excellent support for JSON columns, which overcomes some of the listed limitations of NoSQL databases, but also allows you to store "unstructured" data.
I'll have to look into it for sure, I'm by no means an expert in this- 4 weeks ago I didn't even know what a SQL database was! I believe mySQL lets you store JSON now as well but I'm not sure how efficient it is at it.
And yeah, unstructured might not be the best word, maybe unorganized? That also seems a tad off though since JSONs are pretty structured/organized as well.
This is true:
And also this:
plus the cost of duplication of data.
Your article reminds me of Sarah Mei's Why You Should Never Use MongoDB
She explains with more detail why MongoDB(I take it for many NoSQL) is not ideal once your app starts having relationships on its data. Which probably will always happen in any application.
The use case for NoSQL storages is for unorganized data but I think that specific use case firs better for features of a webapp rather than an app itself.
I don't think my post can hold up against Mei's article but I'm flattered it reminded you of it!
Honestly, based on my limited experience with both databases, I'd use MongoDB all the time if I could. If only because it's so much easier to pick up and understand than having to query and join tables. Raw mySQL queries can get really lengthy.
Sadly, the more complex my apps get as I learn I'm going to have to bite the bullet and embrace relational databases sooner or later.
Nosql has transformed and disrupted how web pages are presented. It's more suitable for web apps and has been fully adopted by major IT players such as Google.
Sql with its stability and power, begins to run into challenges with super large databases.
Large queries don't just die, they just take 8 hours or more to return
data. Join operations can be highly expensive.
I worked for a major healthcare institution who suffered immensely from their massive SQL solution. A Google exec on their board, steered them to Nosql where they began implementing huge data migration projects.
Reason: Doctors and nurses wanted web apps for immediate patient feedback. Sql was too slow.
With cache you can make web apps respond faster, not only by using NoSQL solutions.