# SQL vs NoSQL: Choosing Linked Tables or Flexible Data Stores

> Learn how SQL and NoSQL databases store data, what each one does well, and how to choose between them for a real system.

**In short:** SQL databases keep data in linked tables with a fixed shape, while NoSQL databases use other models and are a label for many different designs.

From "System design", a free course by Venkat R. Murty: https://designervenkat.online/learn/system-design/sql-vs-nosql

## What You Will Learn

Every app needs a place to keep its data. That place is a database. In system design, two families are compared again and again: SQL databases, which store data in linked tables, and NoSQL databases, which use more flexible shapes. This lesson explains how each one stores data, what each one is good at, and how to choose between them for a real system.

- **Database:** A program that keeps data safe and lets an app read and change it.
- **Table:** A grid of rows and columns, like one sheet in a spreadsheet.
- **Row:** One record in a table, such as one customer.
- **Column:** One named field in a table, such as email.
- **Relational database:** A database that keeps data in tables that link to each other.
- **SQL:** Structured Query Language, the standard language for asking a relational database for data. People also use the word SQL as a short name for relational databases.
- **NoSQL:** A loose label for databases that do not use the table-and-join model. It covers many different designs.
- **Schema:** The rules that say which fields a record has and what kind of value each field holds.
- **Join:** A query step that combines rows from two tables using a shared value, such as a customer ID.
- **Transaction:** A group of changes that must all succeed or all fail together.
- **ACID:** Four promises a database makes about transactions: atomicity (all or nothing), consistency (the rules are kept), isolation (work done at the same time does not mix), and durability (saved changes survive a crash).
- **Rollback:** Undoing the unfinished changes in a transaction.
- **Horizontal scaling:** Handling more load by adding more machines.
- **Vertical scaling:** Handling more load by making one machine stronger.
- **JSON:** A text format for nested data made of named fields. Many document databases store records in this style.

## What Are the Two Ways to Store Data?

The main difference is the shape of the data. A relational database fits data into tables with set rules. A NoSQL database offers several flexible shapes, and each one is built around a different way of reading data. Neither shape is better in every case.

### How Do SQL Databases Store Data?

A relational database keeps data in tables. Each column is a named field, such as name or price, and each row is one record. Every table has a schema, so every row must follow the same field list. Relational databases have been used for decades, so there are many mature products and many people who know how to work with them.

Tables are linked by shared values. An order can hold a customer ID that points to a row in the customers table. When a question needs data from both tables, a join combines them in one query. Because each fact is stored once, a correction is made in one place.

SQL databases also usually support ACID transactions. A transaction can change several rows and several tables, and the database makes sure they all save together or none of them save.

### How Do NoSQL Databases Store Data?

NoSQL is not one design. It is a label for many databases that do not use the table-and-join model. Each family has its own shape, and each suits different jobs.

- **Key-value:** Each value is found by its key, like a cart ID. Lookups are direct and quick.
- **Document:** Each record is stored whole as a flexible, JSON-style document that can hold nested fields.
- **Wide column:** Related data is kept in wide rows that can hold many fields. This shape suits very heavy write loads, such as sensor readings.
- **Graph:** Data is kept as nodes (the items) and edges (the links between them). It suits questions about connections.

NoSQL schemas are flexible. Two records in one collection can have different fields, and an app can add new fields later without changing every old record. Data is often grouped by how the app will read it, so one lookup can return everything a screen needs, without a join.

## What Does Each Side Do Well?

Each family wins in different situations. The best way to see the difference is to look at the strengths and the costs side by side.

### Where Does SQL Shine?

- **Related data:** Customers, orders and products link to each other, and tables with joins model those links cleanly.
- **Exact numbers:** Money, stock counts and bookings need ACID transactions so that each change is either complete or absent.
- **Flexible questions:** The query language can filter, sort, group and join data in many combinations. This suits reports and questions nobody planned for.
- **Built-in rules:** Constraints, such as a balance that must not drop below zero, stop bad data at the door.

Relational databases can also grow across several machines, but this takes extra work. Splitting data across machines adds a lot of operational effort, and joins and transactions can slow down when they span machines. That topic has its own lesson.

### Where NoSQL Shines

