Data is the backbone of all subfields of data science. No matter the size or application of the project you’re building, you’ll need to obtain and analyze data. Most often, the data you need is stored in a DBMS (database management system).
To interact and communicate with the DBMS, you need to use its language or a language that it understands. The language used to interact with DBMSs is SQL (Structured Query Language).
In recent years, another term surfaced in the field of databases: NoSQL databases. If you’re just starting with data science, you might have heard of both SQL and NoSQL databases.
But, what is the difference and how do you choose which one to use?
Let’s start from the absolute beginning. Why do we have two approaches to interact with databases?
Simple: SQL and NoSQL interact with different types of databases. SQL is the approach used to interact with relational databases, while NoSQL is used to interact with non-relational databases.
SQL vs. NoSQL: What’s the Difference?
Relational Databases
In relational databases, data is stored in different tables, each containing multiple records (rows). These tables are connected to each other using one or more relations.
Keys define the relation between the tables. A key is the table field (column) that contains unique values for each record. If a field is defined as a table’s primary key, this field can be included in multiple tables and can be used to access different tables simultaneously. Once a primary key connects one table to another, it will become known in the other table as a foreign key.
For example, the image below shows part of an airline's database. We have two tables: pilots table and flights table. These two tables are connected and used as the primary key in the pilots’ table “PilotId.” I can use the pilots’ ID to access information about the pilot for the pilots’ table and the flights this pilot is handling from the flights table.
So, in the pilots’ table, PilotId is the primary key, while it is a foreign key in the flights table. PilotId is used here to form a relation between the two tables.
Non-Relational Databases
Unlike relational databases, non-relational database — NoSQL databases — don’t store data in tables and records. Instead, in these types of databases, the data storage structure is designed and optimized for specific requirements.
Instead of SQL, which is what relational databases use, NoSQL databases use object-relational-mapping (ORM) to facilitate communication with its data.
The four popular types of NoSQL databases are: column-oriented, document-oriented, key-value pairs and graph databases. You can use these types individually or you can combine them. The choice will depend on your application and the type of data you need to store.
Column-Oriented Databases
As the name suggests, in a column-oriented database, the data is stored and organized as columns. This type of database is beneficial if you need to store sparse data.
Document-Oriented Database
In a document-oriented database, the structure of the data sorted isn’t so rigid. Instead, the data is stored as a collection of fields and data values together in the structure of a document. These document-oriented databases are often stored as JSON strings and can be encoded using YAML, XML or as plain text to protect the data.
A benefit of using a document-oriented database is that your documents don’t all need to have the same structure. This means the developer has the freedom to sort different data types within the same database. In Python, MongoDB is an example of document-oriented databases.
Key-Value Databases
If you’re familiar with Python, you can think of a key-value database as a Python dictionary. Every data object in the database will be stored as a pair: a key and a value.
Graph Databases
When your data is highly interconnected, you will need to use a database that allows you to implement the interconnection of the data efficiently. Graph databases are considered the most complex NoSQL database. However, graph databases are versatile, which make them a great fit for many applications.
How to Choose Between SQL and NoSQL
So, how can we choose between SQL and NoSQL databases?
There are four aspects you need to consider in order to answer this question: flexibility, scalability, consistency and available technology.
1. Flexibility
Sometimes your data has different structures and different types. By definition, NoSQL databases give you more freedom to design your schema and store different data structures within the same database. SQL databases, on the other hand, have a more rigid structure and schema.
2. Scalability
Have you ever seen a Japanese car parking elevator? It allows you to park cars on top of each other. Now, ask yourself this: Which will be more efficient, adding levels to the elevator or building new elevators? SQL databases are vertically scalable, which means you can add levels to it (increase the load) while NoSQL databases are horizontally scalable, meaning you can increase its load by dividing the work over multiple servers.
3. Consistency
SQL databases have a highly consistent design. However, a NoSQL database can be consistent (or not) based on DBMS. For example, MongoDB is consistent, while a database like Cassandra is not.
4. Available Technology
One aspect you might consider is the current stage of development of the database technology. Since SQL databases have been around for a longer time, they’re more developed than NoSQL databases. So, for beginners, starting with SQL and then moving to NoSQL might be the best choice.
As a rule of thumb, SQL is a better choice if you’re dealing with an RDBMS (relational database management system) and want to analyze the data's behavior or want to build custom dashboards. Also, SQL often allows for faster data storage and recovery, and works better with complex queries.
On the other hand, NoSQL databases are the better choice if you want to expand upon RDBMS's standard structure, or you need to create a flexible schema. NoSQL databases are also better when the data you’re storing and logging is coming from distributed sources, or you just need to store it temporarily.
The Takeaway
In the end, I can’t say that SQL is absolutely better than NoSQL or the way around. Each one has its own advantages and disadvantages, and you should make your choice based on your data, its application and what makes the development process easier for you.
SQL databases are older so they have a more established fixed schema design and structure. NoSQL databases are easily scalable, flexible and relatively simple to use due to their flexible schema.
So, what are the requirements of your data? Is your data well-structured? Are you dealing with a large amount of data?
Listen to your data and you’ll make the best choice.
