Databases

SQL vs NO-SQL Databases — Everything you need to know

Hello everyone. In this article, we will be looking at what is SQL and NoSQL databases and then we will continue in-depth about both these types and also discuss their benefits and use cases.

What is a SQL / Relational Database?

SQL databases have been used for decades and they are also called Relational databases. Relational databases store data in a structured tabular format (using rows and columns). In this way, the data can be easily identified and retrieved. It is called relational because the relationships can be established between the tables through mapping fields.

SQL stands for Structured Query Language, the language used by relational database management systems. It is a declarative language and the SQL commands can be divided into 5 categories based on their functionalities as follows

  1. Data Definition Language(DDL)
  2. Data Control Language (DCL)
  3. Data Manipulation Language (DML)
  4. Transaction Control Language (TCL)
  5. Data Query language (DQL)

Some of the popular relational databases are

  • MySQL
  • Oracle
  • PostgreSQL
  • Microsoft SQL Server
  • SQLite
  • MariaDB
  • IBM DB2

ACID Properties in Relation Database

Relational Databases follow the ACID principles as follows

 What is a NO-SQL Database?

NoSQL stands for Not only SQL Database and any database which is non-relational falls under NoSQL database. These databases are non-tabular and stores data in a way different than relational databases. The data models are flexible. The NoSQL databases came into existence mainly due to processing Big Data, IoT, etc. Most of the NO-SQL Databases do not follow ACID principles but they follow BASE in order to gain other benefits, like scalable and resiliency.

BASE Properties

NoSQL database relies on a model known as the BASE model. BASE (Basically Available, Soft state, Eventual consistency).

Basically Available: The Database is always available and guarantees the availability of the data. There will be a response to any request.

Soft state: The system state could change over time

Eventual consistency: The system will eventually become consistent once the data is synchronized across the distributed cluster

Types of No-SQL Databases

The No-SQL Databases can be divided into 4 types as follows

  1. Key-Value Database

Data in this type of DB is stored in key/value pairs to handle heavy loads of data. They use a simple hash table data structure to store the data for faster storage and retrievals. The Key-Value databases are based on Amazon’s Dynamo paper. The use cases for this type of DB are for storing Shopping Carts, Caching, etc.

Examples of Key-Value Databases: Redis, Memcached, AWS DynamoDB, Riak, etc

2. Document-Based Database

Document-Oriented NoSQL DB stores and retrieves data as a key-value pair but the value part is stored as a document. The document can be stored in JSON or XML formats; The value is understood by the database and can be queried. These databases can be used especially when you don’t know the schema up front and it is flexible to add the columns later.

The document-based database is mainly used for CMS systems, blogging platforms, real-time analytics & e-commerce applications.

Examples of Document Based databases: MongoDB, CouchBase, RavenDB, etc

3. Column-Based Database

Column-Based databases work on columns and are based on BigTable paper by Google. In this database, every column is treated individually and the values of single-column databases are stored contiguously. The names and the format of the columns can vary from row to row within the same table, unlike relational databases.

The above picture demonstrates that a single row has multiple columns with each having different types. Let us take the example below to understand. Every row has different columns. The row key for 1st record is Bob and the columns are email, gender, and age. So, multiple columns exist inside a single row

The Column Based databases deliver high performance on aggregation queries like SUM, COUNT, AVG, and MIN if the data is computed and stored in columns. These databases are very useful for data analytics, big data, data warehouses, business intelligence, CRM, etc

Examples of Column-Based Databases: Casandra, CosmoDB, Bigtable, and HBase.

4. Graph-based Database

The Graph database stores the entities as well the relations amongst those entities as graphs with nodes and edges. Nodes store information about data entities and the edges store information about the relationships between the nodes. Every node and edge has a unique identifier.

A Graph database is multi-relational in nature and traversing the relationships are fast as they are already captured into the DB hence we don’t need to re-calculate every time

The Graph base database makes good uses cases for social networks, logistics, and geospatial data.

Examples of Graph-Based Databases Amazon Neptune, Polyglot, Neo4J, Infinite Graph, OrientDB, FlockDB

Differences between SQL and No-SQL Database

Having seen about SQL and No-SQL databases above, now let us compare the differences between both

 

When to use SQL vs NoSQL Database?

The choice of the database depends on the use case. If the data is structured, then you can go for a relational database and if the data schema is not clear or confirmed, then NoSQL is a good choice. NoSQL Databases work well for articles, videos, social media content, Big data, etc as we saw above in the different types of NoSQL databases. NoSQL Databases are not a replacement for SQL DB as the functionality are different and both can coexist in the same application.

Hybrid SQL and NoSQL databases

There is a database type called NewSQL databases, which is a hybrid of SQL and NoSQL databases. This is an interesting concept that combines the relational and NoSQL database methods in a single Database instance. The diagram below depicts the functionalities of SQL, NoSQL, and NewSQL database

The examples of NewSQL databases

  • Clustrix
  • CockroachDB
  • Spanner
  • H-Store
  • HyPer
  • MemSQL
  • NuoDB
  • Hana
  • VoltDB

Nowadays, modern SQL databases incorporate a hybrid approach where you can choose between relational and NoSQL models. For example, MS SQL Server, Informix, Oracle, and PostgreSQL support the JSON data format.

In this article we learnt about SQL (relational) and NoSQL (non-relational) databases. We saw the different types of NoSQL databases and then compared SQL and NoSQL databases. We went through a Hybrid database called NewSQL to conclude

Hope you had good learning and thanks for reading this.

I work as a freelance Architect at Ontoborn, who are experts in putting together a team needed for building your product. This article was originally published on my personal blog.