Lesson 20.1 gave you one table: a drawer that remembers one kind of thing. Real products remember several kinds of things that point at each other. This lesson teaches you to design those connections — and to start from the questions, not from the layout of the first page.

The giant-table instinct

Every beginner has this instinct, because every first screen suggests it. The research page shows the company name, ticker, sector, report title, report date, rating, source URL, source name, analyst name, and three tags — so the beginner builds one table with all eleven columns:

giant_table: company_name | ticker | sector | report_title
  | report_date | rating | source_url | analyst | tag1 | tag2 | tag3

It works for exactly one week. Then the failures arrive on schedule:

• The company renames a division; you update it in forty rows and miss three.
• Two reports share one source; you paste the URL twice and the copies diverge.
• A report needs a fourth tag; there is no tag4 column, so it goes into a notes field where no query can find it.
• "Show every company with no reports yet" is unanswerable — companies only exist as repeated text inside report rows.

The giant table confuses the *display* — one convenient screen — with the *reality* — several different things with different lifespans. Companies outlive reports. Sources support many claims. Tags apply across everything. Each deserves its own home.

Start from the questions

The fix is embarrassingly simple: before drawing any table, write the questions the product must answer. For the saved-research app:

1. Which reports belong to this company? 2. Which source supported this claim? 3. Which items did this user save? 4. Which reports carry this tag? 5. Which evidence in this report is stale?

Each question names a relationship — a line between two kinds of things. The tables follow the questions, never the reverse.

The three relationships, in plain language

Every connection between tables is one of three shapes. Learn them with companies, reports, sources, tags, and users:

One-to-many: one company, many reports. One row in companies connects to many rows in research_reports. Each report points at exactly one company. This is the most common shape in every application: one customer with many orders, one project with many tasks, one article with many comments.

One-to-one: one user, one profile. One row in users connects to at most one row in user_profiles. Used when a record has grown too wide or has different access rules — not as a default.

Many-to-many: reports and tags. One report carries many tags ("semiconductors", "margin", "Q4"); one tag labels many reports. Neither side can hold a single pointer to the other, so a small third table — a *join table* like report_tags — stores the pairings, one row per pairing. Users saving reports work the same way: one user saves many reports, one report is saved by many users, and a saved_items table records who saved what and when.

ONE-TO-MANY                  MANY-TO-MANY
companies 1 ──→ ∞ reports    reports ∞ ──→ report_tags ←── ∞ tags
users 1 ──→ ∞ reports            (each row: report_id + tag_id)
reports 1 ──→ ∞ evidence_items

A foreign key is a stored pointer

A foreign key is a field whose value is the ID of a row in another table. That is the whole definition. Report 101 stores company_id = 1, which means "this report belongs to company 1." An evidence item stores report_id = 102, which means "this claim appears in report 102."

Plain-language reading practice — say these aloud until they feel obvious:

• research_reports.company_id → companies.id reads: "each report belongs to one company."
• evidence_items.report_id → reports.id reads: "each evidence item supports one report."
• report_tags.report_id → reports.id plus report_tags.tag_id → tags.id reads: "each pairing links one report to one tag."

The database can enforce these pointers — refusing a report that names a nonexistent company — but even unenforced, the discipline matters: connect through IDs, never by pasting names.

Normalization: one home per fact

Normalization sounds academic. It means one thing: every fact lives in exactly one reliable home, and everything else points at it.

• The company's sector lives in companies, once. Reports point at the company; they do not retype the sector.
• The source's URL and publisher live in sources, once. Evidence items point at the source; they do not duplicate it.
• The user's display name lives in users, once. Saved items point at the user.

This is not a ritual of creating endless tables. Two tables are fine when two kinds of things exist. Five are right when five do. The test is duplication: if editing one real-world fact means editing many rows, that fact needs its own home.

Worked conversion: one spreadsheet becomes three tables

Here is the before-and-after beginners need to see once, concretely. A research log kept as a flat spreadsheet:

BEFORE: research_log.csv (one flat sheet)
report_title              | company_name    | ticker | report_date | rating | source_url              | tag
Apple Q4 filing notes     | Apple Inc.      | AAPL   | 2026-02-10  | hold   | https://www.sec.gov/…/0000320193 | semiconductors
Apple services follow-up  | Apple inc.      | AAPL   | 2026-03-01  | buy    | https://www.sec.gov/…/0000320193 | margin
Novo pipeline review      | Novo Nordisk    | NOVO-B | 2026-02-20  | watch  | https://…/annual-report-2025      | pharma
Apple margin re-check     | APPLE INC       | AAPL   | 2026-03-05  | buy    | https://www.sec.gov/…/0000320193 | margin

Count the duplication: "Apple" spelled three ways, the same SEC URL pasted three times, the tag margin typed twice with nothing stopping Margin or margins tomorrow. Rename the company, fix the URL, or add a fourth tag and you edit many cells and still miss one.