- **Huge volume:** Many NoSQL systems are designed to add servers as load grows, which is horizontal scaling.
- **Simple, fast lookups:** Finding one item by its key is quick and direct, which suits carts and login sessions.
- **Changing shapes:** New fields can appear without a large migration, which helps products whose data keeps changing.
- **Spread across machines:** Data can be copied and spread over many machines, so the system can keep serving requests when one machine fails.

The price is weaker transaction support. Many NoSQL databases give ACID promises only inside one piece of the data, so a change that spans many records may not be all-or-nothing.

> **Tip:** The label NoSQL tells you little about what a specific product promises. Read that product's own documentation on transactions before you rely on it for money or stock.

### An All-or-Nothing Change in Code

The short Python program below uses SQLite, a small SQL database that comes with Python. It moves money between two accounts. A CHECK rule says a balance can never go below zero. When a move breaks that rule, the database raises an error, and the code rolls back the change.

```python
import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE accounts (name TEXT PRIMARY KEY, balance INTEGER NOT NULL CHECK (balance >= 0))")
db.execute("INSERT INTO accounts VALUES ('alice', 100), ('bob', 20)")
db.commit()

def transfer(src, dst, amount):
    try:
        db.execute("UPDATE accounts SET balance = balance - ? WHERE name = ?", (amount, src))
        db.execute("UPDATE accounts SET balance = balance + ? WHERE name = ?", (amount, dst))
        db.commit()
        return "moved"
    except sqlite3.IntegrityError:
        db.rollback()
        return "rejected"

print(transfer("alice", "bob", 30))
print(transfer("bob", "alice", 500))
for row in db.execute("SELECT name, balance FROM accounts ORDER BY name"):
    print(row[0], row[1])

```

Output:

```text
moved
rejected
alice 70
bob 50

```

The first move works. Alice goes down to 70 and Bob goes up to 50, and the change is saved. The second move would take Bob below zero, so the database refuses it. The balances at the end show that nothing from the rejected move was kept.

## How to Choose in Practice

No database is best for every job. Work through these questions for each part of your system, not once for the whole app. Start with the data itself.

1. **Look at the data shape:** If every record has the same fields and the records link to each other, SQL is a natural fit. If records vary a lot or sit inside each other, a NoSQL model may fit better.
2. **List the main queries:** Write down the most common reads and writes. If one simple lookup dominates, a key-value or document store can serve it well. If many questions combine data from several sources, SQL is stronger.
3. **Name the consistency needs:** Ask what a mistake costs. Money, stock and medical records need strict rules, so they usually need ACID transactions.
4. **Estimate the scale:** Think about how many users and writes you expect now and in the near future. Scale matters most when one machine clearly cannot handle the load.
5. **Start with SQL:** Choose SQL as the default. Add a specialized NoSQL store only when a real, measured problem appears in a specific part of the system.

> **Tip:** Ask what a wrong answer costs. If a wrong number means lost money or a double sale, favor the database that protects correctness, even if it is harder to scale.

Real systems often use both. A retail system might keep orders and inventory in SQL, while it keeps activity logs or recommendation data in NoSQL. A microservice, which is a small service that does one job, can use one type or both, depending on its data.

## Worked Example: An Online Store

Take an online store. Shoppers browse products, put items in a cart, pay, and receive an order. These parts have different needs, so they can use different databases. This is an illustration of a common design, not a copy of any company's actual setup.

1. **Orders, payments and stock:** Keep these in SQL. A purchase must be all-or-nothing. The order, the payment record and the stock change are written in one transaction, so a crash never leaves a paid order with no stock change.
2. **The shopping cart:** Store each cart as a key-value or document record. The cart ID is the key, and the items sit inside as a list. Reads and writes are simple and frequent. Amazon built its Dynamo key-value store for shopping cart data, and it was designed to keep accepting writes during failures.
3. **Product views and events:** Page views, clicks and searches arrive as a huge, steady stream. Each event is small, is written fast, and is rarely joined to other data. A document or wide-column store handles this load well.
4. **Putting it together:** The store uses both types. Checkout stays in SQL because money and stock must be exact. The cart and the event stream sit in NoSQL because they need speed and scale more than joins. Each choice came from the same questions: shape, queries, consistency and scale.

## Common Mistakes to Avoid

