Database Design

Writing SQL queries is one skill. Designing the tables they query is another. A well-designed database is easy to query, resistant to data anomalies, and straightforward to maintain. A poorly designed one leads to duplicated data, inconsistent records, and queries that feel like solving a puzzle. In this lesson, you'll learn the principles that separate good designs from bad ones.

What You'll Learn

By the end of this lesson you will take one wide table where every fact is repeated on every row, split it into tables where each fact is stored exactly once, and write the CREATE TABLE statements that keep it that way — with the keys and constraints that stop bad rows from ever being written. The second half is the part that surprises people: a constraint you declared is not necessarily a constraint the database is checking, and this lesson shows you how to tell the difference on the environment you are actually running in.

This is the milestone the whole warehouse capstone sits on. The capstone's customers, products, orders and line items are four tables rather than one spreadsheet, and every later report — the category rollups, the leaderboard, the monthly trend — is only writable because the schema separated those four things first. You arrive here able to join tables back together and read the combined rows (Joins and Relationships), and to write and update rows (Inserting and Updating); what is new is deciding which tables should exist before anything is inserted at all.

The Problem with Bad Design

Let's start by looking at what happens when you put everything in one table.

-- A poorly designed "flat" table
CREATE TABLE orders_flat (
    order_id INTEGER PRIMARY KEY,
    customer_name TEXT,
    customer_email TEXT,
    customer_city TEXT,
    product_name TEXT,
    product_price REAL,
    product_category TEXT,
    quantity INTEGER,
    order_date TEXT
);

INSERT INTO orders_flat VALUES
    (1, 'Alice Johnson', 'alice@example.com', 'New York', 'Laptop', 999.99, 'Electronics', 1, '2025-01-10'),
    (2, 'Alice Johnson', 'alice@example.com', 'New York', 'Mouse', 24.99, 'Electronics', 2, '2025-01-10'),
    (3, 'Bob Smith', 'bob@example.com', 'London', 'Laptop', 999.99, 'Electronics', 1, '2025-01-12'),
    (4, 'Alice Johnson', 'alice@new-email.com', 'New York', 'Keyboard', 79.99, 'Electronics', 1, '2025-01-15');

-- Notice the problems:
-- 1. Alice Johnson carries two different email addresses across her three orders
-- 2. Product info is duplicated (Laptop appears twice)
-- 3. Updating Alice's email requires finding ALL her rows
SELECT * FROM orders_flat ORDER BY order_id;

This table has three classic problems:

Update anomaly — Alice's email changed in one row but not others.
Insertion anomaly — You can't add a new product without creating a fake order.
Deletion anomaly — Deleting Bob's order loses his customer information entirely.

Normalization: First Normal Form (1NF)

A table is in 1NF when:

Each column contains atomic (indivisible) values
Each row is unique
There are no repeating groups

-- BAD: violates 1NF with comma-separated values
CREATE TABLE contacts_bad (
    id INTEGER PRIMARY KEY,
    name TEXT,
    phone_numbers TEXT -- "555-0100, 555-0101, 555-0102"
);

INSERT INTO contacts_bad VALUES
    (1, 'Alice', '555-0100, 555-0101'),
    (2, 'Bob', '555-0200');

-- Searching for a specific number is painful:
SELECT * FROM contacts_bad WHERE phone_numbers LIKE '%555-0101%';

