[Database] SQL and NoSQL

Upwork® Global Inc

SQL Database

• SQL is refer to “Structured Query Language”.
• Relational database.
• Table-based structure.
• Can use SQL programming language to deal with.
• More rigid and structured to deal with data.
• Work better with structured data, and can combine with related tables easily.

NoSQL Database

• NoSQL is refer to “Non SQL” or “Not Only SQL”.
• Non-relational database.
• File-based structure.
• More flexible to deal with data.
• Work better with less structured data, because it’s no need to rely on other collections.

Normalization

SQL :

• If need to add a new information in current table, can create another table to rely on it.
• Minimizes data redundancy.

NoSQL :

• Can also use another collection to rely on current collection, but need to fetch all data in current collection, and link in our program manually.
• Can just add a collection into current collection, and this achieve the faster queries.
• Update the information in multiple records will be significantly slower.

Data Storage (Schema)

SQL :

• The SQL storage model is a table with fixed rows and columns, and need to define the table in advance.

NoSQL :

• There are multiple storage models for NoSQL databases, including key-value model, column storage, document database, and graph database.
• Can store data in one place without defining the types of data in advance, and also can add different new types as needs.

Scaling

When the data have major increase, can either scale horizontally by adding additional servers to database or vertically by increasing the storage size of existing servers.

SQL :

• Most SQL databases are vertically scalable, meaning that need to add more RAM or CPU to existing single server to increase storage space.

NoSQL :

• NoSQL databases are horizontally scalable, meaning that need to add more servers to database to get more storage space.

transaction

SQL :

• Need to ensure ACID compliance (Atomicity, Consistency, Isolation, Durability). ACID compliance reduces anomalies and protects the integrity of your database by prescribing how transactions interact with the database.
• If we execute multiple updates individually, the update within a transaction ensures either both succeed or both fail.

NoSQL :

• Sacrifice ACID compliance for flexibility and processing speed.
• Modification of a single collection is atomic. If we update multiple values within a collection, either all multiple values are updated successfully or it remains unchanged.
• There’s no transaction equivalent for updates to multiple collection, we need to process in our program logic manually.