Almost every system design discussion reaches the same question sooner or later: "Which database will you use, and why?" In earlier chapters we added load balancers and caches in front of our servers, but the database is where the data actually lives, and choosing the wrong kind of database can make a design slow, expensive or impossible to scale.
Databases are broadly grouped into two families: SQL (relational) databases like MySQL and PostgreSQL, and NoSQL databases like MongoDB, Cassandra, Redis and DynamoDB. This chapter explains how they differ, why both exist, and how to pick one in an interview with a clear justification.
There is no "best" database. There is only a database that fits your data shape, your access patterns and your consistency needs.
What Is a SQL (Relational) Database?
A relational database stores data in tables (relations) made of rows and columns, with a fixed schema, relationships expressed through keys, and a declarative query language, SQL.
Think of a set of linked spreadsheets with strict rules:
users orders
+---------+-------+------+ +----------+---------+--------+
| user_id | name | city | | order_id | user_id | amount |
+---------+-------+------+ +----------+---------+--------+
| 1 | Aarav | Pune |<---| 101 | 1 | 499 |
| 2 | Diya | Delhi| | 102 | 1 | 1299 |
+---------+-------+------+ | 103 | 2 | 250 |
+----------+---------+--------+
primary key <---- foreign key
Key properties:
Schema-on-write: the table structure is declared up front, and every row must match it.
Normalisation: each fact is stored once (a user's city lives only in
users), and tables are combined at query time with joins.ACID transactions: Atomicity, Consistency, Isolation, Durability. Transferring money between two accounts either fully happens or does not happen at all.
Constraints: primary keys, foreign keys,
NOT NULL,CHECKandUNIQUEare enforced by the database itself, not by your application code.Powerful ad-hoc queries: you can ask new questions (group, filter, join, aggregate) without redesigning the storage.
Popular examples: PostgreSQL, MySQL, Oracle Database, Microsoft SQL Server, SQLite.
What Is a NoSQL Database?
NoSQL ("Not only SQL") is an umbrella term for databases that do not use the relational table model as their primary data model. They typically offer flexible schemas and are designed to scale out across many machines.
NoSQL became popular in the late 2000s when companies like Google (Bigtable) and Amazon (Dynamo) needed to store enormous amounts of data across thousands of commodity servers with very high availability. Their papers inspired many open-source databases.
"NoSQL" is not one design. There are four main families:
| Type | Data model | Examples | Good for |
|---|---|---|---|
| Key-value | A giant dictionary: key → opaque value | Redis, Amazon DynamoDB, etcd | Sessions, caching, shopping carts, feature flags |
| Document | Self-describing JSON-like documents grouped in collections | MongoDB, Couchbase, Amazon DocumentDB, Firestore | Product catalogs, user profiles, content management |
| Wide-column | Rows grouped by a partition key, each holding many sorted columns | Apache Cassandra, HBase, ScyllaDB, Google Bigtable | Time-series, event logs, chat messages, heavy write workloads |
| Graph | Nodes and edges with properties | Neo4j, Amazon Neptune, JanusGraph | Social networks, recommendations, fraud rings, knowledge graphs |
DynamoDB is often described as a key-value and document store, because each item is addressed by a key but can hold nested attributes. The categories are a guide, not strict boxes.
A Worked Example: Same Data, Two Models
Let us store users and their orders in both styles. The script below uses Python's built-in sqlite3 module for the relational side, and a plain Python dictionary to show what a document database would store.
import sqlite3, json
# ---- Relational (SQL) version ----
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE users (user_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(user_id),
amount INTEGER NOT NULL CHECK (amount > 0));
INSERT INTO users VALUES (1,'Aarav','Pune'),(2,'Diya','Delhi');
INSERT INTO orders VALUES (101,1,499),(102,1,1299),(103,2,250);
""")
db.execute("PRAGMA foreign_keys = ON")
rows = db.execute("""
SELECT u.name, COUNT(o.order_id) AS orders, SUM(o.amount) AS total
FROM users u JOIN orders o ON o.user_id = u.user_id
GROUP BY u.user_id ORDER BY total DESC""").fetchall()
print("SQL result:", rows)
try:
db.execute("INSERT INTO orders VALUES (104, 99, 100)")
except sqlite3.IntegrityError as e:
print("SQL rejected bad row:", e)
# ---- Document (NoSQL-style) version ----
user_doc = {
"_id": 1, "name": "Aarav", "city": "Pune",
"orders": [{"order_id": 101, "amount": 499},
{"order_id": 102, "amount": 1299}],
}
total = sum(o["amount"] for o in user_doc["orders"])
print("Document read (one lookup):", user_doc["name"], len(user_doc["orders"]), total)
print(json.dumps(user_doc))
Output:
SQL result: [('Aarav', 2, 1798), ('Diya', 1, 250)]
SQL rejected bad row: FOREIGN KEY constraint failed
Document read (one lookup): Aarav 2 1798
{"_id": 1, "name": "Aarav", "city": "Pune", "orders": [{"order_id": 101, "amount": 499}, {"order_id": 102, "amount": 1299}]}
What this shows:
The SQL version stores users and orders separately and joins them when asked. The database itself refused an order for a user (id 99) who does not exist. (SQLite enforces foreign keys only after
PRAGMA foreign_keys = ON; PostgreSQL and MySQL/InnoDB enforce them by default.)The document version stores the user together with their orders. Reading "a user with their orders" is a single lookup with no join, which is fast and easy to spread across machines. But nothing stops you from writing an order with a negative amount, and if you ever need "all orders above ₹1,000 across all users", the embedded design makes that query harder.
Embedding vs Referencing
Document databases give you a choice:
Embed related data inside the parent document when it is read together and is bounded in size (a user's 3 saved addresses).
Reference it by id in another collection when it grows without limit or is shared (a user's 50,000 orders, or a product shared by many orders).
A useful rule: design NoSQL data around your queries; design SQL data around the entities.
Schema: Rigid vs Flexible
| Schema-on-write (SQL) | Schema-on-read (most NoSQL) | |
|---|---|---|
| When is structure checked? | When data is written | When application code reads it |
| Adding a field | ALTER TABLE (a migration) | Just start writing documents with the new field |
| Bad data | Rejected by the database | Accepted; the application must cope |
| Best when | Structure is stable and correctness matters | Structure varies (e.g., products with different attributes) or evolves quickly |
"Schemaless" is a slightly misleading word. The schema still exists; it has just moved from the database into your application code. Modern document databases also let you add validation rules (MongoDB supports JSON Schema validation on collections), so the line is softer than it used to be.
Scaling: Why NoSQL Became Popular
Recall Scalability: Vertical vs Horizontal. A traditional relational database usually runs as one primary server that handles all writes. You can scale it up (bigger machine) and add read replicas, but at some point the single write path or the dataset size becomes the bottleneck.
Typical SQL setup Typical NoSQL cluster
App servers App servers
| |
+---------+ +-----+-----+-----+-----+
| Primary | (all writes) | N1 | N2 | N3 | N4 | data split by key,
+---------+ +-----+-----+-----+-----+ each node owns a part
/ \ add N5 → more capacity
Replica Replica (reads)
Many NoSQL databases were built from day one to partition data by key across many nodes and to replicate each part, so adding machines adds capacity almost linearly. The trade-off: operations that touch many keys (joins, multi-row transactions, ad-hoc aggregations) become harder or are not offered at all.
Relational databases can be split across servers too, which we will see in Database Sharding & Partitioning, but it is usually done by an extra layer (such as Vitess for MySQL or Citus for PostgreSQL) rather than being the core design.
ACID vs BASE
Relational databases are known for ACID. Many early NoSQL systems described their guarantees as BASE:
Basically Available: the system keeps answering even when parts of it fail.
Soft state: replicas may temporarily hold different values.
Eventual consistency: if writes stop, all replicas eventually converge to the same value.
| ACID | BASE |
|---|---|
| Correctness first | Availability and scale first |
| Reads see committed data immediately | Reads may briefly see stale data |
| Bank transfers, orders, inventory, bookings | Likes, view counts, feeds, recommendations |
Today this is a spectrum, not two camps. MongoDB has supported multi-document ACID transactions since version 4.0 (replica sets) and 4.2 (sharded clusters); DynamoDB offers transactional APIs and strongly consistent reads; Cassandra lets each query pick its consistency level. We explore why these trade-offs exist in CAP Theorem & PACELC.
The Lines Are Blurring
SQL databases learned NoSQL tricks: PostgreSQL's
JSONBtype stores and indexes JSON documents; MySQL has aJSONtype too.NoSQL databases learned SQL tricks: transactions, secondary indexes, SQL-like query languages (Cassandra's CQL, Couchbase's SQL++).
NewSQL / distributed SQL databases such as Google Spanner, CockroachDB, TiDB and YugabyteDB offer SQL and ACID transactions while partitioning data across many nodes automatically.
Graph querying is getting a standard: GQL (ISO/IEC 39075) was published in 2024 as the first ISO standard graph query language, alongside widely used languages like Cypher.
So in an interview, avoid saying "NoSQL does not support transactions" or "SQL cannot scale". Both statements are outdated.
Side-by-Side Comparison
| Aspect | SQL (relational) | NoSQL |
|---|---|---|
| Data model | Tables, rows, columns | Key-value, document, wide-column, graph |
| Schema | Fixed, enforced on write | Flexible, usually enforced by the application |
| Relationships | Joins and foreign keys | Embedding, denormalisation, or application-side joins (graph DBs are the exception: relationships are first-class) |
| Query flexibility | Very high: ad-hoc SQL | Best for queries planned at design time |
| Transactions | Full ACID, multi-table | Varies; often single-key atomicity, some offer multi-item transactions |
| Scaling | Vertical + read replicas; sharding needs extra work | Horizontal partitioning is usually built in |
| Consistency | Strong by default | Often eventual by default, frequently tunable |
| Typical use | Payments, orders, ERP, anything with complex relationships | Huge write volumes, flexible data, simple key-based lookups at massive scale |
How to Choose in a Design Interview
Ask these questions in order:
Does the data need strong correctness across multiple records? (money, stock, seat bookings) → lean SQL.
Is the data highly relational, with many ad-hoc queries? (admin reports, analytics on business entities) → SQL.
Is access mostly "get/put by key" at very high scale? → key-value or wide-column.
Do records have varying structure and are they read as a whole? → document.
Is the main question about relationships several hops deep? ("friends of friends who like X") → graph.
What scale do the numbers from Back-of-the-Envelope Estimation suggest? A few thousand writes per second and a few TB usually fit comfortably in a well-tuned PostgreSQL or MySQL setup. Do not reach for a distributed NoSQL cluster before you need it.
Example Choices
| Component | Reasonable choice | Why |
|---|---|---|
| Payments ledger for a UPI-style app | Relational (PostgreSQL/MySQL) | ACID transactions, constraints, audits |
| User sessions / login tokens | Key-value (Redis) | Simple lookups, very fast, expiry (TTL) support |
| E-commerce product catalog | Document (MongoDB) | A phone and a T-shirt have different attributes |
| Chat messages / IoT sensor readings | Wide-column (Cassandra) | Enormous write volume, read by partition (chat id, device id) in time order |
| "People you may know" | Graph (Neo4j) | Multi-hop relationship traversal |
Polyglot Persistence
Large systems rarely use one database. An e-commerce site might keep orders in PostgreSQL, the catalog in MongoDB, sessions and carts in Redis and search in Elasticsearch. Using different stores for different jobs is called polyglot persistence. The cost is operational: more systems to run, monitor, back up and keep in sync.
Common Mistakes
"NoSQL is faster." It is faster for the access patterns it was designed for. A query it was not designed for (an ad-hoc join, a scan across partitions) can be far slower than in SQL.
"SQL cannot scale." Read replicas, caching, partitioning and distributed SQL databases run very large systems on relational databases.
Choosing NoSQL because "the schema might change". Schema migrations are a normal, solved problem in SQL. Choose based on access patterns and scale.
Modelling a document database like SQL (one collection per entity, joining in application code everywhere). You get the costs of both and the benefits of neither.
Embedding unbounded arrays (all orders inside the user document). Documents have size limits (16 MB in MongoDB) and big documents are slow to update.
Treating "NoSQL" as one thing. Redis and Neo4j have almost nothing in common. Name the specific type.
Interview Questions
1. What are the main differences between SQL and NoSQL databases?
SQL databases use tables with a fixed schema, joins and ACID transactions, and traditionally scale vertically plus read replicas. NoSQL databases use other models (key-value, document, wide-column, graph), have flexible schemas, are usually designed to partition across many nodes, and often favour availability with tunable or eventual consistency.
2. Which database would you use for a banking system and why?
A relational database, because transfers must be atomic across multiple rows, balances must never be read inconsistently, and constraints and auditability matter more than raw write throughput. If global scale is required, a distributed SQL database (such as Spanner or CockroachDB) keeps ACID semantics.
3. Name the four types of NoSQL databases with one example and use case each.
Key-value: Redis, sessions/caching. Document: MongoDB, product catalogs. Wide-column: Cassandra, time-series and chat messages. Graph: Neo4j, social connections and recommendations.
4. What does BASE stand for?
Basically Available, Soft state, Eventual consistency. It describes systems that stay available and let replicas temporarily diverge, converging later, in contrast to ACID's immediate consistency.
5. When would you embed vs reference data in a document database?
Embed when the child data is read with the parent, is owned by it, and is bounded in size. Reference when it grows without limit, is shared by many parents, or is frequently queried on its own.
6. Do NoSQL databases support transactions?
Many do today. MongoDB supports multi-document ACID transactions (4.0 for replica sets, 4.2 for sharded clusters), and DynamoDB has TransactWriteItems/TransactGetItems. Most offer at least single-key atomicity. Cross-partition transactions are usually more expensive than single-partition operations.
Summary
SQL databases: tables, fixed schema, joins, constraints and ACID transactions. Excellent for relational data and correctness-critical workloads.
NoSQL databases: key-value, document, wide-column and graph models, flexible schemas, built for horizontal scale.
Model SQL around entities, NoSQL around queries.
ACID vs BASE is now a spectrum: many databases offer tunable consistency and some transactions.
In interviews, justify your choice using data shape, access patterns, consistency needs and estimated scale, and do not be afraid to use more than one database.
Start with the questions your system must answer. The right database is the one that answers them correctly, at your scale, with the least complexity.