-- GOOD: 1NF with a separate table for phone numbers
CREATE TABLE contacts (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE phone_numbers (
    id INTEGER PRIMARY KEY,
    contact_id INTEGER NOT NULL,
    phone TEXT NOT NULL,
    type TEXT DEFAULT 'mobile'
);

INSERT INTO contacts VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO phone_numbers VALUES
    (1, 1, '555-0100', 'mobile'),
    (2, 1, '555-0101', 'work'),
    (3, 2, '555-0200', 'mobile');

-- Now searching is clean and precise
SELECT c.name, p.phone, p.type
FROM contacts c
JOIN phone_numbers p ON c.id = p.contact_id
WHERE p.phone = '555-0101';

Second Normal Form (2NF) and Third Normal Form (3NF)

2NF removes partial dependencies — every non-key column must depend on the entire primary key. 3NF removes transitive dependencies — non-key columns shouldn't depend on other non-key columns.

-- Before normalization: one big table
CREATE TABLE order_details_bad (
    order_id INTEGER,
    product_id INTEGER,
    product_name TEXT,        -- depends only on product_id (partial dependency)
    product_category TEXT,    -- depends only on product_id
    customer_name TEXT,       -- depends only on order_id (partial dependency)
    quantity INTEGER,
    PRIMARY KEY (order_id, product_id)
);

-- After normalization: properly separated tables

-- Customers table
CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    city TEXT
);

-- Products table
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    category TEXT,
    price REAL NOT NULL
);

-- Orders table (references customers)
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date TEXT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- Order items (references orders and products)
CREATE TABLE order_items (
    id INTEGER PRIMARY KEY,
    order_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL DEFAULT 1,
    unit_price REAL NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Insert normalized data
INSERT INTO customers VALUES (1, 'Alice Johnson', 'alice@example.com', 'New York');
INSERT INTO customers VALUES (2, 'Bob Smith', 'bob@example.com', 'London');

INSERT INTO products VALUES (1, 'Laptop', 'Electronics', 999.99);
INSERT INTO products VALUES (2, 'Mouse', 'Electronics', 24.99);
INSERT INTO products VALUES (3, 'Keyboard', 'Electronics', 79.99);

INSERT INTO orders VALUES (1, 1, '2025-01-10');
INSERT INTO orders VALUES (2, 2, '2025-01-12');

INSERT INTO order_items VALUES (1, 1, 1, 1, 999.99);
INSERT INTO order_items VALUES (2, 1, 2, 2, 24.99);
INSERT INTO order_items VALUES (3, 2, 1, 1, 999.99);

-- Query the normalized data
SELECT
    c.name AS customer,
    o.order_date,
    p.name AS product,
    oi.quantity,
    oi.unit_price,
    oi.quantity * oi.unit_price AS line_total
FROM order_items oi
JOIN orders o ON oi.order_id = o.id
JOIN customers c ON o.customer_id = c.id
JOIN products p ON oi.product_id = p.id
ORDER BY o.id, oi.id;

Now each fact is stored exactly once. Updating Alice's email means changing one row in the customers table.

Primary Keys and Foreign Keys

Primary keys uniquely identify each row. Foreign keys describe relationships between tables — and, when the database is checking them, enforce referential integrity.

That qualifier is doing real work, and it is the single most important sentence in this lesson. In SQLite a REFERENCES clause is parsed and stored whether or not anything enforces it, and enforcement is controlled by a connection setting called PRAGMA foreign_keys. In this environment that pragma starts at 0 — off — on every single Run, so a REFERENCES clause you did not switch enforcement on for is documentation rather than a rule. Notice that the fence below opens with PRAGMA foreign_keys = ON; for exactly that reason. Every fence in this lesson that depends on a foreign key being checked carries that line first, and yours will have to as well.

-- Enable foreign key enforcement in SQLite
PRAGMA foreign_keys = ON;

-- Create tables with proper key relationships
CREATE TABLE authors (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    bio TEXT
);

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    author_id INTEGER NOT NULL,
    published_year INTEGER,
    FOREIGN KEY (author_id) REFERENCES authors(id)
);

CREATE TABLE reviews (
    id INTEGER PRIMARY KEY,
    book_id INTEGER NOT NULL,
    rating INTEGER CHECK(rating BETWEEN 1 AND 5),
    comment TEXT,
    FOREIGN KEY (book_id) REFERENCES books(id)
);

-- Insert valid data
INSERT INTO authors VALUES (1, 'Jane Austen', 'English novelist');
INSERT INTO authors VALUES (2, 'George Orwell', 'English essayist and novelist');