- **Treating NoSQL as one thing:** A key-value store and a graph database solve different problems. Compare the exact product, not the label.
- **Assuming NoSQL always scales better:** Scaling depends on how the data is read, written and split. A well-run SQL database covers many real needs.
- **Skipping the data design:** A flexible schema still needs a consistent shape. Without one, queries can return unreliable results.
- **Choosing for fashion:** "It is more modern" is a weak reason. Pick the database from your data and your queries.
- **Expecting ACID everywhere:** Check what each database guarantees before you depend on it for money or stock.

## Check Your Understanding

Try these two short scenarios. Each one needs the ideas from this lesson.

**Quick check:** You are building a banking app that moves money between accounts. Separately, you need a feed that stores millions of fast-changing user activity events. Which database type fits each part?

A. NoSQL for the transfers and SQL for the activity feed
B. SQL for the transfers and NoSQL for the activity feed
C. SQL for both parts, because one database type should be used everywhere
D. NoSQL for both parts, because it always scales better

**Answer:** B. Transfers need all-or-nothing ACID changes, so money is never lost or counted twice. SQL gives that. The activity feed needs a very high write rate and flexible event shapes, which NoSQL handles well. The other two options assume one type must fit every part of the system.

**Quick check:** A small team is building a recipe website. Each recipe has a title, ingredients and steps. Users want to search recipes by ingredient, and the team wants to count how many recipes each author has written. What is a sensible first choice?

A. A key-value store, because every lookup uses one key
B. A relational database with a fixed schema and joins between authors and recipes
C. A graph database, because recipes are a kind of connection
D. A wide-column store, because it handles the largest write loads

**Answer:** B. Searching by ingredient and counting recipes per author are queries over linked data, which SQL handles well. SQL is also a sensible default when the team is small. A key-value store is built for lookups by one key, not for searches across many fields.

## Key Takeaways

- **Shape:** SQL stores data in tables with a fixed schema, links them with joins, and offers ACID transactions. NoSQL uses flexible shapes such as key-value, document, wide-column and graph.
- **Strengths:** SQL suits related data, exact numbers and complex questions. NoSQL suits huge volumes, simple fast lookups, changing data and spreading across many machines.
- **Choice:** Start from your data shape, your queries, your consistency needs and your expected scale. Treat SQL as a sensible default, and add other stores only where a real need appears.

## Sources

- [Microsoft Learn: Relational vs. NoSQL data (.NET architecture guide)](https://learn.microsoft.com/en-us/dotnet/architecture/cloud-native/relational-vs-nosql-data)
- [Weld: SQL vs NoSQL, differences and which database to use](https://weld.app/blog/sql-or-nosql-databases-which-one-is-best-for-storing-data-in-your-organisation)
- [DesignGurus: When to choose SQL versus NoSQL databases for a design problem](https://www.designgurus.io/answers/detail/when-to-choose-sql-versus-nosql-databases-for-a-design-problem)
- [DesignGurus blog: SQL vs. NoSQL key differences, use cases and which to choose](https://www.designgurus.io/blog/sql-vs-nosql-key-differences)
- [Zubair Khalid: Relational vs Non-Relational Database, differences and use cases](https://www.zubairkhalid.com/blog/data-analysis/relational-vs-non-relational-database)
- [Alekseia Leinikov: SQL vs NoSQL 2026, database types and how to choose](https://www.alekseialeinikov.com/en/blog/topics/data/sql-vs-nosql-2026-database-types-acid-base-how-to-choose)
- [Imarticus: SQL vs NoSQL, differences, examples and guide](https://imarticus.org/blog/sql-vs-nosql/)
- [PuppyGraph: SQL vs NoSQL, which is the best database](https://www.puppygraph.com/blog/sql-vs-nosql-databases)
- [Amazon's Dynamo (All Things Distributed, 2007)](https://www.allthingsdistributed.com/2007/10/amazons_dynamo.html)
- [Dynamo: Amazon's Highly Available Key-value Store (paper summary, ScaleDojo)](https://scaledojo.dev/papers/dynamo-amazons-highly-available-key-value-store)
- [Paper Review: Dynamo: Amazon's Highly Available Key-value Store (Sookocheff)](https://sookocheff.com/post/databases/dynamo/)
