When it comes to using, learning, or choosing a database, one of the biggest issue is to determine which type better suits your need.
What is the best choice for me? SQL relational or NoSQL non relational database?
Let’s try to figure this out by first looking at the big picture :
The concept behind SQL relational databases is the table, we will have one for each piece of information we will deal with. Each table will be composed of columns and rows, representing an aspect of data. Each table has one or more columns to represent the primary key, this is a piece of information used to uniquely identify an instance of it. In a relational database, we can also establish relationships between tables, for example, a row of table A can refer to a row in table B.
The language used to work with SQL relational databases is SQL (structured query language). On one hand, this language is extremely powerful, SQL is, in fact, one of the most versatile and widely-used options available, making it a safe choice and especially great for complex queries. On the other hand, it can become quite restrictive. Due to the core behind-the-scene working of SQL databases, using this language will require you to use predefined schemas to determine the structure of your data before you work with it. And all of your data will have to follow the same structure. So, understand how this can become quite complicated if you desire to make a major change to how you structure your information, that would require a lot of work and preparation.
On the other hand, for NoSQL databases this whole pre-defined structure is absent, your data, won’t be stored anymore in tables but in objects that can be completely different and be structured in totally different ways. You can store data in a document-oriented way, graph-based way or organized as a key/value structure. This flexibility means that we will be able to create documents without defining their structure first, add fields on the go, and use different documents with completely different structures. Also notice, with NoSQL databases, there are no relationships! In fact, we will use two mechanisms called Embedding and Referencing to join informations.
Now that I have explained to you the differences in concept, let’s compare directly the two technologies to find out which one can better suit your needs :
MySQL (Relational database)
- Open source and free
- Very used technology that has matured over time, with a huge community and very well tested
- Compatible with most of the platforms like Windows, Linux and Mac. You can also connect it with Node.js, Ruby, C#, C++, Java, Perl, Python and PHP, meaning that it’s not limited to SQL query language.
NoSQL (Relational database)
- Extremely fast especially for simple queries
- Highly scalable
- You can use different schemas to work with NoSQL databases
- Ability to add columns on the fly
What to choose?
If you have problems establishing a schema for your database, your information structure is not clearly defined, or if simply your database schema will continue to change then go with a NoSQL database.
Applications that might need this solution are mobile-apps, content analyzing apps, or simply products from a rapid growth business.
If, on the other hand, you want to benefit from the predefined structure of a SQL database go with a SQL database.
Applications that might need this are: Inventory apps, accounting systems.