INSERT INTO books VALUES (1, 'Pride and Prejudice', 1, 1813);
INSERT INTO books VALUES (2, '1984', 2, 1949);
INSERT INTO books VALUES (3, 'Animal Farm', 2, 1945);

INSERT INTO reviews VALUES (1, 1, 5, 'A timeless classic');
INSERT INTO reviews VALUES (2, 2, 5, 'Terrifyingly relevant');
INSERT INTO reviews VALUES (3, 2, 4, 'Must read');

-- Query across relationships
SELECT
    b.title,
    a.name AS author,
    COUNT(r.id) AS review_count,
    ROUND(AVG(r.rating), 1) AS avg_rating
FROM books b
JOIN authors a ON b.author_id = a.id
LEFT JOIN reviews r ON b.id = r.book_id
GROUP BY b.id, b.title, a.name
ORDER BY avg_rating DESC, b.title;

With the pragma on, the rule is real. Here is the same schema being asked to store a book whose author does not exist:

PRAGMA foreign_keys = ON;
CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    author_id INTEGER NOT NULL,
    FOREIGN KEY (author_id) REFERENCES authors(id)
);
INSERT INTO authors VALUES (1, 'Jane Austen');
INSERT INTO books VALUES (1, 'Pride and Prejudice', 1);
INSERT INTO books VALUES (2, 'Ghostwritten', 99);

There is no author 99, so the database refuses the row and the script stops with a non-zero exit:

Error: FOREIGN KEY constraint failed

Predict

The script below is the same schema and the same orphaned row, with one difference: the PRAGMA line is gone. The films table still declares studio_id as REFERENCES studios(id), and there is no studio 7. What happens when this runs in this environment?

CREATE TABLE studios (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE films (id INTEGER PRIMARY KEY, title TEXT NOT NULL, studio_id INTEGER NOT NULL REFERENCES studios(id));
INSERT INTO studios VALUES (1,'Aurora'),(2,'Borealis');
INSERT INTO films VALUES (12,'Orphan',7);
SELECT COUNT(*) AS orphaned FROM films f LEFT JOIN studios s ON s.id = f.studio_id WHERE s.id IS NULL;

It fails on the last INSERT with 'Error: FOREIGN KEY constraint failed', the same as the demo above.
It runs clean, exits 0, and the count of orphaned films comes back as 1.
The CREATE TABLE fails, because a REFERENCES clause is invalid unless foreign keys are enabled first.

Constraints for Data Integrity

Constraints are rules enforced by the database to keep data valid.

-- Create a table with various constraints
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    department TEXT NOT NULL DEFAULT 'Unassigned',
    salary INTEGER CHECK(salary > 0),
    hire_date TEXT NOT NULL,
    manager_id INTEGER,
    FOREIGN KEY (manager_id) REFERENCES employees(id)
);

-- Valid inserts
INSERT INTO employees (id, email, name, department, salary, hire_date)
VALUES (1, 'sarah@company.com', 'Sarah CEO', 'Executive', 150000, '2015-01-01');

INSERT INTO employees (id, email, name, department, salary, hire_date, manager_id)
VALUES (2, 'tom@company.com', 'Tom Engineer', 'Engineering', 95000, '2020-03-15', 1);

INSERT INTO employees (id, email, name, department, salary, hire_date, manager_id)
VALUES (3, 'lisa@company.com', 'Lisa Designer', 'Design', 85000, '2021-06-01', 1);

SELECT id, email, name, department, salary, manager_id
FROM employees
ORDER BY id;

Note that this fence does not switch foreign keys on, and that is deliberate: nothing here depends on the manager_id reference being checked. The three constraints it does depend on — UNIQUE, NOT NULL and CHECK — need no pragma. They are enforced on every connection, always, and the next three demos prove it by breaking each one in turn.

