SQL versus NoSQL

While SQL and NOSQL databases have coexisted since the beginnings of information technology, the latter did not attract much interest until the early 21st century. The main advantages and disadvantages of each one are given below:

Advantages of NoSQL over SQL

NoSQL databases have two main strengths:

  1. Flexibility. The NoSQL model of storage is based on “documents” (e.g. key-value pairs) whereas in SQL it is based on rows (e.g. tuples of values). Thus, in NoSQL, any kind of data can be stored without having to define it previously.

    For example, to store a user ID number and name in a SQL database, we must specify that the table Users has a numerical field id and a text field name. In contrast, in a NoSQL database we can directly store the following key-value pair: { id: 1, name: "John Doe" }. If we later wish to add a third field, e.g. age, we simply store { id: 1, name: "John Doe", age: 22 }. To do this in a SQL database we need to alter the table design beforehand, i.e. to change the definition of the table to add the third field age (and we have to specify whether it is a numerical or other type of field).

  2. Scalability. To improve performance, NoSQL databases can easily be distributed among several servers, as each “document” is a unit of information isolated from the rest. Therefore, in NoSQL it suffices to indicate which servers will form part of the database.

    In contrast, SQL databases were designed to function in a single machine, so the only “native” way of scaling is by adding more hardware (more memory, more processor and more hard drive).

Advantages of SQL over NoSQL

However, SQL databases are still unbeatable in two aspects:

  1. Normalisation. In SQL, redundancy in the data is eliminated by normalisation, which is simply the way of structuring the tables and their relationships. This allows much better storage than in NoSQL databases.Additionally, normalisation ensures the dependencies and restrictions between the data, and therefore SQL databases are stricter, as one way of putting it, than NoSQL databases.
  2. Reliability. Most SQL databases use the ACID model of consistency.
  • Atomicity. When an operation consists of a series of steps, either they are all executed or it returns to the state the data was in before the operation.
  • Consistency. It ensures that the data are always as expected and do not change their type.
  • Isolation. Different operations with the same information are always independent and do not generate any kind of error.
  • Durability. Once the operation has been executed, it will persist and cannot be undone even if the system crashes (e.g. a power failure).

Instead, NoSQL databases usually respond to needs covered by the BASE model:

  • Basically Available: the availability of the data is ensured by replicating the database in several servers.
  • Soft-state. It is assumed that the consistency of the data is provided by the developer, not the database. As a result, the type of data can change over time and even between different replicas of the same database.
  • Eventual consistency. It is ensured that the data will be consistent in the future, but when exactly that will occur is not guaranteed.
Library
Library. Source: Pixabay

Conclusion

To sum up: which type of database is better? Which of the two should I use in my company? The answer is: it depends. In Big Data projects, a NoSQL database will probably be best, as the data that are usually analysed are not very structured; for example, server logs, interaction analyses, a linguistic corpus, etc. On the other hand, SQL is ideal in projects in which we have to analyse relationships between our data. For example, purchases made by our users, messages in discussion groups, invoices issued, etc.

Leave a Reply