Databases in System Design - SQL vs NoSQL (When and Why)

Every system, no matter how simple or complex, eventually converges to a single fundamental need: it must remember things.

Users sign up, data gets generated, transactions happen, content is created, relationships form, and all of this needs to be stored, retrieved, and updated reliably. At a small scale, this seems trivial. You pick a database, store your data, and move on.

But as systems grow, data stops being passive.

It becomes the centre of gravity around which everything else revolves.

Performance depends on it. Scalability depends on it. Consistency, availability, and even user experience are shaped by how data is stored and accessed.

And this is where one of the most important decisions in system design emerges:

Should you use a relational database (SQL), or a non-relational database (NoSQL)?

At first glance, this appears to be a technology choice. But in reality, it is a design philosophy decision; one that reflects how your system models the world.

The Structured World of SQL

Relational databases, often referred to as SQL databases, are built on a simple but powerful idea: data should be organised into structured tables with clearly defined relationships.

This model has been around for decades and is grounded in mathematical principles. Each piece of data fits into a predefined schema. Tables are connected through relationships, and queries allow you to retrieve and combine data in flexible ways.

This structure brings clarity.

When you design a relational schema, you are effectively defining the shape of your data upfront. You decide:

• What entities exist
• What attributes do they have
• How they relate to each other

Once defined, this structure is enforced strictly. Every piece of data must conform to it.

This may feel restrictive at first, but it provides a powerful guarantee:

The data in the system is consistent, predictable, and reliable.

This is why relational databases are widely used in systems where correctness is critical. Financial systems, transactional platforms, and enterprise applications rely on the guarantees provided by SQL databases to maintain integrity.

For example, systems within companies like Oracle and Microsoft have long relied on relational databases to manage structured, high-integrity data.

The Power of Relationships

One of the defining strengths of SQL databases is their ability to model relationships between data.

Consider a simple scenario:

• A user places an order
• An order contains multiple items
• Each item belongs to a product

In a relational database, these relationships are explicitly defined. You can join tables together and retrieve complex, interconnected data with a single query.

This ability to perform joins allows systems to answer rich questions:

• What products has a user purchased?
• Which items are most frequently bought together?
• What is the total value of all orders in a given time period?

This expressiveness is one of the reasons SQL remains dominant in many domains.

Consistency

Relational databases are designed with strong consistency in mind.

They follow properties often referred to as ACID:

• Atomicity
• Consistency
• Isolation
• Durability

While these terms may sound theoretical, their impact is very real.

They ensure that:

• Transactions either complete fully or not at all
• The database remains in a valid state
• Concurrent operations do not interfere in harmful ways
• Data is not lost, even in the event of failure

This makes SQL databases particularly well-suited for systems where correctness cannot be compromised.

Think of banking systems. If money is deducted from one account but not credited to another due to a failure, the system becomes unreliable. SQL databases are designed to prevent such scenarios.

The Limits Begin to Appear

Despite their strengths, relational databases begin to show limitations as systems scale.

The first challenge is scalability.

SQL databases are traditionally designed to scale vertically. You increase the power of a single machine to handle more load. But as we explored earlier, vertical scaling has limits, both in terms of hardware and cost.

The second challenge is flexibility.

Because the schema is predefined, making changes to the structure of data can be complex. Adding new fields, modifying relationships, or evolving the data model often requires careful migrations.

In rapidly evolving systems, this rigidity can slow down development.

The third challenge is distribution.

Distributing relational databases across multiple nodes while maintaining strong consistency is difficult. It introduces coordination overhead, which can impact performance and availability.

These challenges do not make SQL obsolete, but they create space for a different approach.

Enter NoSQL - A Different Philosophy

NoSQL databases emerged not as a replacement for SQL, but as a response to the challenges of scale, flexibility, and distribution.

Instead of enforcing a rigid schema, NoSQL systems embrace a more flexible approach to data modelling.

Data is often stored in formats that closely resemble how it is used in applications:

• Documents
• Key-value pairs
• Wide-column structures
• Graphs

This flexibility allows developers to evolve data models quickly without being constrained by predefined schemas.

But more importantly, NoSQL databases are designed with distribution as a first principle.

From the ground up, they assume:

• Data will be spread across multiple machines
• Systems will operate across regions
• Failures will occur

This makes them naturally suited for horizontally scaled systems.

Companies like Amazon and Netflix have leveraged NoSQL databases to handle massive amounts of distributed data with high availability.

