Part 4 of 6 · 2 min read
The running example for this roadmap
The small bookstore database used in worked examples through this subject.
Think of a single worked diagram that recurs through a textbook, growing one labelled detail at a time instead of being redrawn from scratch on every page. A reader who already knows the diagram can focus entirely on what is new.
In plain words: this subject uses one running example for exactly that reason: a small bookstore, with customers, books, authors and orders. Level 0 only needs two of its tables. Later levels add tables, indexes, and eventually a second server, but the business the tables describe never changes, so nothing has to be relearned from the start on every page.
A worked example. The bookstore, as it stands at the end of this level, needs two tables. These numbers are made up to show the idea, not measured from a real shop.
| Table | What one row is | How many rows (made up) |
|---|---|---|
customers |
One person who has ever placed an order | 400 |
orders |
One order, placed by one customer | 1,200 |
The key lines. The two tables, in the SQL that creates them — later parts of this module explain CREATE TABLE, primary keys and foreign keys in depth, so this is shown now only to be pointed back to, not to be understood line by line yet:
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
full_name TEXT NOT NULL,
address TEXT NOT NULL
);
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
placed_on DATE NOT NULL
);
Table, row, column
A table holds every record of one kind of thing, arranged as rows and columns. A row is one record — one customer, one order. A column is one named piece of information every row has, such as address or placed_on.
Example: the bookstore's customers table has one row per customer and a full_name column that every one of those rows fills in.
The line customer_id INTEGER NOT NULL REFERENCES customers(customer_id) inside orders is the entire fix for part 1's address problem: an order does not store a customer's address, or even their name — only a reference to exactly one row in customers. Part 2 of Level 3 names this kind of reference formally and explains what it enforces.