Lesson 3.1 · 15 min
SQL vs NoSQL
Model one shop as tables and as documents, run five real queries step by step, then shard it and count the nodes each query touches.
The idea in plain words
A SQL database is like a set of strict spreadsheets you can combine in any way you like. NoSQL databases each organize data around the questions you will ask most often, which makes those questions fast and others harder.
The problem it solves
Every system has to store data, and databases are designed for different jobs. Picking one means matching its strengths to how your app actually reads and writes data.
How it works
- Write down your most frequent reads and writes (the access patterns).
- Relational databases store data in tables and combine them with joins at query time, so new questions are easy and transactions keep related rows correct.
- NoSQL databases store data the way you will read it: by key, as a whole document, in time order, or as a graph.
- That makes the planned reads one fast lookup, and unplanned ones harder.
- Default to relational, and move a specific workload to NoSQL when its pattern clearly fits.
Example
A shop shows an order with its items. In a relational database that is a join across orders, items and products. In a document database the whole order is one document, read in one go, but if a product is renamed, every order document holding the old name needs updating or stays as it was.
Key terms
- SQL (relational)
- Data in tables with fixed columns, linked by keys and combined with joins.
- NoSQL
- A family of non-relational databases: key-value, document, wide-column and graph.
- Schema
- The agreed shape of the data: which fields exist and their types.
- Join
- Combining rows from two tables that share a key, at query time.
- Transaction
- A group of changes that all happen or none do.
- Access pattern
- The specific reads and writes your app does most often.
In this lesson you'll learn
- What relational, key-value, document, wide-column and graph databases are each good at.
- How the same queries cost differently on normalized tables vs embedded documents, and once data is sharded.
- How to pick a store from access patterns and invariants, and say the trade-off.
Sections
- Theory: the families
- Theory: same data, two models
- Design: in the interview
- Design: pick a database
- Check yourself
Cheat sheet
| Start from | access patterns + invariants |
|---|---|
| Embed | snapshots, hot aggregates |
| Reference | data that changes and is shared |
| Transactions | relational default; others: limits |
| Sharded | cross-node joins/writes cost round trips |
The interactive simulations and the quiz load with JavaScript.