A duplicate email, against a column declared UNIQUE:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    salary INTEGER CHECK(salary > 0)
);
INSERT INTO employees VALUES (1, 'tom@company.com', 'Tom Engineer', 95000);
INSERT INTO employees VALUES (2, 'tom@company.com', 'Tom Again', 88000);

Error: UNIQUE constraint failed: employees.email

A missing name, against a column declared NOT NULL:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    salary INTEGER CHECK(salary > 0)
);
INSERT INTO employees VALUES (1, 'tom@company.com', 'Tom Engineer', 95000);
INSERT INTO employees (id, email, name) VALUES (2, 'lisa@company.com', NULL);

Error: NOT NULL constraint failed: employees.name

And a negative salary, against the CHECK:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    salary INTEGER CHECK(salary > 0)
);
INSERT INTO employees VALUES (1, 'tom@company.com', 'Tom Engineer', 95000);
INSERT INTO employees VALUES (2, 'lisa@company.com', 'Lisa Designer', -1000);

Error: CHECK constraint failed: salary > 0

Read the error text itself, not just the fact that there was one: each message names the constraint that stopped you and, for the first two, the exact table and column. That is the fastest way to find which of a dozen columns rejected a row.

Key constraint types:

NOT NULL — Column must have a value. Enforced always.
UNIQUE — No duplicate values allowed. Enforced always.
CHECK — Value must satisfy a condition. Enforced always.
DEFAULT — Provides a fallback value when the column is left out of an INSERT.
FOREIGN KEY — Declares that a value must match a row in another table. Enforced only when PRAGMA foreign_keys = ON; has been run on this connection, which in this environment it has not been unless you wrote it yourself.
PRIMARY KEY — Identifies the row. It implies UNIQUE always, but it implies NOT NULL only for a single INTEGER PRIMARY KEY column. The section below shows what that exception costs.

Where PRIMARY KEY stops meaning NOT NULL

The last bullet is a real trap and it is worth three lines of code to see. SQLite documents that for historical compatibility a PRIMARY KEY column that is not a single INTEGER PRIMARY KEY can hold NULL, and since NULL is never equal to NULL, the uniqueness check does not stop a second one going in either.

CREATE TABLE badges (code TEXT PRIMARY KEY, holder TEXT);
INSERT INTO badges VALUES (NULL, 'Ada');
INSERT INTO badges VALUES (NULL, 'Bo');
SELECT COUNT(*) AS rows_with_no_badge_code FROM badges WHERE code IS NULL;

Two rows, both with no primary key value, in a table whose primary key was supposed to identify them. The fix is one word — declare the column TEXT PRIMARY KEY NOT NULL — and it is one word you have to write yourself, on every non-integer primary key and on every column of a composite one.

Predict

The section above told you which primary key does imply NOT NULL: a single INTEGER PRIMARY KEY. The script below sends a NULL straight at one — seat_no on the seats table — and sends another NULL at locker_no, an INTEGER column that is one half of a composite key. What does the script print?

CREATE TABLE seats (seat_no INTEGER PRIMARY KEY, occupant TEXT NOT NULL);
CREATE TABLE lockers (
  block TEXT,
  locker_no INTEGER,
  holder TEXT NOT NULL,
  PRIMARY KEY (block, locker_no)
);
INSERT INTO seats VALUES (NULL, 'Ada');
INSERT INTO lockers VALUES ('west', NULL, 'Bo');
SELECT seat_no, occupant FROM seats ORDER BY occupant;
SELECT block, locker_no, holder FROM lockers ORDER BY holder;

Both key columns print None, because a NULL that is written is a NULL that is stored.
seat_no prints 1 and locker_no prints None.
The first insert is refused with 'Error: NOT NULL constraint failed: seats.seat_no', so the seats table prints only its header and dash rule.

Entity-Relationship Modeling

Before writing CREATE TABLE statements, sketch out your entities and their relationships.

Common Relationship Patterns

PRAGMA foreign_keys = ON;

