Whether you have worked with databases, are about to work with one, or haven’t worked at all, this topic is something you should know irrespective.
You need to always think about how your data looks like first, how you are about to query it and what are your scalability requirements. Whether it's you choosing the perfect database for your personal project or it’s an organisation, It is an important decision whether to pick a relational (SQL) or non-relational (NoSQL) data structure.
This article is not about which type is better but about which type suits you for your current project requirements. Each one has clear advantages and disadvantages which must be kept in mind.
If you just want to see the key-points mentioned in this article skip directly to the end.
Now with that said, let’s get started
What is SQL?
SQL or Structured Query Language is pronounced as “S-Q-L” or sometimes as “see-quel” is a standard language to access and manipulate Relational Databases.
It gives the users the power to create queries to INSERT, DELETE, UPDATE, SEARCH, ALTER, and to do a lot more with the Database. It is a very powerful language and is preferred by lots of developers as it can handle very complex queries as well.
Common and Popular Examples of Relational Databases which use SQL are —
MySQL
Oracle
SQLite
Postgres
MS-SQL
A very Basic syntax pattern of SQL statement is —
General--
SELECT * FROM <TABLENAME> WHERE <ATTRIBUTE> = <VALUE>
Practical--
SELECT * FROM EMPLOYEE WHERE Salary > 90000
What is NoSQL?
As the name suggests NoSQL is “not” SQL, in other words, it is a non-relational database and is unstructured. Due to its unstructured nature, it is sometimes called as UnQL
It follows a database design which can take all sorts of data models. It mainly consists of key-value pairs, graphs, documents, etc. It is especially used to store large sets of distributed data.
Common and Popular Examples of NoSQL Databases are —
Mongo DB
BigTable
Cassandra
Hbase
Redis
Information may be stored in many ways in this type of unstructured databases, but a widely followed approach is to store the information in **JSON **format.
{
role: "employee",
salary: "90000"
}
Comparing SQL vs NoSQL
Now that we have a basic understanding of what SQL and NoSQL database means, it would be much easier to compare the two types and figure out which one better suits your needs. Let’s go ahead and compare them now.
Database Structure
A SQL Database follows a table like structure which can have an unlimited number of rows and every data present inside the database is properly structured with Predefined Schemas, it is basically used to manipulate Relational Databases Management Systems.
A NoSQL Database is a Distributed Database where the data is very unstructured with Dynamic Schema. Unlike SQL it cannot have unlimited rows but it follows a **Standard Schema Definition *and can store all sorts of data models with large distributed data in the form of **key-value pairs, graph databases, documents or wide-column stores*.
Syntax
SQL is a very powerful language and is very handy when it comes to access as well as manipulate not just simple but very complex queries as well. It uses SQL syntax to define and manipulate data. An example of this syntax is —
SELECT * FROM STUDENT WHERE NAME = "Sayan" AND MARKS > 80;
Whereas in NoSQL the queries are mainly focused on the **collection of large documents *and it is not a good option to manipulate complex queries. As there are different types of NoSQL databases available therefore it *doesn’t follow a single syntax base, the syntax differs from database to database. The most commonly used format to store data is **JSON.
{
name:"Sayan",
marks:"85"
}
Hierarchy
SQL Databases are stored in the form of tables consisting of n number of rows thus it cannot follow a hierarchical approach rather all the data is present individually in each row under a specific attribute with its own significance.
Whereas NoSQL is a very good fit for hierarchical data storage as it stores the data in the form of key-value pairs in JSON format and thus serves as a better fit for large distributed data.
Scalability
A SQL Database is always vertically scalable. The meaning of this sentence is that if there is more load on the server then it can be handled by increasing the CPU, SSD, RAM, etc. This will work only on a single server. Thus by this, you may already be aware that this requires more strain on a single server.
A NoSQL Database, on the other hand, is horizontally scalable. This means that if there is excess load on the server then it will simply add a few more servers to handle the large traffic. It is simple and very efficient and prevents the server from being non-responsive.
Stability
A SQL Database is more stable and is always preferred when doing heavy duty transactions. The reason being it keeps the data very secure through data integrity and atomicity. It follows ACID Property which stands for Atomicity, Consistency, Isolation, and Durability.
A NoSQL Database is less stable than a SQL Database when there is high load on the server or even when performing complex transactions. It follows Brewers CAP Theorem **which stands for **Consistency, Availability, and Partition Tolerance.
Click to know more about Brewers CAP Theorem
What is the right choice for your business?
This decision completely depends on the business type and the ideation of the business. If you want to work with pre-defined structures and set schemas then SQL Databases is the correct choice for you. If you are building anything similar to an inventory monitoring system or accounting system then SQL Database is a better fit.
But if you want to build an application with dynamic schema or if your business has a very rapid growth and no schema definition then NoSQL Databases is what you need. If you cannot determine the type of schema your application wants or you want to keep it changing from time to time like in case of mobile apps, analytics or content management then NoSQL is a better fit.
Key-points in Short
Thank you for reading this. I really appreciate all the effort. If you’re having any doubts comment below and I’ll try to answer them.
Peace Out. ✌🏼
Top comments (3)
Hierarchical schemas aren't that problematic in SQL. Say for a tree structure of same data type it's enough to add a single indexed foreign key column "parent".
Getting parent entity from such structure is obvious and for getting child entities, one would do a simple self join.
For graphs, one can create a table that contains two foreign key columns to store graph edges.
Although writing graph algorithms in plain SQL would get hairy, but luckily databases such as PostgreSQL support writing stored procedures in other languages, so one can write highly performant hierarchical data algorithms that run on top of database server.
Doing tree or graph querying isn't even that hairy in SQL anymore. Its part of the SQL standard at this point and supported by most database engines :) This is something I use quite extensively nowadays.
mariadb.com/kb/en/recursive-common...
That's interesting, I had used common table expressions before but had no idea their recursion can be used for hierarchical structure querying.