From Idea to Tables

For five chapters you have read, joined, and changed the Marquee's four tables without once asking where they came from. Somebody had to decide that films and screenings deserved separate tables, that the price lived on the screening, that bookings got a table of their own. This page shows the deciding. The method is humble and universal: find the nouns, give each one a table, and put every fact in exactly one home.

Think of an architect's floor plan before any furniture arrives: rooms first, then the doorways between them, and only then the sofas. Table design works the same way — tables are the rooms, pointers between them are the doorways, and the actual data moves in last. You would not buy a sofa for a house with no walls, and you should not write an INSERT for a schema with no plan. That is the whole analogy; from here on, the real terms.

Nouns First

Start from the business described in plain sentences, the way Lora would say it out loud: "The Marquee shows films on two screens. Customers book seats for specific screenings." Now underline the nouns. Film, screen, customer, seat, booking, screening. Every candidate table in the design is hiding in that list, and nothing that isn't in the list needs a table at all.

Not every noun survives. The test is simple: does the noun recur, and does it carry facts of its own? A film recurs (there are many) and carries facts (a title, a runtime, a rating), so it earns a table. A seat, at the Marquee, carries nothing of its own — "G7" is just a label written on a booking — so it stays a column. A screen is the same: the number 1 or 2 on a screening, not a thing with its own facts to keep.

Run the test across the list and four nouns survive: films, screenings, customers, bookings. That is not a coincidence dressed up as a method. The four tables you have been using since Chapter 2 are exactly the nouns of Lora's business that recur and carry facts of their own.

One Fact, One Home

With the tables sketched, every fact the business keeps must be assigned to exactly one of them. For each fact, ask: whose is it? The runtime belongs to the film — it does not change between Tuesday and Friday. The email belongs to the customer. The timestamp of a booking belongs to the booking. Most facts settle instantly.

Some resist, and those are the interesting ones. The ticket price feels like it belongs to the film, until you remember that matinees exist: the same film costs less at 2 p.m. than at 8 p.m. The fact that changes per showing must live on the showing — price belongs to the screening. The trick for finding a fact's home is to imagine it changing and watch what it changes with.

Getting a home wrong is not a style problem. A misplaced fact is tomorrow's contradiction: put the price on the film and the matinee discount has nowhere to live, so someone will store it twice, and the two copies will eventually disagree. The next page is entirely about that disease; this page is where you learn not to catch it.

Draw the Pointers

Now say the business sentences one more time and listen for which noun mentions which. "A booking is a customer attending a screening in a seat" — so bookings mention customers and screenings. "A screening shows a film" — so screenings mention films. Each mention becomes a foreign key, and the arrows fall out of the sentences without any cleverness on your part.

Direction still follows Chapter 2's rule: the pointer lives on the "many" side. One film has many screenings, so film_id sits in screenings and points at films. One customer has many bookings, so the booking carries customer_id. If you can read the arrows out loud — "many screenings point at one film" — the design is telling you its shape honestly.

Interrogate the Sketch with Questions

A design on paper is a hypothesis, and Chapter 1 already gave you the test kit: Lora's real questions. Replay them against the sketch. "What's on tonight?" — a path from screenings to films exists, so the question has a home. "How full is the 8 p.m. show?" — count bookings pointing at that screening; path exists. "Which film sold best this month?" — bookings to screenings to films; a longer walk, but every step follows an arrow.

A question with no path is the design telling you something is missing. If Lora asked "which snacks sell best?", the sketch has no answer — there is no snacks table and no pointer to one, so the design would have to grow before that question could be asked. That is the method working, not failing: better to discover the hole on paper than after 800 rows have moved in.

Only when the sketch survives the interrogation does SQL enter the room, and by then it is dictation:

CREATE TABLE films (
  film_id     INTEGER PRIMARY KEY,
  title       TEXT,
  runtime_min INTEGER,
  rating      TEXT
);

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  name        TEXT,
  email       TEXT UNIQUE,
  joined_on   DATE
);

CREATE TABLE screenings (
  screening_id INTEGER PRIMARY KEY,
  film_id      INTEGER REFERENCES films(film_id),
  screen       INTEGER,
  starts_at    TIMESTAMP,
  price        DECIMAL(6,2)
);

CREATE TABLE bookings (
  booking_id   INTEGER PRIMARY KEY,
  customer_id  INTEGER REFERENCES customers(customer_id),
  screening_id INTEGER REFERENCES screenings(screening_id),
  seat         TEXT,
  booked_at    TIMESTAMP,
  UNIQUE (screening_id, seat)
);

You have read every one of those statements before, but read them differently now: as decisions. Each table is a noun that passed the test, each column is a fact that found its one home, each REFERENCES is a sentence Lora actually says about her business. The schema was never scripture; it was an argument, and now you can make the argument yourself. Two pages from now, these same tables return with their full set of rules attached.