After — three tables, duplication replaced by ID pointers:

AFTER:
companies
id | ticker | name            | sector
1  | AAPL   | Apple Inc.      | Technology
2  | NOVO-B | Novo Nordisk A/S| Healthcare

research_reports
id  | company_id → companies.id | title                     | report_date | rating
101 | 1                         | Apple Q4 filing notes     | 2026-02-10  | hold
102 | 1                         | Apple services follow-up  | 2026-03-01  | buy
103 | 2                         | Novo pipeline review      | 2026-02-20  | watch
104 | 1                         | Apple margin re-check     | 2026-03-05  | buy

report_tags (join table: one row per pairing)
report_id → research_reports.id | tag_id → tags.id | answers
101 | 1 | "which reports carry 'semiconductors'?"
102 | 2 | "which reports carry 'margin'?"
104 | 2 | "which reports carry 'margin'?"

tags (one row per tag — the fact "margin exists" lives here once)
id | label
1  | semiconductors
2  | margin

The company name now appears once. The source URL (moved to a sources table in the full schema below) appears once. margin appears once in tags; reports link to it by ID. To answer "which reports belong to Apple?" follow every research_reports row with company_id = 1. No retyping, no spelling drift.

How to draw foreign keys on paper

Do not just define foreign keys — draw them so you can trace a question with your finger:

1. Draw one box per table. Write the table name on top, then list field: type, one per line. Star the id line. 2. Draw an arrow from the child pointer to the parent id: research_reports.company_id ──→ companies.id. Label the arrow with the question it answers: "which reports belong to this company?" 3. Read every arrow aloud as a sentence before you continue: "each report belongs to one company." If you cannot say it, the arrow is wrong. 4. Test with one real ID: put your finger on company 1, slide along the arrow, collect reports 101, 102, 104. If your finger cannot travel from question to answer, replace copied text with pointers until it can.

Hand this drawing to an agent as text lines — research_reports.company_id → companies.id // which reports belong to this company? — and instruct: "Create exactly these tables and foreign keys; do not add text-name copies." The agent writes the CREATE TABLE migration; you own the boxes, arrows, and questions.

The Sonariq mini-schema

Here is the running example grown to a realistic small size — five tables plus one join table. Read it as answers to the five questions above:

companies (id, ticker, name, sector, created_at)
  │ 1 ──→ ∞ filings, research_runs, reports
  │
filings (id, company_id → companies, filing_type, period_end,
         filed_at, source_url, retrieved_at)
research_runs (id, company_id → companies, started_at, status,
               model, owner_id → users)
evidence_items (id, report_id → reports, source_id → sources,
                quote, page_ref, retrieved_at)
reports (id, company_id → companies, run_id → research_runs,
         title, report_date, rating, status)

What is deliberately *not* duplicated: the company name appears once, in companies. The filing URL appears once, in filings. The source publisher appears once, in sources. Reports, runs, and evidence items carry IDs that point at those homes. Ask "which source supported this claim?" — follow evidence_items.source_id. Ask "which reports came from this run?" — filter reports by run_id. Ask "what did we know before March?" — every table carries its own date, so no question depends on another table's memory.

A practical note on working with a coding agent: the agent is excellent at writing the migration — the CREATE TABLE statements and constraints — once the design exists. It cannot decide what a row *means*. "Is a saved item one row per user-report pair, or one row per report with an array of users?" is a human product decision. Define the row and the relationship first, in sentences, then hand the agent a design it can implement.

Check your understanding

1. Why does the giant table break when a company renames itself? 2. Sketch the three relationship shapes from memory: one-to-one, one-to-many, many-to-many. 3. Read this aloud in plain language: evidence_items.source_id → sources.id. 4. A product needs "users save reports." Why is a saved_by text column on the report row the wrong design?

Exercise: split one spreadsheet

Find one real spreadsheet or product form — an event signup, an expense sheet, a content calendar, anything with repeated names in its rows. Then:

1. List the product questions it must answer (at least three: "which ___ belong to this ___?"). 2. Sketch three tables with names, fields, types, and IDs. 3. Draw the relationships as table.field → other_table.id lines, and write the question each line answers.

Finish line: a three-table sketch — on paper or in a file — where every relationship line carries its question in plain words.

Verify: pick one repeated value in the original sheet (a name, a URL, a tag). Confirm it now appears exactly once in your sketch, with everything else pointing at it. Then test each of your three questions: can you trace the pointers from question to answer without retyping anything?

Common failure mode: splitting into three tables but keeping the duplicated text in all of them "for convenience." Duplication is the disease the split was supposed to cure — replace the copies with ID pointers.

What comes next

You can now design records and their connections on paper — which is exactly the right order, because paper is cheap and migrations are not. Next you meet the machine that will run this design: PostgreSQL, the durable relational database underneath, and the two managed paths — Supabase and Neon — that let a beginner run it without operating infrastructure.