This topic we shall understand in brief about what is SQL DB, No-SQL DB and when to choose SQL and when to choose No-SQL.
9.1 SQL:
SQL stands for Structured Query Language. Here the data will be stored in a structured way in the form of rows and columns.
Each record will have a Schema. These schemas will help to do operations on the data. The data can be obtained from queries like ” SELECT name FROM username”. Similarly, we have commands for inserting, deleting, updating also.
When the data are distributed across tables, we use joins to retrieve the data. This is also called as relations.
9.1.1 Different types of relations are:
One to One: One user from “user” table will be having an email id in “contact” table..
One to Many: One user from “user” table will be ordering multiple produts from “products” table.
Many to Many: One user from “user” table, from “city” table, ordering “products”.
SQL DB are vertically scalable. i.e. by increasing memory, CPU. Most of the time they are used for monolithic applications. Scaling SQL DB can be expensive.
9.1.2 Some of the advantages of SQL over NoSQL DB:
1. It is better for relational data.
2. Normalization.
3. SQL language can be used to get the result that is very easy to learn.
4. Data integrity. As there is only one copy of data, any change can be done in that table itself.
5. Follows ACID properties.
9.2 NoSQL:
A NoSQL DB can be considered as a Non Relational database (no tables). No SQL will not have any schema. Usually we use NoSQL to work with big data and real-time web applications. The data will be stored as a collection.
The data can be stored in semi or no structured way. Because of this there is a possibility of duplication of the data. They provide Eventually consistent.
NoSQL is used for MicroServices or Distributed Applications.
NoSQL DB are horizontally scaled, means more servers can be added. Hence it is less cost for scalability.
9.2.1 Some of the advantages of NoSQL over SQL DB:
1. Can be used with Big Data
2. There is no predefined schema
3. NoSQL handles unstructured data.
4. Cheaper to manage.
5. Scaling is easy, horizontal scaling.
9.2.2 Types of NoSQL DB
MongoDB
CouchDB
Apache Cassandra
Neo4J
So which one to use? SQL or NoSQL DB?
SQL:
when we know that the data or schema is not going to change, then we choose SQL DB.
We want ACID compliance.
NoSQL:
When using Big Data
When the data needed to be stored across the servers.
Sometime in big projects, we use both of the database.