Disclaimer: This article was written few years ago and may no longer be relevant as software engineering has changed a lot in the last few years. This is what may be more relevant now: Future of Software Engineering - Gaurav ChandakNeed to decide which type of database to use for your product/system? Whether to use MySQL or MongoDB?
This article will serve as a general guide to decide whether to pick a SQL database or one of the NoSQL databases.
Before we go into the differences of SQL vs NoSQL databases, let's first go through a brief introduction of SQL and NoSQL databases.
SQL Database (Relational Database)
A relational database is a database based on the relational model of data.
The relational model organizes data into one or more tables (or "relations") of columns and rows, with a unique key identifying each row.
Rows are also called records or tuples. Columns are also called attributes.
Generally, each table/relation represents one "entity type" (such as customer or product). The rows represent instances of that type of entity (such as "Lee" or "chair") and the columns representing values attributed to that instance (such as address or price).
In SQL terminology,
- "Tuple" is known as "Row".
- "Attribute" is known as "Column".
- "Relation" is known as "Table".
Popular Relational Databases: Oracle, MySQL, MariaDB, Microsoft SQL Server, PostgresSQL, SQLite.
NoSQL database (Non-relational Database)
A NoSQL ("non-relational") database provides a mechanism for storing and retrieving data modeled in means other than the tabular relations used in relational databases.
NoSQL databases are increasingly used in big data and real-time web applications.
Popular NoSQL databases: MongoDB, Redis, Memcached, HBase, Cassandra, DynamoDB, Elasticsearch, Neo4J.
There are different types of NoSQL databases based on how the data is modeled. The most popular types of NoSQL databases are:
- Key-Value Store: Data is stored as key-value pairs. Examples: Redis, Memcached, DynamoDB.
- Document Store: Data is stored as documents (JSON, XML, etc). Examples: MongoDB, CouchDB, Elasticsearch
- Wide-Column Store: Data is stored in tables but the names and format of the columns can vary from row to row in the same table. Examples: HBase, BigTable, Cassandra, DynamoDB.
- Graph Database: Data is stored as graph nodes with edges representing the relationship between the nodes. Examples: Neo4J.
SQL vs NoSQL: Differences
There are multiple differences between SQL and NoSQL databases. Let's look at the major differences.
- Relational: SQL databases are relational and have fixed rows and columns whereas NoSQL databases do not.
- Schema: SQL databases have a rigid schema (structured data) whereas NoSQL databases have a flexible schema (unstructured data).
- Data Structure: SQL databases are table-based whereas NoSQL databases could be document-based, key-value, graph-based, or wide-column.
- Scalability: NoSQL databases can be horizontally scaled (add more servers) whereas SQL databases cannot and need to be vertically scaled (add bigger server).
- Normalization: In general, it is recommended to normalize tables in a SQL Database whereas denormalization is preferred in NoSQL databases.
- Joins: Since data is generally denormalized in NoSQL databases, joins are not required. Joins are generally required in SQL databases.
- ACID Transactions: Most NoSQL databases do not support ACID Transactions whereas SQL databases support ACID transactions.
Pros and Cons of SQL Databases
Pros
- Provides ACID Transactions
- Have lesser storage requirements
Cons
- No/limited horizontal scaling
- Rigid data models
- Not convenient for hierarchical data
Pros and Cons of NoSQL Databases
Pros
- Highly Scalable through horizontal scaling
- Flexible data models
- Can store hierarchical data as documents
Cons
- No/limited ACID Transactions
- Requires more storage because of duplication (denormalized data)
Should we use SQL or NoSQL?
Deciding which type of database would depend on your use case. The below points serve as a general guide to decide whether to pick a SQL database or one of the NoSQL databases.
We should use NoSQL if we are looking for:
- flexible data models,
- fast queries through denormalization,
- horizontal scaling,
- ease of development
We should use SQL if we are looking for:
- standard relational data model,
- fixed schema,
- ACID transactions,
- less data duplication,
- less storage,
- faster inserts/updates
Further Reading on System Design:
