Chapter 2: Relational Fundamentals: Tables, Keys, Constraints, Normalization

Why this matters

Relational database fundamentals are frequently treated as "basic" and skipped past quickly — but a surprising share of real production data-integrity bugs trace back to a missing constraint, a wrong key choice, or a normalization decision made without understanding the actual trade-off. Getting these right structurally prevents entire categories of bugs from being possible at all, the same "structural prevention over convention" theme from the companion OOP book's encapsulation chapter, applied to data instead of objects.

Tables, rows, and columns

[BEGINNER] A relational database organizes data into tables (rows and columns), where each row represents one record and each column represents one attribute of that record, with a declared data type per column.

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP NOT NULL DEFAULT now()
);

Keys: identity and relationships

[BEGINNER] A primary key uniquely identifies each row in a table — no two rows can share one, and it can't be null. A foreign key references another table's primary key, establishing and enforcing a relationship between tables.

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    total_cents INTEGER NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT now()
);

-- This INSERT fails if customer_id 999 doesn't exist in the customers table —
-- the foreign key constraint makes an orphaned order STRUCTURALLY IMPOSSIBLE,
-- not just discouraged by application-level convention.
INSERT INTO orders (customer_id, total_cents) VALUES (999, 2999);
-- ERROR: insert or update on table "orders" violates foreign key constraint

[SENIOR INSIGHT] This is the direct database-level analog of the companion OOP book's Chapter 1 lesson: an invariant ("every order belongs to a real customer") enforced by application code alone can be bypassed by any code path that doesn't know the convention exists; a foreign key constraint makes it structurally impossible for the database to accept a violating row, regardless of which application, script, or engineer is writing to it.

Constraints: enforcing invariants at the data layer

[BEGINNER] Beyond primary/foreign keys, PostgreSQL supports several other constraint types, each enforcing a specific invariant directly in the database:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    sku VARCHAR(50) NOT NULL UNIQUE,           -- UNIQUE: no two products share a SKU
    price_cents INTEGER NOT NULL CHECK (price_cents > 0),  -- CHECK: enforces a business rule directly
    stock_quantity INTEGER NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0)  -- can never go negative
);

[IN PRACTICE] The CHECK (stock_quantity >= 0) constraint means that any code path attempting to decrement stock below zero — a bug in application logic, a race condition, an ad hoc script run by an engineer during an incident — is rejected by the database itself, rather than relying on every single piece of code that touches this table to remember and correctly implement the rule. This is a direct, practical instance of "push invariants as close to the data as possible" — the database is the one place every writer must pass through, making it the most reliable enforcement point available.

Normalization: organizing data to eliminate redundancy

[BEGINNER] Normalization is the process of structuring tables to minimize data redundancy and prevent update anomalies — organized into a series of increasingly strict "normal forms," of which the first three are what matter for most practical schema design:

Before normalization — redundant data, prone to inconsistency:

-- One wide table: customer info repeated on every single order row.
CREATE TABLE orders_unnormalized (
    order_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(255),
    customer_email VARCHAR(255),
    product_name VARCHAR(255),
    product_price_cents INTEGER
);
-- If a customer updates their email, EVERY row for that customer must be updated —
-- miss even one, and the data is now inconsistent (an "update anomaly").

After normalization — each fact stored exactly once:

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255)
);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    price_cents INTEGER
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    product_id INTEGER REFERENCES products(id)
);
-- A customer's email is stored in EXACTLY ONE place. Updating it updates it everywhere
-- it matters, because every order simply REFERENCES the customer, rather than
-- duplicating their data.

When to denormalize deliberately

[SENIOR INSIGHT] Normalization isn't a goal in itself — it's a tool for preventing update anomalies, at the cost of requiring joins (Chapter 5) to reconstruct a complete picture across tables, which has a real performance cost, especially at scale. Denormalization — deliberately reintroducing some redundancy — is a legitimate, common optimization when read performance matters more than eliminating redundancy, and the redundant data changes rarely enough that the update-anomaly risk is manageable:

