What type of database have an impact on?
There are eight main aspects you should consider before picking the model of your database.
- Development speed
- Data consistency
- Performance
- Maintenance
- Security
- Scalability
- Flexibility
- Cost
Each of them has various priorities and that depends on the type and scale of your application. It's wise to spend some time for consideration and choose rightly as changing your mind in the middle of a developing process can be tricky or near impossible. In terms of possibilities both models are similar but only on the surface. There are good reasons why no relational (NoSQL) databases were invented. But it's even more interesting why relations (SQL) databases still exist and are picked for new applications by many leading companies. In this article I will elaborate on the eight mentioned aspects and sum it up in the table in the penultimate chapter.
Why do most apps use relational databases?
Relational databases where the first one that could be reliably and easily used on a massive scale at the time. The famous Oracle company was the first to build a successful commercial database management system (DBMS) based on relational databases in 1979. The idea behind DBMS is to enable working with databases with implemented transaction models (ACID) in a fairly easy way using API (querying in specific languages like SQL). After that the market experienced a huge and constant supply of similar products developed by different companies. Most of them failed to succeed but their work impacted the relational databases development and helped make them reliable and mature in fairly short time.
I think it's worth mentioning the fact that Oracle didn't invent relational databases nor its competitors. The author of relational database theory is Edgard Codd and thanks to his work (1970) software companies could start implementing workable relational databases.
It turned out that the relational database model is so good and well thought out that even today after 50 years of it's conception it's still worth considering implementing in your application as it should just work out in most cases by default.
Data consistency and easy usage
Thanks to reliability and simple query language for a very long time there was practically no alternative to relational databases. Reliability was achieved by implementation of transactional model ACID which has been guaranteeing data integration and consistency.
ACID transaction model:
- Atomicity - either all transaction are applied or none
- Consistency - the state of the database is consistent before and after transaction
- Isolation - one transaction doesn't have access to the other
- Durability - changes to the database are solid even if hardware fails
Structured Query Language (SQL) has been responsible for simple usage. Putting or querying data is very simple. Writing in SQL is commonly compared to writing in English. What can be an obstacle is understanding the relation model itself by an inexperienced developer though.
Strict database structure
When working with relational databases before inputting any data you have to define a structure of your database first. This has to be really well thought out. You can change it afterwards but doing this you have to take into consideration relations between tables and field data types which will become harder as your app grows. On the other hand strict predefined structure ensures less errors on the production which many companies consider as an acceptable trade-off. Additionally for experienced developers, databases with relations (ERD diagrams) can tell a lot about application itself, giving logical understanding of objects and their dependence and thus boosting development in later stages.
Analytical capabilities
This is a field where relational databases really shine out from a developer's perspective. SQL gives you a solid arsenal to get almost any answer from your data in a multidimensional section. The beauty of this lies in the built-in solution so you don't need any additional software to get what you want directly in your application. I wrote that SQL is an easy language but it's partially true. Getting expertise in this field takes time but when you get there many tasks far beyond putting and retrieving data can be done at incomparable performance to programing languages.
Vast variety
Because of the long term popularity and the reliability relation databases are still maintained by many well known and established companies like Oracle, Microsoft, Amazon or IBM. This means that this model will be still available on the market with solid support in coming years and many apps will start or continue using it. Currently you can choose from a variety of different free and payable solutions like: MySQL, MariaDB, Microsoft SQL Server, PostgreSQL and many more.
Are NoSQL databases better than relational?
In the same cases they are better, in some not so. They are meant to fill a gap rather than going to be a successor of relational databases. To understand this let's look at shortcomings of relational databases to see why no relational models emerged.
The relation model downsides
The relation model is not corresponding well with Object Oriented Programming. For developers using OOP it's counter intuitive to use data retrieved from a sql database. To help them out there are plenty of mappers for almost any language but still you need a proxy to get your work done efficiently.
Another common issue is related to the required structure of a database. It can take a lot of time to make a good model for an app and because of the relational model concept there may be a need to join multiple tables which can considerably slow down data retrieving.
The above mentioned downsides are related to development speed but it turned out that the biggest flaw lays in scalability of the relational model. The problem was first encountered by Google in around 2000 when started using enormous amount of data. Soon the same problem got Facebook, Amazon and other big companies. Each of them try to fix it in a different way using sharding, buffering or replication. Unfortunately none of it could ultimately guarantee ACID principles. The relation model limit was achieved and there was a necessity to create a new one to fulfill requirements of huge and distributed applications.
No relation model
Having a big problem to solve, companies started to develop new databases based on a no relation model. From 2005 there has been an ongoing boom and on the market emerged highly capable databases like HBase, Casandra, DynamoDB or MongoDB. When used with Hadoop they are suited for very large amounts of data and are often facilitated in cloud architecture. The interesting thing is the way they store data. Depending on the database it can be stored in columns, key-values or documents. The most popular in recent years became MongoDB proprably because its document base model which is easy for adoption for most developers.
NoSQL databases solve relational models issues related to OOP and complex data modeling. They also scale extremely well but often they can only implement full ACID principles on a document level. Additionally when used in distributed systems with high availability they use eventual consistency feature which means that it takes time to update data between different locations and this can be unacceptable in some cases.
Why to use SQL databases?
You may wonder why bother to use old fashioned relational databases when no relation model fixes main of its issues. Notice that only scalability was the reason behind creating the no relational model. The new model also comes with its flaws. That's why it's still worth considering relation database usage in your app as many leading companies still do.
SQL vs NoSQL databases comparision
SQL | NoSQL (MongoDB) | Winner | |
|---|---|---|---|
Development speed | Slow at early stages because of predefined schemas and types. It can also slow down adding new features to your app without redesigning the model. | Fast at early stages because no restrictions on documents and collections by default. | NoSQL |
Data consistency | Great data consistency. Full ACID principles on a database level. | Weaker data consistency. Often ACID applied only on a document level. | SQL |
Application performance | Slower but in most applications it won't be noticeable. Advanced queries (multiple joins) can slow down the performance but on the other hand can give a final answer without referring to the target application making it faster at the end. | By default it has better performance but lacks built-in more complicated queries which have to be delegated to the target application code or other software. | NoSQL |
Appliaction maintenance | Strict schemas and types help to understand and maintain databases. But there is a chance that the data model is of bad quality and it will make maintenance a nightmare. | Loosely inserted documents and coupled collections can lead to unmanageable chaos. There is a possibility to implement a schema though. | SQL |
Application security | Most SQL databases require at least admin credentials by default. Popular attacks like SQL injections are well know and simple to counter. Strict schemas and types also play an important part in security field. | Most NoSQL databases don't come with obligatory login but this can be set up after creating the database. Most attacks like NoSQL injections are less known to most developers compared to SQL injections. | SQL |
Application scalability | Applies horizontal scalability. That means there is a limit of your database but not too many applications will ever reach it. | Applies vertical scalability that can be practically endless. Used by Google with success gives a nice proof that you don't have to worry about it. | NoSQL |
Application flexibility | Little flexibility. It's similar to strict programming languages in many ways. | Very flexible. You can switch on/off many features like defined schemas, relations usage (yes, it's possible), embedded documents, strict types etc. | NoSQL |
Appliaction cost | There is slower development speed which can result in greater Time To Market. On the other hand there are more experienced developers and that can boost development anyway compared to the NoSQL database and produces lower cost. | Fast development speed by default but many companies try to define schemas anyway. A Smaller pool of experienced developers can result in higher Time To Market and greater cost. | Tie |
Edge cases
Both databases will work out in most of the existent and future apps but there are cases when you specifically should use one of them.
| Model | Reason | |
|---|---|---|
| IoT | NoSQL | Internet of Things projects can have incredible requirements in terms of reading and inserting data. NoSQL performance will be noticeable in such cases. |
| Online money transaction | SQL | Relational database has by default better data consistency guaranteed by ACID. Many NoSQL databases boasted about the similar accomplishment but there are multiple cases when it can't be proved. Better be on the safe side even with worse performance. |
| Huge distributed apps | NoSQL | Only non relational databases can handle such apps as proved by Google or Facebook more than decade ago. There is also a trade-off as eventual consistency in such cases though. |
What about ecommerce and blogging, two of the most popular web apps? Neither of them is an edge case and it doesn't matter which one you will use as long as you know exactly what impact they will have on your app. Notice that e-commerce is in most cases not related to online money transactions because you just delegate this function to other companies like PayPal or Stripe.