8. SQL vs NoSQL
1
The picture to keep in your head2
8.1 What are tables, rows and keys?3
8.2 What does ACID mean?4
8.3 What is normalisation, and what are joins?5
8.4 What kinds of NoSQL databases are there?6
8.5 What's the difference between schema-on-write and schema-on-read?7
8.6 Why does NoSQL often duplicate data and avoid joins?8
8.7 How do you choose between SQL and NoSQL?9
Memory card and where the analogy breaks10
Practice: five levels11
Interview questions12
Solutions13
Sources14
Code lab8. SQL vs NoSQL
8.1 What are tables, rows and keys?
3 min read+10 XP
Relational databases
In the office: Each ledger has one subject (an authors ledger, a books ledger), ruled columns, and numbered lines. When the books ledger needs the author, it doesn't copy their details; it writes the author's line number.
In the system: A relational database stores data in tables.
- A table holds one kind of thing:
authors,books,orders. - A row is one item: one book. A column is one attribute with a type:
title TEXT,price INTEGER. - A primary key (PK) uniquely identifies each row:
books.id = 42. No two rows share it. - A foreign key (FK) is a column that points at another table's primary key:
books.author_id = 1means "see row 1 in authors". The database refuses a foreign key that points at nothing.
Figure: each fact lives in one place; keys link the tables together.
You ask questions in SQL (Structured Query Language), saying what you want and letting the database work out how:
sql
Examples: PostgreSQL, MySQL, SQLite, Oracle, SQL Server.
Memory code: "One table per thing, one row per item, keys for the links."