Difference between SQL and NoSQL databases
Whenever you want data persistence, you generally want to use a database.
For those of you who need a quick refresh on what a database actually is:
A database is a collection of information stored in a way that it can be easily accessed, managed and updated.
As time evolve, we store more and more data. The term Big Data is certainly not just a hype word anymore. A good example of huge amounts of data being saved is through social media.
But how is this data saved? How is it organized?
The “classic” way of storing data is through the use of SQL databases. For a long time, SQL databases has been a key part of the IT infrastructure.
But, as with everything in the ever-changing world of computer science, a new type of database has emerged – the NoSQL database.
The rest of this article consists of three sections:
- Description of SQL
- Description of NoSQL
- Comparisen between SQL and NoSQL
What is SQL?
SQL is an abbreviation for Structured Query Language. Having defined what SQL actually means, we can get a feel for what it is actually used for.
SQL is used to access, update and manipulate databases. Unlike many programming languages, SQL is pretty straight forward. In fact, most of the time, SQL queries are written in plain english.
The major commands in SQL are the SELECT, DELETE, UPDATE and INSERT commands.
SQL is a language used to access, update and manipulate databases.
We now know that SQL is a query language for databases. But how is the data in the actual database organized? This is crucial, and the basis for SQL is RDBMS – Relational Database Management System.
When storing data in a RDBMS, the data is stored in tables. A table is a set of related data fields connected to an entity. This may sound complicated, but as you’ll see in the following example, it is not complicated at all.
Let’s assume you want to store data about students in a database. Our entity (table) will be named Student, and the data fields will be everything we want to save about a particular student.
This visual representation of the entity is a good example of the type of information we might want to persist in our student database. However, one key feature of a RDBMS, is that database tables can be related to each others.
Expanding on our previous example, let’s say we want to keep record of which course a student is enrolled in. To do this, we need a new entity / table, named course.
Wait, what? Why can’t we just append a new data field to the student table?
Surely, we can do this – but this would be bad design. Obviously, multiple students can be enrolled in the same course. Thus, the same information would be saved multiple times. This is called redundancy, which is not something we usually want.
And what if we would like to store more information about a course? Assuming we want to store the cost to take each course. How would we append this data to the Student table? It would make no sense to have a data field “cost” in the student table.
Before I strive off too far into why redundancy is bad, let’s create our database table named Course.
Now, this is where the relations between tables comes in.
For the sake of argument, we assume that each student can and must be enrolled in one, and only one course.
The relationship between the entities Student and Course is in this situation what we call a MANY-TO-ONE relationship. Said in other words: A student can be enrolled in one and only one course, and a course can have many students.
This explains the key features of a RDBMS database which uses SQL.
The database tables (entities) in a SQL database are related to eachothers.
What is NoSQL?
NoSQL stands for Not only SQL, and is developed in response to the classic relational SQL databases. In fact, NoSQL has a completely different way to store and retrieve data. In short, NoSQL uses non-relational databases.
As previously mentioned, with the rise of Big Data, we need to store massive amounts of unstructured data (think Social Media..). If we would like to store this kind of data in a database, we don’t have the luxury of developing a relational database with a clearly defined schema. In other words, data is scattered and all over the place.
This is where NoSQL comes in. Non-relational databases provides greater flexibility, and is great for non-structured data.
Previously, we described relational databases as entities (tables) with relations between eachothers. NoSQL databases are document-oriented, and the data in each document can be information of all types.
Let’s assume you want to develop a social media application, and you need to store photos, videos, posts and profile information in a database. With a NoSQL-database, all this information can be stored in a single document. This document is easily accessible, as we only need to access one particular document to get information about users of the application.
However, although NoSQL databases are non-relational, it is certainly necessary to have some form of internal structure in the database.
Primarily, there are four types of non-relational databases:
- Key-value model: Data is stored in key-value pairs. This is the simplest form of a NoSQL database, and data is accessed through the indexed keys.
- Column store: Data tables are stored as columns, not as rows, as with SQL databases.
- Document database: The document database is built on the same principles as the key-value model. Each key in the database is connected to a complex data structure, known as the key’s document. Each document can contain several more key-value pairs, and even nested documents.
- Graph stores: Used to store networks of data. A good example is social media data.
SQL vs NoSQL
Generally, if you have structured and consistent data, you would use a SQL database. On the other hand, if your data is not structured and consistent, you would use a NoSQL database. In reality, it is of course not as black and white as this. The following table compare SQL and NoSQL databases.
|SQL databases are relational, and the basis for these databases are RDBMS.||NoSQL databases are non-relational.|
|SQL databases require that you develop a schema for your internal structure in advance. All data that is to be stored in the future has to obey to this schema.||NoSQL databases have a dynamic schema. The internal structure of your database can change over time. In other words, NoSQL provides great scalability.|
|SQL data is stored in the form of tables / entities, as we saw in our previous Student-example.||NoSQL does not store data in the form of highly structured tables. NoSQL provides four common types of databases.|
|Querying data in a SQL database is highly standardized through the SQL, Structured Query Language.||NoSQL does not have a standardized query language, and one would often benefit from APIs to access data in a NoSQL database.|
|SQL databases require that the data to be stored are highly structured.||NoSQL databases are great for unstructured data.|