8. SQL vs NoSQL
The picture to keep in your head
Every question below is answered twice: first in the office, so you can picture it, then in the system, so you know exactly how it works. Each answer ends with a memory code to lock it in.
The picture to keep in your head
An old shop keeps its records two ways. The accountant keeps a ledger book: ruled pages, the same columns on every line, and when a line needs details from elsewhere it says "see page 12" instead of copying them. Nothing goes in that doesn't fit the columns, and at the end of the day everything must balance. Out back, there's a cabinet of folders: one folder per customer, and inside each folder, whatever that customer's story needs, all in one place. Quick to grab, easy to add odd papers to, but if a customer's address changes, you may have to fix it in several folders.
The ledger is a relational (SQL) database. The cabinet is a NoSQL store, specifically a document store; NoSQL has a few other shapes too (8.4).
Our running example: the bookstore's data: authors, books, users, orders and the items in each order.
| In the office | In the system |
|---|---|
| The ledger book | Relational (SQL) database |
| Printed columns on every page | Schema: columns and types, fixed in advance |
| "See page 12" | A foreign key: a link to a row in another table |
| Looking up page 12 while reading | A join |
| The accountant's rule that everything balances | Transactions and constraints (ACID) |
| A folder holding one customer's whole story | A document (nested JSON) |
| The same address written in many folders | Denormalised, duplicated data |
Figure: the office, mapped to the system. The ledger is strict and links everything; the cabinet keeps each story in one folder.