Warming up the neural circuits...
By the end of this chapter you will:
A well-designed schema is invisible. It does its job and nobody notices. A badly-designed schema is a fire that never goes out — duplicate data you forget to update, fields with no type enforcement, tables that require 7 JOINs for every query. is the antidote.
A messy kitchen has flour next to the sink, spices scattered across three cabinets, and knives in a random drawer. Cooking takes twice as long because you're always searching. A professional kitchen — mise en place — has every ingredient in a labeled container at a known location. That's normalization.
Normalization is about putting each piece of data in exactly one logical place. When a customer changes their email, you update one row in one table. When a product price changes, you update one row — not 500 cached copies in 12 different tables.
Every column contains a single value. No arrays, no comma-separated lists, no "phone1, phone2, phone3" columns.
-- ❌ NOT 1NF — repeating group
CREATE TABLE orders_bad (
id SERIAL PRIMARY KEY,
customer TEXT,
items TEXT, -- "pizza, coke, fries" ← no
quantities TEXT -- "1, 2, 1" ← matching by position? nightmare
);
-- ✅ 1NF — each item is a separate row
CREATE TABLE orders (id SERIAL PRIMARY KEY, customer TEXT);
CREATE TABLE order_items (
id SERIAL PRIMARY KEY,
order_id INT REFERENCES orders(id),
item TEXT,
quantity INT
);What it prevents: Having to parse "pizza, coke, fries" with string.split(',') in every query. Unsearchable, un-indexable, un-joinable.
Every non-key column must depend on the entire primary key, not just part of it. Only relevant for tables with composite primary keys.
-- ❌ NOT 2NF — department_name depends only on department_id (part of the key)
CREATE TABLE employee_projects (
employee_id INT,
project_id INT,
department_id INT,
department_name TEXT, -- depends on department_id, not the full key!
PRIMARY KEY (employee_id, project_id)
);
-- ✅ 2NF — split into two tables
CREATE TABLE employee_projects (
employee_id INT,
What it prevents: Updating "Engineering" to "Platform Engineering" in 500 rows and inevitably missing 3.
No column should depend on another non-key column. Every non-key column must depend directly on the primary key, not on another non-key column.
-- ❌ NOT 3NF — city_name and state depend on zip_code, not on customer_id
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
name TEXT,
zip_code TEXT,
city_name TEXT, -- depends on zip_code, not id!
state TEXT -- depends on zip_code, not id!
);
-- ✅ 3NF — decompose
CREATE TABLE customers (
What it prevents: A user updating their zip code but not their city, creating an inconsistency where zip 94105 maps to "San Francisco" in one row and "Oakland" in another.
Boyce-Codd Normal (BCNF) is a slightly stricter 3NF. Every determinant must be a candidate key. In practice, 3NF covers 95% of real-world schema design. If you're hitting BCNF violations, you're already deep into database theory.
4NF addresses multi-valued dependencies. If a table has two independent one-to-many relationships, split them:
-- ❌ NOT 4NF — skills and languages are independent
CREATE TABLE developers (
id INT, skill TEXT, language TEXT,
PRIMARY KEY (id, skill, language)
);
-- Row: (1, "Python", "English"), (1, "Python", "French"), (1, "Java", "English")...
-- Data explodes: N skills × M languages rows per developer!
-- ✅ 4NF — separate tables
CREATE TABLE developer_skills (developer_id INT, skill
Normalization is the default. Denormalization is an optimization you apply with a specific performance goal, backed by a benchmark.
Legitimate reasons to denormalize:
| Pattern | When | Example |
|---|---|---|
| Snapshot / materialized aggregate | Computing a COUNT or SUM on every page load is too slow | Store order_count on the customers table, update it via trigger or application code |
| Pre-joined tables | A 5-table JOIN is needed on 90% of page loads | Create a order_summaries materialized view or table |
| Historical snapshots | You need to know what data looked like at the time of the event, not what it looks like now | Store product_price_at_time_of_order in the order_items table alongside product_id |
| Event sourcing | Every change is an append-only event | The current state is a projection, not the source of truth |
Before denormalizing, answer three questions:
EXPLAIN ANALYZE.)A primary key uniquely identifies each row. You have two choices:
| Type | Example | Pros | Cons |
|---|---|---|---|
| Surrogate | BIGSERIAL, UUID | Never changes, fast JOINs, no business meaning | Adds a column, no built-in uniqueness on business fields |
| Natural | email, sku, isbn | Meaningful, enforces business uniqueness | Can change (email changes, SKU format changes), wider, slower JOINs |
The industry consensus: Use surrogate keys (BIGSERIAL or UUID) as the primary key for every table. Add a UNIQUE constraint on any natural key that must be unique for business reasons.
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY, -- surrogate — never changes
sku VARCHAR(50) NOT NULL UNIQUE, -- natural — business meaning, enforced unique
name VARCHAR(255) NOT NULL
);Shopify runs on MySQL (historically) with tens of thousands of tables. Their schema design principles:
Every table has id BIGINT PRIMARY KEY AUTO_INCREMENT. Surrogate keys everywhere. Business uniqueness is enforced with separate UNIQUE constraints.
Soft deletes with deleted_at. Never hard-delete a row. Mark it with a timestamp. This preserves history, enables undo, and makes analytics possible. A background job eventually purges rows deleted > 90 days ago.
Audit trails as separate tables. orders holds current state. order_events holds every state change. This is 4NF in action — current state and history are independent concerns.
Denormalized counters. shop.product_count is updated synchronously on product create/delete. This was denormalized because "show the shop's product count" was called on 80% of page loads and joining + counting was the #1 slow query.
| Mistake | Why it's wrong | What to do instead |
|---|---|---|
| UTF-8 emoji in a VARCHAR field without checking encoding | MySQL with utf8 (3-byte) silently truncates 4-byte emoji like 🙋 | Use utf8mb4 in MySQL. PostgreSQL's UTF8 is 4-byte by default and handles it correctly. |
| Natural keys as primary keys | Email address as PK means every table referencing users stores the full email. User changes email → cascade update across 50 tables | Surrogate BIGSERIAL or UUID as PK. UNIQUE constraint on the natural key. |
| Premature denormalization | "We'll denormalize from day one because JOINs are slow" — you now maintain the same data in 3 places with no consistency guarantees | Start normalized (3NF). Measure. Denormalize only where EXPLAIN ANALYZE proves it's necessary. |
| No UNIQUE constraint on business keys | Two users with the same email. Two products with the same SKU. Application-level checks have race conditions. | Add UNIQUE constraints in the database. The database is the single source of truth. |
deleted BOOLEAN instead of deleted_at TIMESTAMPTZ | You know something was deleted, but not when. You can't audit. You can't purge "deleted > 90 days" efficiently. | deleted_at TIMESTAMPTZ — NULL means active, a timestamp means deleted, and you get the "when" for free. |
NOT NULL column to a 500M-row table locks the table for hours. Use strategies like: add as nullable first, backfill in batches, then change to NOT NULL when done.UUID v7 (time-ordered) or BIGSERIAL for the primary key, UUID for external-facing IDs.CREATE VIEW doesn't store data. A complex view is as slow as the underlying query. Use MATERIALIZED VIEW when you need the performance benefit of pre-computation.SELECT * on a normalized schema can require 5+ JOINs. This is why ORMs generate slow queries by default. Write explicit queries that fetch only the columns and JOINs you need.deleted_at = NOW() doesn't prevent a buggy query from returning it. Use Row-Level Security (RLS) if you need hard guarantees.orders(id, customer_name, customer_email, product_1, product_2, product_3, total). Normalize it to 3NF. Draw the resulting tables and their relationships.UNIQUE constraint to the users.email column. Try inserting a duplicate. What error does PostgreSQL return?post_comment_count column onto the posts table. Write a PostgreSQL trigger that increments it on INSERT and decrements on DELETE to the comments table. Write a query that verifies the counter matches SELECT COUNT(*) FROM comments WHERE post_id = X.tenant_id column. Add Row-Level Security policies so tenant A can never see tenant B's data.What is normalization? Organizing a database to reduce redundancy and improve data integrity by ensuring each piece of data exists in exactly one place. 1NF eliminates repeating groups, 2NF eliminates partial key dependencies, 3NF eliminates transitive dependencies.
What's the difference between a primary key and a unique constraint? Both enforce uniqueness. A table can have only one primary key (which is also NOT NULL), but many unique constraints. The primary key is the default target for foreign keys.
Why shouldn't you use an email address as a primary key? Emails change. If email is the primary key, every referencing it must cascade-update, which is slow and risky. Use a surrogate key (BIGSERIAL/UUID) as PK and a UNIQUE constraint on email.
When is denormalization the right choice? Give a concrete example. When a specific query is measured to be too slow with normalized data and the performance gain from denormalization outweighs the consistency cost. Example: an e-commerce product page that must show "number of reviews" and "average rating." Running SELECT COUNT(*), AVG(rating) FROM reviews WHERE product_id = X on every page load is slow at scale. Denormalizing review_count and avg_rating onto the products table (updated via trigger) reduces a 50ms query to a 0.5ms column read.
What happens if you add a NOT NULL column to a 1-billion-row table? The database must rewrite every row to include the new column with a default value, locking the table for the duration. In PostgreSQL 11+, adding a column with a constant default is instant (only metadata changes). In older versions, you'd add it as nullable, backfill in batches, then set NOT NULL.
Explain the difference between soft deletes and hard deletes. When would you use each? Soft delete: mark a row with deleted_at without removing it. Enables undo, preserves history for analytics, maintains referential integrity. Hard delete: remove the row permanently from disk. Use soft deletes by default for customer-facing data (users can recover). Use hard deletes for ephemeral data (sessions, rate limit counters, cache tables) where retention has no value.
Normalization (1NF → 2NF → 3NF) is your default. Each form eliminates a specific class of data anomaly. Denormalization is an optimization you apply deliberately, backed by benchmarks, with a plan for consistency. Use surrogate keys (BIGSERIAL/UUID) as primary keys and UNIQUE constraints for business keys. Design schemas that can survive product changes — the data model outlives the first version of the product.
deleted_at TIMESTAMPTZ, not BOOLEAN.deleted_at TIMESTAMPTZ give you that deleted BOOLEAN doesn't? The timestamp of deletion, enabling audit trails and time-based purging ("delete rows deleted more than 90 days ago").| One giant table with 80 columns | Impossible to properly, every query is slow, schema changes block everything | Design to 3NF. Use views for convenience queries, not as a substitute for proper schema design. |