-- ONE-TO-MANY: One department has many staff members.
-- The table is called staff, not employees, because the constraints section above
-- already built an employees table with a different set of columns, and two tables
-- of the same name are two different designs.
CREATE TABLE departments (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE staff (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    department_id INTEGER,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- MANY-TO-MANY: Students enroll in many courses; courses have many students
CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE courses (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL
);

-- Junction table for the many-to-many relationship.
-- NOT NULL on both key columns is not decoration: without it this composite
-- primary key would accept two identical NULL-bearing rows, as the section above showed.
CREATE TABLE enrollments (
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    enrolled_date TEXT,
    grade TEXT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

-- Insert sample data
INSERT INTO departments VALUES (1, 'Engineering'), (2, 'Marketing');
INSERT INTO staff VALUES (1, 'Alice', 1), (2, 'Bob', 1), (3, 'Carol', 2);

INSERT INTO students VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Carol');
INSERT INTO courses VALUES (1, 'SQL 101'), (2, 'Web Dev'), (3, 'Data Science');

INSERT INTO enrollments VALUES (1, 1, '2025-01-05', 'A');
INSERT INTO enrollments VALUES (1, 2, '2025-01-05', 'B');
INSERT INTO enrollments VALUES (2, 1, '2025-01-06', 'A');
INSERT INTO enrollments VALUES (3, 2, '2025-01-07', NULL);
INSERT INTO enrollments VALUES (3, 3, '2025-01-07', NULL);

-- Query many-to-many: which students are in which courses?
SELECT
    s.name AS student,
    c.title AS course,
    e.grade
FROM enrollments e
JOIN students s ON e.student_id = s.id
JOIN courses c ON e.course_id = c.id
ORDER BY s.name, c.title;

-- How many students per course?
SELECT c.title, COUNT(e.student_id) AS enrollment_count
FROM courses c
LEFT JOIN enrollments e ON c.id = e.course_id
GROUP BY c.id, c.title
ORDER BY c.title;

The junction table (enrollments) is the standard way to implement many-to-many relationships. It contains foreign keys to both tables plus any attributes that belong to the relationship itself (like grade and enrolled_date).

Normalizing an Existing Table, Step by Step

Designing a schema on a blank page is one thing. Splitting a table that already holds data is the job you will actually be given, and it has an order to it that is forced rather than chosen: you cannot reference a table that does not exist yet, and you cannot drop the source until everything has been copied out of it.

CREATE TABLE signups_flat (id INTEGER PRIMARY KEY, club TEXT NOT NULL, member TEXT NOT NULL);
INSERT INTO signups_flat VALUES (1,'Chess','Ada'),(2,'Chess','Bo'),(3,'Rowing','Ada');
CREATE TABLE clubs (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE);

Three signup rows are already loaded, holding two clubs and two members between them, and an empty clubs table is waiting with a primary key of its own. That key is the point of the exercise: the flat table identifies a club by repeating its name on every row, and the normalized one will identify it by a number written once. CREATE TABLE ... AS SELECT builds a table from a query and fills it in one statement — note that it copies only columns and values, never a primary key, which is why clubs had to be declared in full above rather than derived.

Arrange the code

These five lines give each club a numbered row, link the memberships to it by that number instead of by name, check the link, empty the flat table now that everything has been copied out of it, and confirm it is empty. They are shuffled. Put them in the order that runs and that makes the link check report all three memberships with all three signup rows still counted beside them — then answer what the order is really testing: two of these lines error outright if they run too early, but one wrong order runs perfectly and silently destroys the migration. Which one, and why is that the more dangerous of the two failures?

SELECT club_id, member, (SELECT COUNT(*) FROM signups_flat) AS flat_rows_still_there FROM memberships ORDER BY club_id, member;
DELETE FROM signups_flat;
INSERT INTO clubs (id, name) VALUES (1,'Chess'),(2,'Rowing');
CREATE TABLE memberships AS SELECT c.id AS club_id, f.member FROM signups_flat f JOIN clubs c ON c.name = f.club;
SELECT COUNT(*) AS flat_rows_left FROM signups_flat;

When the Design Is Right and the Database Still Lets Bad Rows In

Debug

This script is a small normalized schema: studios, and films that reference them. It declares studio_id as REFERENCES studios(id) and NOT NULL, and then counts the films whose studio does not exist. A correct schema should make that count 0, and there should be no way to write a film for a studio that was never created. The script runs, exits cleanly, and prints 1. Commit a hypothesis about why the database accepted a film for studio 7, then fix the script so the count is 0.

CREATE TABLE studios (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE films (id INTEGER PRIMARY KEY, title TEXT NOT NULL, studio_id INTEGER NOT NULL REFERENCES studios(id));
INSERT INTO studios VALUES (1,'Aurora'),(2,'Borealis');
INSERT INTO films VALUES (10,'North',1);
INSERT INTO films VALUES (11,'Drift',2);
INSERT INTO films VALUES (12,'Orphan',7);
SELECT COUNT(*) AS films_with_no_studio
FROM films f
LEFT JOIN studios s ON s.id = f.studio_id
WHERE s.id IS NULL;

Expected output:

2 row(s) affected
1 row(s) affected
1 row(s) affected
films_with_no_studio
--------------------
0

Before you change anything — why do you think this fails?

Try It Yourself

The build below hands you a schema that works and is not safe: the columns have no constraints at all, and the report quietly drops a venue. Tighten the schema and fix the report.

Build

The starter creates two tables with no constraints and reports fees per venue. Work TODO 1 through TODO 3 so the schema refuses bad rows, a third venue that has booked nothing still appears in the report, and its total reads 0 rather than None. Money stays in integer cents throughout.

PRAGMA foreign_keys = ON;
-- TODO 1: tighten the schema below. A venue needs a name that is present and not
--         shared with another venue. A gig needs a venue that exists, a night, and
--         a fee above zero. Use NOT NULL, UNIQUE, REFERENCES and CHECK.
CREATE TABLE venues (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE gigs (id INTEGER PRIMARY KEY, venue_id INTEGER, night TEXT, fee_cents INTEGER);
-- TODO 2: add a third venue, the Attic with id 3, which has booked nothing yet.
INSERT INTO venues VALUES (1,'Hall'),(2,'Cellar');
INSERT INTO gigs VALUES (1,1,'2025-05-01',40000);
INSERT INTO gigs VALUES (2,2,'2025-05-02',25000);
INSERT INTO gigs VALUES (3,1,'2025-05-03',31000);
-- TODO 3: every venue must appear in the report, including one with no gigs.
--         The Attic should read 0 gigs and 0 fees_cents, not None.
--         SUM over no rows at all returns NULL, which prints as None. COALESCE(x, fallback)
--         returns x unless x is NULL, in which case it returns the fallback — so wrapping the
--         SUM in COALESCE(..., 0) turns that empty group into the number 0.
SELECT v.name AS venue, COUNT(g.id) AS gigs, SUM(g.fee_cents) AS fees_cents
FROM venues v
JOIN gigs g ON g.venue_id = v.id
GROUP BY v.name
ORDER BY v.name;

Expected output:

3 row(s) affected
1 row(s) affected
1 row(s) affected
1 row(s) affected
venue | gigs | fees_cents
-------------------------
Attic | 0 | 0
Cellar | 1 | 25000
Hall | 2 | 71000

Before you run — what's your plan for the first TODO?

Once it passes, try two variations and predict each before running. First, delete the PRAGMA foreign_keys = ON; line and add a gig for a venue 9 that does not exist — predict whether the insert is refused, then run it and see the row land silently. Second, put the pragma back and try inserting a second venue named Hall — predict which of your constraints stops it and what the message will name, then check your prediction against the error text.

Recall

No notes. In 05-joins-and-relationships you matched orders to customers. The normalized schema in this lesson splits one flat table into customers, orders, products and order_items. If one customer has three orders and you join customers to orders, how many rows does that one customer produce in the result — and what does that mean for a SUM over a customer-level column?

One row, because normalization stored the customer exactly once.
Three rows, and a SUM over a customer-level column triples it.
Four rows, because the customer row joins to the three orders and also appears on its own.

Transfer

Here is the same business question — total spend per customer — asked twice: once of the flat table this lesson opened with, and once of the normalized tables it ended with. On the data shown, both return Ada 3400 and Bo 2500, byte for byte. Which statement describes what normalizing actually changed about this query?

CREATE TABLE flat (cust TEXT NOT NULL, city TEXT NOT NULL, cents INTEGER NOT NULL);
INSERT INTO flat VALUES ('Ada','Oslo',2500),('Ada','Oslo',900),('Bo','Bergen',2500);
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, cents INTEGER NOT NULL);
INSERT INTO customers VALUES (1,'Ada','Oslo'),(2,'Bo','Bergen');
INSERT INTO orders VALUES (1,1,2500),(2,1,900),(3,2,2500);

-- against the flat table
SELECT cust, SUM(cents) AS spent
FROM flat GROUP BY cust ORDER BY cust;

-- against the normalized pair
SELECT c.name AS cust, SUM(o.cents) AS spent
FROM customers c JOIN orders o ON o.customer_id = c.id
GROUP BY c.name ORDER BY c.name;

Normalizing made the query correct; the flat version was double-counting Ada's city.
The aggregate is unchanged; what normalizing added is a join, and the join is now the thing that can silently drop a customer.
They are equivalent in every case, since the normalized tables hold exactly the same facts as the flat one.

Capstone milestone

Milestone — the normalized schema. Every later stage of the warehouse capstone reads from four tables: customers, products, orders and order_items. This is where those four tables get designed, along with the constraints that stop a later stage writing something the reports cannot survive. The build task above is that schema in miniature. Confirm you can write a schema whose rules are enforced rather than merely declared.

Hint: You do not need any reports yet — this is the schema every later milestone writes into and reads out of. The fourth item is the one people skip: a schema review cannot tell a declared constraint from an enforced one by reading, and in this environment the difference is one pragma that resets on every Run.

Split the flat order data into customers, products, orders and order_items so each fact is stored once
Gave every table a primary key, and wrote NOT NULL on every key column that is not a single INTEGER PRIMARY KEY
Declared each foreign key with REFERENCES, and ran PRAGMA foreign_keys = ON at the top of the script that relies on it
Proved at least one constraint by attempting a violation once and reading the error text it returned
Kept money in integer cents rather than a float column

Key Takeaways

Normalization reduces data redundancy and prevents update, insert, and delete anomalies
1NF: atomic values, no repeating groups. 2NF: no partial dependencies. 3NF: no transitive dependencies
Primary keys uniquely identify rows; foreign keys link related tables
A primary key implies UNIQUE always, but implies NOT NULL only for a single INTEGER PRIMARY KEY — write NOT NULL yourself on TEXT keys and on every column of a composite key
NOT NULL, UNIQUE and CHECK are enforced on every connection; a FOREIGN KEY is enforced only after PRAGMA foreign_keys = ON, which resets to off on every Run here
Many-to-many relationships use junction tables with foreign keys to both sides
Design your schema before writing queries — it's much harder to fix a bad design later

Pro Tip: Don't over-normalize. If you find yourself joining 7 tables for a simple query, you may have gone too far. Sometimes controlled redundancy (denormalization) is acceptable for performance-critical read-heavy applications. The key is making that decision consciously, not accidentally.

Next Steps

A good schema is not a one-time decision — requirements change, and your database needs to evolve with them. The next lesson covers migrations: how to add columns, rename tables, transform data, and restructure your schema safely without losing existing data. You will also learn the expand-and-contract pattern used for zero-downtime changes in production systems.