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.
11 min readFree
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.
Key words
- 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.
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
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?
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?
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)
- Weld: SQL vs NoSQL, differences and which database to use
- DesignGurus: When to choose SQL versus NoSQL databases for a design problem
- DesignGurus blog: SQL vs. NoSQL key differences, use cases and which to choose
- Zubair Khalid: Relational vs Non-Relational Database, differences and use cases
- Alekseia Leinikov: SQL vs NoSQL 2026, database types and how to choose
- Imarticus: SQL vs NoSQL, differences, examples and guide
- PuppyGraph: SQL vs NoSQL, which is the best database
- Amazon's Dynamo (All Things Distributed, 2007)
- Dynamo: Amazon's Highly Available Key-value Store (paper summary, ScaleDojo)
- Paper Review: Dynamo: Amazon's Highly Available Key-value Store (Sookocheff)
Last updated on 17 November 2026
Related lessons
Learn with me
Every lesson is free. I add new ones as I learn. Have a question, or want help with your own work? Book a short call.