Reading a Schema

Walk into any well-run building and somewhere near the door hangs a floor plan: rooms, doors, what connects to what. Databases hang one too. This lesson — the last of the unit — teaches you to read it, using everything the previous six taught you.

The schema

A database's schema is its complete structural description: which tables exist, which columns each has (with their types), which column is each table's primary key, and which foreign keys point where. Not the facts themselves — the shape the facts live in. Rows come and go all day; the schema is what stays, and designing it well is most of what database design means.

The diagram

Schemas are usually drawn as boxes and lines — one box per table, one line per relationship. Here is the floor plan of an actual town library's database, three tables and two lines:

+-------------+       +--------------------+       +------------+
| members     |       | loans              |       | books      |
+-------------+       +--------------------+       +------------+
| id          |<--+   | id                 |   +-->| id         |
| name        |   +---| member_id          |   |   | title      |
| joined      |       | book_id            |---+   | author     |
+-------------+       | due                |       | shelf      |
                      +--------------------+       +------------+

Reading a diagram is a skill of about ninety seconds. Let's spend them.

Boxes first. Three drawers: members, books, loans. Two are familiar kinds of thing. The third is the interesting one — a loan is not a person or an object, it's an event: this member borrowed that book, due on this date. Events deserve drawers too. Beginners often try to squeeze borrowing into the books table (a borrowed_by column) and find it can't remember history — every new loan overwrites the last. An event table remembers every loan ever made.

Then lines. Each line is a foreign key doing exactly what last lesson's did. loans.member_id points at members.id: who borrowed. loans.book_id points at books.id: what they borrowed. A loan card is mostly pointers, and that's its charm — it copies nothing, so it can contradict nothing.

Then a question. The test of any schema is whether real questions have a home. "What has Sara borrowed?" Find Sara in members, note her id, collect the loans pointing at it, follow each loan's book_id into books. Every hop follows a line on the diagram. If a question your building needs has no path of lines to answer it, the floor plan — not the facts — is what's missing.

What you can now see

You read that diagram the way a librarian reads their own building: drawers for each kind of thing (and each kind of event), typed columns, primary keys to name every card exactly once, foreign keys so facts are pointed at instead of copied. That is the entire structure of Unit 1, in one picture. Schemas with fifty tables are this picture, repeated.

But notice what you did for Sara's books: you hopped the lines, drawer to drawer, by hand. In a real library you wouldn't walk the shelves — you'd ask the librarian. Unit 2 introduces the librarian: the query, and the language the whole world uses to phrase one.