A Shift in Trade-offs

With NoSQL, the trade-offs begin to shift.

Instead of strict consistency, many NoSQL systems embrace eventual consistency.

Instead of rigid schemas, they offer flexibility.

Instead of centralised control, they enable distributed scalability.

But these benefits come at a cost:

• More responsibility on the application to manage data integrity
• Reduced ability to perform complex joins
• Increased complexity in ensuring consistency when needed

This is not a drawback; it is a design choice.

And understanding this shift is key to choosing the right database.

Where This Is Heading

At this point, we have two distinct worlds:

• SQL: structured, consistent, relationship-driven
• NoSQL: flexible, scalable, distribution-friendly

But the real question is not which one is better.

It is:

When should you use each?

This is where database selection moves from theory to system design strategy.

The Many Faces of NoSQL

When people say NoSQL, they often imagine a single alternative to relational databases. In reality, NoSQL databases vary significantly in how they store and retrieve data.

Each type exists because different applications have different needs.

Document Databases - Modelling Data as It Is Used

Document databases store data in a format similar to JSON. Instead of splitting data across multiple tables, related information is often stored together in a single document.

This structure mirrors how applications actually consume data.

Instead of performing multiple joins to assemble related information, everything is already grouped together. This reduces query complexity and improves performance for read-heavy workloads.

This model is widely used in systems where:

• Data structures evolve frequently
• Relationships are hierarchical
• Fast reads are more important than complex joins

Key-Value Stores

Key-value databases are the simplest form of NoSQL.

Data is stored as a collection of key-value pairs. You provide a key, and the system returns the associated value.

This simplicity allows for extremely fast lookups and high scalability.

However, it comes with limitations:

• No support for complex queries
• No inherent relationships between data

These databases are often used for:

• Caching layers
• Session storage
• Simple lookup services

Wide-Column Databases

Wide-column databases organise data into rows and columns, but unlike SQL, each row can have a different set of columns.

This flexibility allows systems to handle large-scale, sparse datasets efficiently.

These databases are designed for:

• High write throughput
• Distributed storage across many nodes
• Analytical workloads at scale

Graph Databases

While SQL can model relationships, graph databases treat them as the core of the system.

Data is stored as nodes and edges, making it ideal for highly connected data.

This model excels in scenarios where relationships are complex and deeply interconnected, such as:

• Social networks
• Recommendation systems
• Fraud detection

Where SQL Still Shines

Despite the rise of NoSQL, relational databases remain dominant in many critical systems.

This is not by accident.

SQL databases are still the best choice when:

• Data relationships are complex and require joins
• Transactions must be strongly consistent
• Data integrity is critical
• The schema is stable and well-defined

For example, financial systems and transactional platforms within companies like Microsoft continue to rely heavily on relational databases.

Because in these systems, correctness is not negotiable.

Where NoSQL Excels

NoSQL systems shine in environments where scale and flexibility are more important than strict consistency.

They are particularly effective when:

• The system needs to scale horizontally
• Data models change frequently
• High availability is required
• Workloads are distributed globally

Platforms like Netflix use NoSQL databases to handle massive volumes of user activity, content metadata, and streaming data.

In such systems, slight inconsistencies are acceptable if they enable better performance and availability.

The Hybrid Reality

At this point, it might seem like you must choose one approach over the other.

But in modern system design, that is rarely the case.

Most large-scale systems use a combination of SQL and NoSQL databases, depending on the requirements of each component.

For example, a system might:

• Use SQL for payments and transactions
• Use NoSQL for user activity and analytics
• Use caching for performance

This approach allows systems to utilise the strengths of each model while minimising their weaknesses.

Companies like Amazon follow this strategy extensively, using different databases for different parts of their architecture.

The Real Decision Framework

Choosing between SQL and NoSQL is not about trends or popularity.

It comes down to understanding your system’s priorities.

Ask yourself:

• Do I need strict consistency or can I tolerate eventual consistency?
• Is my data highly structured or flexible?
• Will my system scale vertically or horizontally?
• Are relationships central to my queries?

These questions guide the decision more than any feature comparison ever could.

Final Thought

Databases are not just storage systems.

They define how your system thinks about data, how it evolves, and how it scales.

Choosing the right database is not about picking the most powerful tool:

It is about choosing the tool that aligns with your system’s reality.

Because in system design, the best solutions are not the most sophisticated ones.

They are the ones that fit the problem.