Chapter 05 — SQL vs NoSQL (For Designers)

Shreyesh kolhe
July 10, 2026

Chapter 05 — SQL vs NoSQL (For Designers)

Hey everyone! Welcome to Season 2 — Data & Storage! 🙏

Every big system is really a data problem wearing a trench coat. And the first fork in the road is always: SQL or NoSQL? You may know the basics, but interviewers want the designer's answer — which one, for THIS system, and why? That's what we build today.

What we will cover:

  • SQL (relational) in one picture
  • NoSQL and its 4 flavors
  • Schema, joins, and ACID vs BASE
  • The real decision framework
  • Why "NoSQL scales better" is only half true
  • Polyglot persistence — using both
  • Interview Questions

1. SQL — The Organized Filing Cabinet

SQL databases (MySQL, PostgreSQL) store data in tables — rows and columns, like a spreadsheet with strict rules. Every row in a table has the same columns (a fixed schema).

USERS table                     ORDERS table
┌────┬────────┬─────────┐        ┌────┬─────────┬────────┐
│ id │ name   │ email   │        │ id │ user_id │ amount │
├────┼────────┼─────────┤        ├────┼─────────┼────────┤
│ 1  │ Aisha  │ a@x.com │◀──┐    │ 10 │   1     │  ₹500  │
│ 2  │ Rohit  │ r@x.com │   └────│ 11 │   1     │  ₹200  │
└────┴────────┴─────────┘        └────┴─────────┴────────┘
                          a JOIN links them via user_id

Superpowers: relationships via JOINs, a strict schema that guarantees clean data, and ACID transactions (money never vanishes mid-transfer).

2. NoSQL — The Flexible Toy Box

NoSQL means "not only SQL." It drops the rigid table structure for flexibility and easier horizontal scaling. There are 4 main flavors:

┌─────────────────────────────────────────────────────────────┐
│              THE 4 FLAVORS OF NoSQL                         │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  1. DOCUMENT   → JSON-like docs   (MongoDB)                 │
│     { name:"Aisha", orders:[500,200] }  ← nested, flexible  │
│                                                             │
│  2. KEY-VALUE  → a giant hashmap   (Redis, DynamoDB)        │
│     "user:1" → "{...}"             ← blazing fast lookups   │
│                                                             │
│  3. COLUMN     → wide columns      (Cassandra)              │
│     great for huge write-heavy time-series data            │
│                                                             │
│  4. GRAPH      → nodes + edges     (Neo4j)                  │
│     "who is friends with whom" — relationships as data     │
│                                                             │
└─────────────────────────────────────────────────────────────┘

Superpowers: flexible schema (add fields anytime), scales horizontally with ease, and is fast for simple lookups by key.

3. ACID vs BASE — The Core Philosophy Clash

ACID (usually SQL)BASE (usually NoSQL)
Motto"Be correct, always.""Be available, mostly."
ConsistencyStrong — data is always validEventual — catches up in a moment
Example needBank transfer 💸Instagram like count ❤️
TradeCorrectness over raw speed/scaleScale/availability over instant consistency
ACID = Atomicity, Consistency, Isolation, Durability
       → "the transaction fully happens, or not at all"

BASE = Basically Available, Soft state, Eventual consistency
       → "you'll see the update... in a second or two"

We'll go deep on eventual consistency in Chapter 09 (CAP) and Chapter 14. For now: ACID = trust; BASE = scale.

4. The Decision Framework

Don't memorize "SQL good / NoSQL good." Ask these questions:

CHOOSE SQL WHEN...                 CHOOSE NoSQL WHEN...
─────────────────                  ───────────────────
✔ Data is highly relational        ✔ Data is unstructured / varies
  (users↔orders↔products)            (flexible fields per record)
✔ You need ACID transactions       ✔ You need massive write scale
  (finance, inventory)               (feeds, logs, IoT, chat)
✔ Complex queries & JOINs          ✔ Simple lookups by key
✔ Schema is stable                 ✔ Schema evolves fast
✔ Consistency is critical          ✔ Availability > instant consistency

The "NoSQL scales better" myth

People say NoSQL "scales better." The honest truth: NoSQL was designed to scale horizontally out of the box, while scaling SQL horizontally (sharding) is harder. But modern SQL (with read replicas + sharding) scales enormously too. The real difference is ease, not a hard ceiling. Say that in an interview and you'll sound senior.

5. Polyglot Persistence — Use Both!

Real systems rarely pick just one. They use the right tool per job:

   [ User accounts, payments ]  → PostgreSQL (SQL, ACID)
   [ Product catalog ]          → MongoDB (flexible docs)
   [ Session / cache ]          → Redis (key-value, fast)
   [ Activity feed / logs ]     → Cassandra (write-heavy)
   [ "People you may know" ]    → Neo4j (graph)

   ONE company, MANY databases. This is polyglot persistence.

Interview Questions — Quick Fire!

Q: When would you choose SQL over NoSQL?

"I'd choose SQL when the data is highly relational and I need complex queries with joins, when a fixed schema and data integrity matter, and especially when I need ACID transactions — for example a banking or inventory system where correctness is non-negotiable. SQL guarantees the data is always consistent and valid."

Q: What are the types of NoSQL databases?

"Four main types: document stores like MongoDB that hold JSON-like documents; key-value stores like Redis or DynamoDB that act as a giant fast hashmap; wide-column stores like Cassandra, great for write-heavy time-series data; and graph databases like Neo4j for relationship-heavy data such as social connections."

Q: What's the difference between ACID and BASE?

"ACID — atomicity, consistency, isolation, durability — prioritizes correctness; a transaction either fully happens or not at all, and data is always valid. It's typical of SQL. BASE — basically available, soft state, eventual consistency — prioritizes availability and scale, accepting that data may be briefly stale before it converges. It's typical of NoSQL. ACID is about trust; BASE is about scale."

Q: Does NoSQL really scale better than SQL?

"It's more accurate to say NoSQL was designed for horizontal scaling from the start, so scaling out is easier. SQL can also scale massively with read replicas and sharding, but it takes more effort because of joins and strict consistency. So the difference is ease of scaling, not an absolute ceiling."

Key Points to Remember

ConceptKey Takeaway
SQLTables + schema + JOINs + ACID. Best for relational, transactional data.
NoSQLFlexible schema, easy horizontal scale. 4 flavors: document, key-value, column, graph.
ACID vs BASEACID = correctness (trust) · BASE = availability + eventual consistency (scale).
DecisionRelational + transactions → SQL. Flexible + huge write scale → NoSQL.
PolyglotReal systems use multiple databases, one per job.

What's Next?

Chapter 06 covers the single biggest performance trick in all of system design: caching. We'll see how a scoop of RAM in the right place can turn a 50ms database read into a 1ms one — and the famous problems (stale data, cache invalidation) that come with it.

Keep designing, keep scaling! See you in the next one!