In the world of data, it's not easy to hold and manage data, to do this correctly we have data management systems or in short software that holds data, manages the data, databases play a big role in organizing and retrieving information efficiently.

Two prominent types of databases are relational databases (RDBMS) and NoSQL databases. Each has its own set of benefits and limitations, and choosing between them depends on the specific requirements of a project.

In this blog, we'll explore what relational databases are, delve into the advantages and limitations of RDBMS, compare SQL and NoSQL databases, and discuss when to use each.

Make sure to subscribe to this article as I am going to cover all concepts of SQL like what are joins, schemas, indexes, horizontal scaling, functions, conditional functions, and queries, hosting SQL with Docker, etc in the upcoming days.

A relational database(RDBMS) is a type of database that structures data into tables related to each other based on dependencies and relationships. The tabular structure allows for efficient storage and retrieval of data.

A relational database structures data into multiple tables that connect similar to a spreadsheet/Excel sheet. For example, a company like Amazon, Paytm, or Flipkart may use one table to store customer data, another table to store order data that has been placed by customers, and link the two together based on a customer ID(Consider this unique string or number same as your Roll Number in college that is unique and help to identify you) that connects the relevant rows.

An example schema may have the following structure:

Customers Table

• CustomerID(primary key)
• FirstName
• LastName
• EmailAddress
• PhoneNumber

Orders Table

• OrderID(primary key)
• OrderDate
• OrderTotal
• CustomerID(foreign key)

The CustomerID field in the Orders table is linked to the primary key in the Customers table. This allows complex SQL queries to "join" data from both tables:

SELECT * 
FROM Customers
INNER JOIN Orders 
ON Customers.CustomerID = Orders.CustomerID

This JOIN statement matches customer data with their associated orders, enabling the database to relate more information to each order such as the customer's email address, phone number, etc.

Benefits of RDBMS:

• Structured organization- Related data is stored systematically in different tables that are linked, allowing for easy data access
• Scalability & Flexibility- It's easy to add more tables, rows, and storage space without disrupting the existing database structure
• Data Integrity- Features like ACID (atomicity, consistency, isolation, durability) ensure data accuracy and prevent corruption
• Standardized Query Language- SQL provides a standardized way for managing data and database administration

Limitations of RDBMS:

• Rigid Schema- Predefined schema doesn’t easily accommodate unstructured data
• Vertical Scaling Difficulty- Adding more CPU or RAM can get very expensive
• No Built-In Support for Unstructured Data- Unstructured data like images require additional functions

SQL vs NoSQL Databases

SQL databases are vertically scalable structured databases that allow for complex transactional queries. NoSQL databases are horizontally scalable unstructured databases focused on the storage and retrieval of large volumes of unstructured data.

When to use SQL:

• Need ACID compliance and data integrity (will write another blog to explain ACID property in detail for reference https://www.geeksforgeeks.org/acid-properties-in-dbms/)
• Structured data with pre-defined schemas
• Complex query-intensive environment
• Clear database structure won't change much over time

When to use NoSQL:

• Need flexibility for unstructured or semi-structured data
• Need horizontal scaling across distributed systems
• Low latency and high availability is critical
• Require dynamic schema for rapidly changing data

The choice between SQL vs NoSQL depends on the use case, data models, and scalability requirements. Many modern applications often use both SQL and NoSQL databases together to get the best of both worlds!

Benefits of the RDBMS / SQL Stack:

• Powerful Query Capabilities: SQL can join, aggregate, sort, group, and filter data for impactful insights
• Data Integrity Protection: Atomicity, Consistency, Isolation, and Durability (ACID) prevent errors
• The ecosystem of Tools: Visualization, ETL, and reporting tools expand the functionality
• Enterprise Reliability: Tested over decades for mission-critical applications

When Horizontal Scaling Fails for RDBMS:

• Data Volume Overflow: SQL databases may run out of storage capacity, leading to poor performance
• Increased Users: Too many concurrent users can overload database servers
• Transaction Bottlenecks: High transaction volumes exceed single-server capabilities

In these cases, the vertical scaling approach of adding more resources to a single server has limits. Instead, a horizontal scaling approach is needed where the database is distributed across multiple commodity servers.

This is where NoSQL databases shine…

The rest of the post will dive into NoSQL databases and when to use SQL vs. NoSQL in case you want to get more insights you can continue reading this article.

NoSQL Databases: Built for Scale

Unlike the rigid schema of relational databases, NoSQL databases have dynamic schemas that allow various data types to be stored together without a predefined structure. Some major examples include MongoDB, Cassandra, Redis, and Neo4j.

Instead of SQL, NoSQL databases have their own APIs or query languages optimized for specific use cases:

• Document Stores (MongoDB) allow storing JSON-style documents
• Wide Column Stores (Cassandra) store data in columns and rows
• Key-Value Stores (Redis) use a simple key/value metadata structure
• Graph Databases (Neo4j) connect data as nodes in a graph network

Benefits of NoSQL Databases:

• Flexible Schemas- Easily store unstructured or semi-structured data
• Massive Scalability- Designed from the ground up to scale horizontally
• High Availability- Distributed across hundreds of servers with no single point of failure
• Low Latency- Deliver very high performance across clustered systems
  

When to Use SQL vs NoSQL:

The strengths and weaknesses of SQL and NoSQL are complementary. Smart modern applications often leverage both relational and non-relational databases for different needs as we have discussed already:

Use SQL for:

• Need strong data consistency guarantees
• Complex transactional processing
• The established structure that evolves gradually

Use NoSQL for:

• Rapid ingestion of large volumes of data
• Dynamic schema for rapidly evolving data
• Object storage, Key-value store use cases
• Graph database requirements

By combining SQL and NoSQL databases, applications can be built for optimal query processing, flexibility, and scalability all at once!

Here are some examples of popular SQL databases, who uses them, and some NoSQL database examples as well:

MySQL

• Used By: Facebook, Twitter, YouTube, Spotify
• Popular open-source SQL database

NoSQL

Used By:Apple, Netflix, Facebook eBay, Forbes, Goldman Sachs, Verizon

As you can see, nearly every top tech company leverages both SQL and NoSQL databases in their tech stack because they each excel in different areas. SQL for traditional complex queries and transactions where ACID compliance matters; NoSQL for rapid iteration and massive scalability needs.

😊 I trust this article proves beneficial to you. I've endeavored to elucidate the concepts of RDBMS and NoSQL databases, including their limitations. My primary focus is on delving into the specifics of RDBMS, particularly the MySQL topic. Subsequent articles will delve deeper into MySQL fundamentals, providing detailed explanations along with examples and queries.