When to choose SQL and NoSQL

Rajdeep Chandra
Nov 1 · 4 min read

If someone asks you why would you choose a NoSQL database over an SQL you can relate to the below pointers which describes when and why you should choose such databases effectively in a distributed system architecture.This is not a comparison between the two but most importantly when to choose one in your application

SQL:

The main part is that we can have relations with different tables having same attribute and can have normalisation. This database is quite useful when your application is doing a lot of update and delete of records. There are few more reasons to choose a RDBMS such as data consistency(ACID) and read optimisation.

1. Insertion is expensive

A person table in a SQL database

So whenever you are performing an insertion in a SQL database you have make you data insert one by one into the upper columns moving the pointer and also you will also need the data in the address table to e mapped to the person table with the help of a join which is expensive operation.

2.Schema is not easily changeable

to insert another column we need to add locks to maintain consistency

Suppose in the above table if you want to insert another column or an attribute like rating as such you need to add something called ‘locks’ to maintain the consistency, which is again an expensive operation in a SQL database.

3.Not Built for Scale

Most of the SQL databases are not built for horizontal scaling and scaling a SQL database is tough. So most of the users who uses this Database still have a set of users but not advisable to use in a large scale distributed system.

4.Read Optimised

SQL databases are read optimised that means if you want find the age of people working in your company who are less than 30 you just need to move your pointer to the age column in the table and do a minimal operation to find the count.

5.Data Consistency

Data consistency are always maintainer because they give us ACID properties. ACID is always guaranteed so thats why transactional systems use this databases for their financial transactions

6.Joins are easy to construct:

SQL databases are built for joins such as inner join, outer join,left outer join and so on.As in the above diagram, the relation between tow tables are inhibitive with foreign key and primary key associations.

NoSQL:

This database system has been leveraged many big companies as their primary database. The data is stored in a JSON format rather than a table.Each JSON object is a document which is represented by an ObjectID as the primary key. The above table schema will be rewritten in NoSQL as a document which will hold:

{
"key":111,
"name":"Raj",
"age":30,
"address":{
"city":"BNG",
"country":"IND"
},
"role:"SDE2"
}

1.Insertion is less expensive

The whole row has changed to a document which will hold the above key bindings of each column.Here we won’t be having any foreign key relationship with the address table whereas the whole address key object will hold the data inside the parent object only like a JSON nesting. Since the district column doesn’t have a data so we don’t even store the data.

So when you are sending a request or inserting data the above whole JSON object will come and set in the database in a single insert.And whenever you are pulling out a row from a NoSQL you are actually pulling out the whole object which is less expensive.

2.Schema is easily changeable

{
"key":111,
"name":"Raj",
"age":30,
"address":{
"city":"BNG",
"country":"IND"
},
"rating:"A"
}

Here in the above JSON document if you want to insert the attribute of rating like the way we wanted to do in SQL, we can do it easily irrespective of the other documents. We can add it straightaway. The consistency will be preserved and no other change to the document is required.

3.Built for Scale

Most of the NoSQL databases are built with horizontal partitioning(What is horizontal partitioning?).Since most of the users who wants to use a NoSQL database they tend to scale their application so availability precedes consistency.

4.Not Read Optimised

NoSQL databases are not read optimised that means if you want find the age of people working in your company who are less than 30, the database will return you each document and after that you will need to do some expensive operation to get your return values.

5.No Data Consistency

NoSQL databases doesn’t retain ACID properties of a database thats why financial transactions are not possible, because if the ACID and data consistency is not maintained it doesn’t make sense for them to use.

6.Joins are hard to construct

There are no concept of joins in NoSQL databases that means if you relate the address field in two tables you need to go to every document and find the address in both the table and then with the returned result you need to merge them finally to get your result.


Thanks for reading! If you have any questions, feel free to reach out at rajrock38@gmail.com, connect with me on LinkedIn, or follow me on Medium and Twitter.

If you found this article helpful, it would mean a lot if you gave it some applause👏 and shared to help others find it! And feel free to leave a comment below.

Rajdeep Chandra

Written by

Full Stack Developer • Interested in JavaScript.Everything() • Writes on programming • Simple rule:Code less code efficiently

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade