Model From Queries

Start from the questions the whole method

A schema is not a picture of your domain. It is a machine for answering a specific, finite list of questions — and until you have written that list down, you have nothing to design against.

What

A written list of the plain-English questions your API must answer, produced before any table exists.

Where

A file in your repo — docs/queries.md — committed alongside the schema it justifies.

When

At the very start of any data modelling work, and again every time someone asks for a feature that the current tables cannot serve.

How

Write 8-12 questions, write the SQL for each by hand, then create the smallest set of tables that makes all of that SQL run.

The failure mode this prevents is expensive and extremely common. A beginner opens a diagram tool, spends three hours producing something symmetrical and satisfying, then discovers on day four that question 7 — "which places have more than one person saved them?" — requires a table they did not draw. The diagram was never wrong in the abstract. It was answering questions nobody asked.

So here are the ten questions the City Guide API must answer. These are not examples. This is the actual specification for the schema you build in this module, and Modules 05 through 12 all run against it.

Q1.  All places in a given neighbourhood, newest first.
Q2.  Places in a neighbourhood tagged 'coffee' with a rating of 4 or better.
Q3.  The 5 most recently added places, each with its tags.
Q4.  Every list a given user owns, with a count of places in each.
Q5.  Every place in one named list, in the order it was added.
Q6.  Which tags are used most — top 10 by number of places.
Q7.  Places saved by more than one distinct user.
Q8.  Places with no tags at all.            # data quality, not a feature
Q9.  Average rating per neighbourhood, for neighbourhoods with 3+ places.
Q10. Find places whose name contains a fragment, case-insensitively.

Now write the SQL, still with no tables. This feels backwards and it is the entire trick. Writing the query forces you to name every column you need, and a column you never name in any of ten queries is a column you do not need. Here is Q2:

-- Q2: coffee places in Indiranagar, rating 4 or better
SELECT p.id, p.name, p.rating
FROM places p
JOIN place_tags pt ON pt.place_id = p.id
JOIN tags t        ON t.id        = pt.tag_id
WHERE p.neighbourhood = 'Indiranagar'
  AND t.name          = 'coffee'
  AND p.rating >= 4
ORDER BY p.rating DESC, p.name;

Read what that query just told you. There must be a places table with id, name, neighbourhood, rating. There must be a tags table with a name. And there must be something in between holding pairs — because a place has many tags and a tag has many places, and no single column can hold that. You did not decide any of this. The question decided it.

Q7 is the one that catches people out, so write it now while it is cheap:

-- Q7: places that more than one distinct user has saved
SELECT p.id, p.name, count(DISTINCT l.user_id) AS saver_count
FROM places p
JOIN list_places lp ON lp.place_id = p.id
JOIN lists       l  ON l.id        = lp.list_id
GROUP BY p.id, p.name
HAVING count(DISTINCT l.user_id) > 1
ORDER BY saver_count DESC, p.name;

Notice there is no direct link between a place and a user anywhere in that query. The connection runs place → list_places → lists → user_id, and the DISTINCT exists because one user saving the same place into two of their own lists must count once, not twice. If you had drawn the diagram first, you would very likely have hung "saves" straight off the user and lost the ability to answer Q5 at the same time.

• Ten questions is enough, and twenty is not better. The list must be short enough that you can hold all of it in your head while looking at a table definition. If you cannot, you will design for the first four and forget the rest.
• Write the questions in the language of the product, not the database. "Every list a user saved, with a count of places in each" is a question. "A LEFT JOIN between lists and list_places" is an answer wearing a question's clothes, and it smuggles in a design decision you have not made yet.
• The queries become your tests. In Module 09 these ten questions turn into ten test cases almost mechanically. A schema change that breaks one of them will fail loudly instead of silently returning fewer rows.
• A question you cannot express is a missing table, not a hard query. When SQL starts requiring string functions or subqueries three deep to answer something ordinary, that is the schema telling you it is shaped wrong.

From queries to tables

What

The CREATE TABLE statements derived mechanically from the ten queries — nothing more, nothing speculative.

Where

A single file, sql/schema.sql, that you can drop and re-run at will while learning.

When

Once the queries are written and you can point at the column each one needs.

How

Raw SQL in psql. Module 05 replaces this file with SQLAlchemy models and Alembic migrations — but you want to have written it by hand once first.

Six tables come out of those ten questions. Four are things (places, tags, users, lists) and two are relationships (place_tags, list_places).

-- sql/schema.sql
DROP TABLE IF EXISTS list_places, place_tags, lists, places, tags, users;

CREATE TABLE users (
    id           integer     GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email        text        NOT NULL UNIQUE,
    display_name text        NOT NULL,
    created_at   timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE places (
    id            integer     GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name          text        NOT NULL,
    neighbourhood text        NOT NULL,
    rating        integer     CHECK (rating BETWEEN 1 AND 5),   -- nullable: not yet rated
    address       text,                                          -- genuinely optional
    created_at    timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE tags (
    id   integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text    NOT NULL UNIQUE                                 -- Q6 counts these
);

CREATE TABLE lists (
    id         integer     GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id    integer     NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name       text        NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (user_id, name)          -- one user cannot have two lists called 'Brunch'
);

CREATE TABLE place_tags (
    place_id integer NOT NULL REFERENCES places(id) ON DELETE CASCADE,
    tag_id   integer NOT NULL REFERENCES tags(id),
    PRIMARY KEY (place_id, tag_id)
);

CREATE TABLE list_places (
    list_id  integer     NOT NULL REFERENCES lists(id)  ON DELETE CASCADE,
    place_id integer     NOT NULL REFERENCES places(id) ON DELETE RESTRICT,
    note     text,
    added_at timestamptz NOT NULL DEFAULT now(),        -- Q5 orders by this
    PRIMARY KEY (list_id, place_id)
);

Every constraint there is load-bearing, and you can name the question it serves. UNIQUE on tags.name is what makes Q6's counts meaningful — without it, "coffee" and "coffee" are two tags and the top-10 list is nonsense. added_at exists only because Q5 says "in the order it was added". CHECK (rating BETWEEN 1 AND 5) exists because a rating of 7 would quietly poison Q9's average forever, and no application-level validation survives the day someone loads data with a script.

There is deliberately no UNIQUE (name, neighbourhood) on places. It looks tidy and it is wrong: real cities contain two branches of the same chain on the same street. Uniqueness is a claim about the world, not a tidiness preference, and a false one turns into a 500 error the day a user tries to add the second branch.

Three type choices, decided once. Get these wrong and every later module inherits the mistake.

One-to-many, many-to-many, and the column that ruins everything

What

Two relationship shapes. One-to-many needs a foreign key column; many-to-many needs a whole table.

Where

Every schema you will ever read. Recognising which shape you are looking at is most of reading a schema.

When

The moment you can say "one X has many Y" out loud — then ask whether one Y also has many X.

How

Ask the sentence in both directions. If both directions say "many", you need a join table.

The composite primary key on the join table is doing three jobs at once. It makes the pair unique, so a place cannot be tagged "coffee" twice; it creates the index that makes Q2's join fast; and it removes the need for a meaningless id column on a table whose entire identity is the pair. list_places gets the same treatment, plus two real columns of its own — note and added_at — which is the giveaway that a join table is a genuine thing in your domain and not just plumbing.

Now the alternative every beginner reaches for, because it looks like less work:

-- DO NOT DO THIS
CREATE TABLE places (
    id   integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    tags text                      -- 'coffee,brunch,outdoor-seating'
);

-- Q2 now looks like this, and it is a lie:
SELECT * FROM places WHERE tags LIKE '%coffee%';
-- also matches 'decoffeinated', 'coffee-adjacent', 'not-coffee'

Count what that column costs you. You cannot index it usefully — every query becomes a full scan with a string operation per row. You cannot enforce that a tag exists, so cofee gets typed once and silently becomes a tag nobody can find. You cannot answer Q6 at all without splitting strings in SQL. Renaming a tag becomes a find-and-replace across every row, and one of them will have a comma in a place name. The join table is four lines of DDL. It is not the expensive option.

• The foreign key is a promise the database keeps for you. lists.user_id REFERENCES users(id) means a list pointing at a deleted user cannot exist — not "should not", cannot. Application code that forgets to check is now harmless.
• ON DELETE is a product decision, not a technical one. CASCADE on lists.user_id says "deleting a user deletes their lists", which is what you want. RESTRICT on list_places.place_id says "you may not delete a place someone has saved" — which is also what you want, and it is why the delete in the break step below fails.
• Read a join table's extra columns as domain facts. added_at on list_places answers "when did you save this?". If a join table sprouts four or five such columns, it has stopped being a relationship and become an entity that deserves its own name.
• Postgres does not index foreign key columns for you. The composite primary key covers place_tags lookups by place_id, but not by tag_id alone. Module 03's EXPLAIN ANALYZE is how you find out whether that matters — measure before adding the index.

Normalisation, only as far as it helps

What

A set of rules for eliminating duplicated facts, so that changing one fact means updating one row.

Where

Every relational schema, usually applied by instinct rather than by consciously checking the forms.

When

Continuously while designing — and then deliberately relaxed later, only where you have measured a reason.

How

One practical rule: store each fact exactly once. The formal forms are the theory behind that sentence.

The three forms, in four sentences. First normal form says each column holds one value, not a list — that is exactly why the comma-separated tags column above is wrong. Second normal form says every non-key column must depend on the whole primary key, which only bites on composite keys: putting place_name into place_tags would break it, because the name depends on place_id alone. Third normal form says non-key columns must not depend on each other — storing both neighbourhood and city on places would break it, since the neighbourhood already determines the city. That is the whole of it for practical purposes: no lists in a column, no fact that belongs to part of the key, no fact derivable from another column.

Everything else you have heard about normalisation is elaboration. Store each fact once, and you have passed.

Denormalisation is the deliberate opposite, and it is not a sin. If Q6 — the tag leaderboard — turned out to be slow on ten million rows, you might store a usage_count on tags and keep it updated. You would then own a new problem: the count can drift out of step with reality, and now you need a job that fixes it. That trade is worth making when you have measured the slowness. It is never worth making because you imagine it.

• "Store each fact once" tells you where to cut. A neighbourhood name repeated on 26 rows is not a duplicated fact in a harmful sense yet — it is duplicated text. It becomes a real problem the day a neighbourhood needs its own attributes, and that is the day you promote it to a table.
• Derived values are the most common accidental duplication. A place_count column on lists duplicates something count(*) already knows. The moment it exists, it can be wrong — and a number that can be wrong will be.
• Nullable means "genuinely optional", not "not filled in yet". places.rating is nullable because an unrated place is a real state. places.name is NOT NULL because a nameless place is not a place. Get this right and half your Module 06 error handling disappears.
• The schema is the last line of defence. Application validation is convenience; it runs when your code runs. Constraints run when anything writes — a migration, a psql session, a colleague's import script at 2am.

The diagram, last documentation

Now — and only now, with the DDL written and the queries proven against it — draw the picture. It records a decision that has already been made.

What

An entity-relationship diagram: boxes for tables, lines for foreign keys, crow's feet marking the "many" end.

Where

Your README, checked into the repo as text so it can be diffed and reviewed like code.

When

After the schema exists and runs. Re-drawn when it changes, which is cheap because it is thirty lines of ASCII.

How

One box per table with its columns, one line per foreign key, a crow's foot on the many side.

Read the two many-to-many shapes: place_tags has a crow's foot pointing at it from both sides, which is what many-to-many looks like on paper. lists has one on the users side only — one user, many lists. That is the whole notation you need to read almost any ER diagram you will meet.

Seeding data that is realistically messy

What

A script that fills the empty schema with enough rows — and enough awkward rows — to make queries meaningful.

Where

sql/seed.sql, run immediately after sql/schema.sql, re-runnable from scratch.

When

The moment the tables exist. A schema with no rows cannot be verified, and an unverified schema is a guess.

How

Multi-row INSERT, then reference rows by name rather than by guessed id.

Clean seed data hides exactly the bugs you most need to find. Twenty-six rows of Place 1 through Place 26, all rated 4, all with addresses, will make every query look like it works. Then real data arrives: a name with an apostrophe breaks your import, a name with an accent breaks a badly-configured client, a 74-character name breaks a layout, a null rating turns your average into null, and two genuinely different places with the same name make you realise your "duplicate detection" was matching on the wrong thing. Put all of that in the seed on purpose.

-- sql/seed.sql   (run after schema.sql)
INSERT INTO tags (name) VALUES
  ('bakery'),('bar'),('brewery'),('breakfast'),('brunch'),('cheap'),
  ('coffee'),('dessert'),('dinner'),('late-night'),('outdoor-seating'),('vegetarian');

INSERT INTO users (email, display_name) VALUES
  ('ana@example.com','Ana'),('ben@example.com','Ben'),
  ('chen@example.com','Chen'),('dee@example.com','Dee');

INSERT INTO places (name, neighbourhood, rating, address) VALUES
  ('Third Wave Coffee','Indiranagar',4,'12th Main'),
  ('Third Wave Coffee','Koramangala',3,'5th Block'),          -- same name, a different place
  ('Fatima''s Kitchen','Shivajinagar',5,NULL),                -- apostrophe, doubled to escape it
  ('Café Noir','Church Street',4,'Church Street'),            -- non-ASCII
  ('Bräu Haus Brewing Company','Whitefield',3,NULL),          -- non-ASCII
  ('The Original Late Night Shawarma and Grill House Since 1982','Frazer Town',5,NULL),
  ('Airlines Hotel','Cubbon Park',4,NULL),                    -- no address on record
  ('The Permit Room','Church Street',NULL,NULL),              -- not yet rated
  ('Smoke House Deli','Indiranagar',NULL,NULL),               -- not yet rated
  ('Blue Tokai Coffee Roasters','Indiranagar',4,'100 Feet Road'), ('Koshy''s','Church Street',4,'St Marks Road'),
  ('MTR 1924','Lalbagh',5,'Lalbagh Road'),           ('Rameshwaram Café','Indiranagar',5,NULL),
  ('Toit','Indiranagar',4,'100 Feet Road'),          ('Dyu Art Café','Koramangala',4,NULL),
  ('Matteo Coffea','Church Street',3,'Church St'),   ('Sunny''s','Vittal Mallya Road',4,NULL),
  ('Nagarjuna','Residency Road',4,NULL),             ('Truffles','Koramangala',3,NULL),
  ('Corner House','Residency Road',5,NULL),          ('Bombay Brasserie','Koramangala',3,NULL),
  ('Chinita Real Mexican','Indiranagar',4,NULL),     ('Byg Brewski Brewing Company','Hennur',4,'Sarjapur Rd'),
  ('Pizza 4P''s','Indiranagar',5,NULL),              ('Lavonne','Domlur',4,NULL),
  ('Glen''s Bakehouse','Indiranagar',4,NULL);

-- now(): constant within a statement, so all 26 rows share one created_at.
-- Spread them out so Q1 and Q3 have something real to order by.
UPDATE places SET created_at = now() - (interval '1 day' * (30 - id));

That doubled apostrophe is the whole quoting rule in SQL. A single quote ends a string literal, so a literal apostrophe is written as two: 'Koshy''s'. There is no backslash escape by default in Postgres. This is also the shape of the SQL injection problem you will meet in Module 06 — a user-supplied name containing a quote can end your string early — and the fix is never "escape it yourself", it is parameterised queries, which SQLAlchemy does for you.

Now the join tables. Do not guess ids — look them up by name, so the seed survives being re-run:

INSERT INTO place_tags (place_id, tag_id)
SELECT p.id, t.id
FROM (VALUES
  ('Blue Tokai Coffee Roasters','Indiranagar','coffee'),  ('Blue Tokai Coffee Roasters','Indiranagar','breakfast'),
  ('Third Wave Coffee','Indiranagar','coffee'),           ('Third Wave Coffee','Koramangala','coffee'),
  ('Café Noir','Church Street','coffee'),                 ('Café Noir','Church Street','dessert'),
  ('Matteo Coffea','Church Street','coffee'),             ('Rameshwaram Café','Indiranagar','cheap'),
  ('Rameshwaram Café','Indiranagar','breakfast'),         ('Fatima''s Kitchen','Shivajinagar','cheap'),
  ('Fatima''s Kitchen','Shivajinagar','late-night'),      ('Toit','Indiranagar','brewery'),
  ('Toit','Indiranagar','bar'),                           ('Bräu Haus Brewing Company','Whitefield','brewery'),
  ('Byg Brewski Brewing Company','Hennur','brewery'),     ('Byg Brewski Brewing Company','Hennur','outdoor-seating'),
  ('Corner House','Residency Road','dessert'),            ('Lavonne','Domlur','bakery'),
  ('Glen''s Bakehouse','Indiranagar','bakery'),           ('Koshy''s','Church Street','breakfast'),
  ('MTR 1924','Lalbagh','vegetarian'),                    ('Nagarjuna','Residency Road','dinner')
) AS v(pname, hood, tname)
JOIN places p ON p.name = v.pname AND p.neighbourhood = v.hood
JOIN tags   t ON t.name = v.tname;

INSERT INTO lists (user_id, name)
SELECT u.id, v.lname
FROM (VALUES
  ('ana@example.com','Coffee crawl'), ('ana@example.com','Weekend brunch'),
  ('ben@example.com','Cheap eats'),   ('ben@example.com','Date night'),
  ('chen@example.com','Working spots'),('dee@example.com','Show visitors around')
) AS v(email, lname)
JOIN users u ON u.email = v.email;

INSERT INTO list_places (list_id, place_id)
SELECT l.id, p.id
FROM (VALUES
  ('Coffee crawl','Blue Tokai Coffee Roasters','Indiranagar'),   ('Coffee crawl','Third Wave Coffee','Indiranagar'),
  ('Coffee crawl','Café Noir','Church Street'),                  ('Coffee crawl','Matteo Coffea','Church Street'),
  ('Weekend brunch','Koshy''s','Church Street'),                 ('Weekend brunch','Glen''s Bakehouse','Indiranagar'),
  ('Weekend brunch','Blue Tokai Coffee Roasters','Indiranagar'), ('Cheap eats','Fatima''s Kitchen','Shivajinagar'),
  ('Cheap eats','Rameshwaram Café','Indiranagar'),               ('Cheap eats','MTR 1924','Lalbagh'),
  ('Date night','Toit','Indiranagar'),                           ('Date night','Café Noir','Church Street'),
  ('Working spots','Blue Tokai Coffee Roasters','Indiranagar'),  ('Working spots','Third Wave Coffee','Koramangala'),
  ('Show visitors around','Toit','Indiranagar'),                 ('Show visitors around','MTR 1924','Lalbagh')
) AS v(lname, pname, hood)
JOIN lists  l ON l.name = v.lname
JOIN places p ON p.name = v.pname AND p.neighbourhood = v.hood;

• Note what the messiness buys you immediately. Two "Third Wave Coffee" rows mean the join tables must match on (name, neighbourhood), not name alone — a detail you would never have noticed with clean data, and one that would have silently double-inserted.
• Nulls in rating change your aggregates. avg(rating) ignores nulls, but count(*) does not — so Q9's "average per neighbourhood" and "places per neighbourhood" are counting different things. That is a real bug in real dashboards.
• Seed data belongs in the repo, not in your head. When Module 09 builds a throwaway test database, this same file fills it. A schema you can rebuild from zero in two commands is a schema you will experiment with fearlessly.
• Twenty-six rows is the right size. Enough that a wrong join produces visibly wrong counts, few enough that you can read the whole table and check by eye. Ten rows hides bugs; ten thousand hides them differently.

Build this

Verify it yourself

Open a prompt with docker compose exec db psql -U cityguide -d cityguide and run these. The row counts are exact — if yours differ, your seed did not fully load, and that is worth finding out now rather than in Module 06.

cityguide=# \dt
              List of relations
 Schema |    Name     | Type  |   Owner
--------+-------------+-------+-----------
 public | list_places | table | cityguide
 public | lists       | table | cityguide
 public | place_tags  | table | cityguide
 public | places      | table | cityguide
 public | tags        | table | cityguide
 public | users       | table | cityguide
(6 rows)                                    # six tables, no more, no fewer

cityguide=# \d place_tags
          Table "public.place_tags"
  Column  |  Type   | Nullable | Default
----------+---------+----------+---------
 place_id | integer | not null |
 tag_id   | integer | not null |
Indexes:
    "place_tags_pkey" PRIMARY KEY, btree (place_id, tag_id)
Foreign-key constraints:
    "place_tags_place_id_fkey" FOREIGN KEY (place_id) REFERENCES places(id) ON DELETE CASCADE
    "place_tags_tag_id_fkey" FOREIGN KEY (tag_id) REFERENCES tags(id)

cityguide=# SELECT
   (SELECT count(*) FROM places)      AS places,
   (SELECT count(*) FROM tags)        AS tags,
   (SELECT count(*) FROM users)       AS users,
   (SELECT count(*) FROM lists)       AS lists,
   (SELECT count(*) FROM place_tags)  AS place_tags,
   (SELECT count(*) FROM list_places) AS list_places;
 places | tags | users | lists | place_tags | list_places
--------+------+-------+-------+------------+-------------
     26 |   12 |     4 |     6 |         22 |          16

# --- the ten questions. Row counts must match exactly. ---
Q1  places in 'Indiranagar', newest first ................ 8 rows
Q2  Indiranagar + tag 'coffee' + rating >= 4 ............. 2 rows
Q3  5 most recently added places with tags ............... 5 rows
Q4  lists owned by ana@example.com, with counts .......... 2 rows (counts 4 and 3)
Q5  places in list 'Coffee crawl', by added_at ........... 4 rows
Q6  tags by usage, most first ........................... 11 rows (coffee = 5; 'brunch' absent)
Q7  places saved by more than one distinct user .......... 4 rows
Q8  places with no tags ................................ 10 rows
Q9  avg rating per neighbourhood, 3+ places .............. 3 rows
Q10 name ILIKE '%coffee%' ............................... 3 rows

# --- the three deliberate breaks ---
cityguide=# INSERT INTO places (name, neighbourhood, rating) VALUES ('Test','Nowhere',7);
ERROR:  new row for relation "places" violates check constraint "places_rating_check"
DETAIL:  Failing row contains (27, Test, Nowhere, 7, null, ...).

cityguide=# DELETE FROM tags WHERE name = 'coffee';
ERROR:  update or delete on table "tags" violates foreign key constraint
        "place_tags_tag_id_fkey" on table "place_tags"
DETAIL:  Key (id)=(7) is still referenced from table "place_tags".

cityguide=# INSERT INTO places (name, neighbourhood) VALUES ('Koshy's Annexe','Church Street');
cityguide'#                                 # NOT an error - psql is waiting for the string
                                            # to close. Press Ctrl+C, then double the quote:
                                            # 'Koshy''s Annexe'

Prove it

Write your answers in notes/module-04.md before you open any of these. The gap between your answer and the one here is the actual content of this module.

1. Which of the ten queries is hardest on this schema, and why?

2. Point at one foreign key doing real work. What breaks without it?

3. Why does a join table beat a comma-separated tags column, beyond "it is cleaner"?

4. A designer asks: "show me each place's opening hours and whether it is open now." What changes?

5. Why timestamptz and not timestamp, when your app runs on one machine?