Thread by @swapnakpanda

🛢SQL and NoSQL Database

👉 What are these?
👉 Key Differences
👉 How to choose between them?

🧵 👇

1️⃣ What is a Database?

A database is an organised collection of data stored in a computer system (typically a server machine, but can be any machine) so that it can be accessed and managed easily.

✪ Initially each database had a separate way of data management. The way one database was storing and managing data may vary from another.

✪ But with time this practice got normalised. The "Relational Database" design came into picture.

2️⃣ What is a Relational Database?

✪ A relational database is a "collection of data items" that have some "pre-defined relationships" between them.

✪ Typically these items are organised as a set of "Tables" with "Columns" and "Rows".

✪ Relationship between 2 tables are established based on one or many similar columns between them.

✪ Relational database became very popular. Because of so many similar kind of database exist, it was thought to develop a common language to manage both data and structures.

3️⃣ What is SQL?

✪ SQL stands for Structured Query Language.

✪ SQL is a computer language for storing, manipulating and retrieving data stored in a relational database.

SQL has 2 parts.

→ DDL: Data Definition Language (to define the structure of data)

→ DML: Data Manipulation Language (to manipulate and, retrieve data)

✪ When we say "Structure" of data, it is also understood as "Schema" of data.

✪ Though a database is "Relational" and the language used in it is called "SQL". But it has become normal to use "Relational Database" and, "SQL Database" interchangeably.

✪ In a SQL Database, data are kept fully structured. Sometimes data are very tightly coupled.

4️⃣ NoSQL Database

✪ SQL Databases were good and served many purposes. But with time data volume increased like anything.

✪ For these big data, SQL was found to be inefficient at many occasions. To improve the efficiency, many started designing new databases.

✪ All these databases didn't keep it mandatory for data to be structured. Hence SQL was not going to work. So, a new term was coined "NoSQL".

✪ NoSQL doesn't mean "Not SQL" rather "Not Only SQL".

✪ NOSQL databases were mostly distributed. Hence they were following CAP Theorem.

✪ CAP Theorem states that no distributed system can support more than any two of Consistency, Availability and Partition tolerance.

5️⃣ NoSQL Categories

NoSQL Databases are categorised mostly in 4 categories.

→ Document Based
→ Key-Value Based
→ Column Based
→ Graph Based

I will describe each of these in separate threads.

6️⃣ SQL vs NoSQL

Now, as we have fundamentals cleared about both SQL and NoSQL databases, let's find out major differences.

Let's classify and then we will elaborate each one.

→ Architecture
→ Schema
→ Scalability
→ Data Structure
→ Properties

6️⃣.1️⃣ Architecture

✪ SQL Databases are "Relational" while NoSQL databases are "Not Relational".

✪ SQL Databases are not mandatorily distributed while NoSQL databases are architected for distributed systems.

6️⃣.2️⃣ Schema

✪ Schemas in SQL Database is mandatory and, predefined.

✪ Schemas in NoSQL Database is dynamic. It is not mandatory to define a Schema before creation of data.

6️⃣.3️⃣ Scalability

✪ SQL Databases are mostly "vertically scalable" means you need to increase the machine's CPU, RAM, Storage for scaling.

✪ NoSQL Databases are "horizontally scalable" means you just need to add new machines to scale up. NoSQL databases are hence distributed.

6️⃣.4️⃣ Data Structure

✪ SQL Databases are Table based.

✪ NoSQL databases are either Document based, Key-Value based, Column based or Graph based.

6️⃣.5️⃣ Properties

✪ SQL Databases are ACID compliant. It means consistency of data is of high priority.

✪ Where as NoSQL Databases support any 2 of CAP (Consistency, Availability and Partition Tolerance).

7️⃣ How to choose?

✪ SQL databases are better for multi-row transactions, while NoSQL is better for unstructured data like documents or JSON.

✪ If data can grow quickly and you want to scale up immediately on-demand, then NoSQL should be the preferred one.

8️⃣ Examples

✪ Relational Database: Oracle, MySQL, PostgreSQL, Microsoft SQL Server

✪ Document Based: MongoDB

✪ Key-Value Based: Redis, DynamoDB, Couchbase

✪ Column Based: Cassandra, HBase

✪ Graph Based: Neo4j, Dgraph

That's it for this 🧵

Hope you like it and find it useful. I will further describe about NoSQL databases in future threads.

Till then, 👋