-- Deliberately denormalized: storing a snapshot of the product name/price AT THE TIME
-- of the order, rather than joining to the current products table.
CREATE TABLE order_line_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id),
    product_id INTEGER REFERENCES products(id),
    product_name_snapshot VARCHAR(255),   -- denormalized: a deliberate historical snapshot
    price_cents_snapshot INTEGER          -- NOT a live reference — this is INTENTIONAL
);

This isn't a normalization mistake — it's a deliberate design decision: an order's receipt should show the price the customer actually paid, not the product's current price if it's since changed. This is denormalization used correctly, for a real business reason, not as a shortcut around understanding normalization properly.

Common mistakes

1. Skipping foreign key constraints "to keep things simple" or for perceived performance reasons, relying entirely on application code to maintain referential integrity — this reintroduces exactly the "convention, not structure" failure mode that constraints exist to prevent.
2. Over-normalizing reflexively, splitting data into many small tables even where the redundancy being eliminated is trivial and the join cost is real and frequently paid — normalization is a tool with a real cost (joins), not an unconditional virtue.
3. Denormalizing without a real, deliberate reason ("copy the data here too, it'll be faster") without considering the update-anomaly risk being reintroduced, or without it actually reflecting a real business need like the historical-snapshot example above.
4. Using CHECK constraints and NOT NULL/UNIQUE inconsistently — enforcing some business rules at the database layer while leaving structurally identical ones to "the application should handle it," creating an inconsistent, partially-protected schema.

Checklist: designing relational schemas well

1. Use foreign keys to enforce every real relationship between tables — don't rely on application code alone to maintain referential integrity.
2. Use CHECK, NOT NULL, and UNIQUE constraints for every genuine business invariant the database can enforce directly, rather than leaving it to scattered application-level validation.
3. Normalize to at least third normal form by default for data with real update-anomaly risk (data that changes and is referenced from multiple places).
4. Denormalize deliberately and document why, specifically when read performance genuinely requires it or when a historical snapshot is the actual business requirement (not merely "current data, duplicated for convenience").

Discussion / interview questions

1. "What's the difference between a primary key and a foreign key?" Strong answer: A primary key uniquely identifies each row in its own table; a foreign key references another table's primary key, establishing and enforcing a relationship — an insert/update violating that reference is rejected by the database.

2. "Why would you use a database-level CHECK constraint instead of only validating a rule in application code?" Strong answer: A database constraint is enforced for every write, from every source (application code, scripts, other services, manual intervention), making a violation structurally impossible rather than merely discouraged by convention — application-only validation can be bypassed by any code path that doesn't go through it.

3. "What problem does normalization solve, and what's the trade-off?" Strong answer: Prevents update anomalies by storing each fact exactly once, requiring only a single update when that fact changes; the trade-off is needing joins to reconstruct a complete picture across normalized tables, which carries a real performance cost, especially at scale.

4. "Give an example of a deliberate, justified denormalization decision, and explain why it's not just 'skipping normalization.'" Strong answer: A historical snapshot (e.g., storing the product price at the time of an order, rather than joining to the current price) — justified because the business requirement is genuinely "what did the customer pay then," not "what does the product cost now," making the redundancy intentional and correct rather than an oversight.

Chapter summary / cheat sheet

• Tables organize data into rows/columns; primary keys uniquely identify rows; foreign keys enforce relationships between tables structurally, not by convention.
• CHECK, NOT NULL, and UNIQUE constraints push business invariants down to the data layer, where every writer — application code, scripts, other services — must pass through them.
• Normalization eliminates redundancy and prevents update anomalies, at the cost of requiring joins to reconstruct data across tables.
• Denormalization is a legitimate, deliberate optimization (for read performance or intentional historical snapshots) — not a shortcut for skipping normalization out of convenience.

---