Document vs Relational Databases

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.