Tables, keys and the schema

Before the first query comes a question every analyst asks: what is in this database, and how do its tables fit together?

Tables, rows and columns

A relational database is a set of tables. Each table describes one kind of thing, and each row is one of those things. Each column has a name and holds one kind of value: a number, a piece of text, a date written as text. In the bakery database used across this handbook (described on its own page), customer holds people, product holds what is on sale, staff holds the people behind the counter, orders holds visits to the counter, order_item holds the lines of each order, and refund holds money given back.

Notice what is not there. An order does not repeat the customer's name, and an order line does not repeat the product's name or category. Each fact is stored once, in the table it belongs to, and the other tables point to it. That is what makes the data consistent (rename a product in one place and it is renamed everywhere) and what makes joins necessary.

An order line does carry a price, unit_price, but it is a different fact: the price paid that day. product.price is today's price, and three products cost more from Monday 16 March, so the lines sold before that date keep the old price. Before you decide two columns say the same thing, ask what each one records.

Primary keys

A primary key is a column whose value identifies one row and only one. In the bakery, every table but order_item has an id column for that purpose. An id is a label, not a measure: id 7 is not "later" or "bigger" than id 6, even if it was probably inserted later. Never read meaning into an id that the data does not promise.

Foreign keys and relationships

A foreign key is a column that holds the primary key of another table: orders.customer_id holds a customer.id. It expresses a relationship, and most relationships are one-to-many: one customer, many orders; one order, many lines. Schema diagrams draw this as a line between the two tables, often marked 1 at the parent end and N (or a crow's foot) at the child end. When you read a diagram, follow the lines: they are the joins you will write.

A foreign key may point back into its own table. customer.referred_by holds the id of another customer, and staff.manager_id the id of another member of staff, which is how the handbook's self-join and recursive examples work.

Exploring an unknown database

SQLite describes itself in a built-in table called sqlite_master. Querying it lists every table, together with the statement that created it.

SELECT name
FROM sqlite_master
WHERE type = 'table'
ORDER BY name;

For the columns of one table, PRAGMA table_info gives their names, types, and whether each is part of the primary key. The table-valued form below can be used in a normal SELECT, so you can choose the columns you want to see.

SELECT name, type, pk
FROM pragma_table_info('orders')
ORDER BY cid;

And pragma_foreign_key_list lists the foreign keys of a table: which column points to which table and column.

SELECT "from", "table", "to"
FROM pragma_foreign_key_list('order_item')
ORDER BY "from";

Looking at the data itself

A schema tells you what can be stored, not what is stored. The next step is always to look at a few rows and count them. LIMIT 5 shows the shape of the values (is a date written 2026-03-02 or 02/03/2026? are times 07:15 or 715?), and a COUNT per column shows where values are missing.

SELECT COUNT(*) AS customers,
       COUNT(referred_by) AS with_referrer,
       COUNT(email) AS with_email,
       COUNT(DISTINCT town) AS towns
FROM customer;

Seven customers came in without a referrer and ten left no email. Five minutes spent this way save an hour later. Knowing that a column can be NULL tells you to use IS NULL; knowing that times are text with a leading zero tells you that you can compare them as text; knowing how many rows a table holds tells you whether a result of fifty rows is a lot or a little.

NULL is not a value

A missing value is stored as NULL. It is not zero and not the empty string: it means "unknown". Any comparison with NULL is itself unknown, so WHERE referred_by = 3 silently skips the rows where referred_by is NULL, and so does WHERE referred_by <> 3. Aggregates other than COUNT(*) ignore NULLs. Keep this in mind whenever a count looks too small.

Common mistakes

Starting to write a long query before looking at the tables. Treating an id as a date or a ranking. Joining two tables on columns that merely share a name rather than on an actual foreign key. Assuming a column is never empty. Assuming every value is spelled the same way: a quick SELECT DISTINCT on a text column shows whether "Old Town" is also written "old town" somewhere. The bakery's towns are clean, but its emails are not: a few are in capitals or carry a stray space (see LIKE).

In the game: the schema beside the terminal grows as chapters reveal new tables, with keys and relationship lines drawn as described here; every chapter starts by reading it.