Schema design & modeling

How to normalize a table

Normalization has one job: store each fact once so it can't contradict itself. Work up the ladder.

1NF means every column holds one atomic value. No arrays, no comma-separated tags column, no repeating phone1, phone2, phone3. One value per cell.

2NF is 1NF plus no partial dependency, and it only applies when the primary key is composite. If the key is (order_id, product_id) and the table also stores product_name, that name depends on product_id alone, half of the key. It belongs elsewhere.

3NF is 2NF plus no transitive dependency: a non-key column depending on another non-key column. A table keyed on order_id that also carries customer_email is the classic miss, because email depends on customer_id, which is not the key.

The payoff is that three anomalies disappear. You change a customer's email in one row and miss another (update anomaly). You can't record a product until someone orders it (insert anomaly). You delete the last order and lose the customer with it (delete anomaly).

wrong "normalizing makes queries faster." It usually adds joins. Normalization buys correctness. Speed comes later, when you denormalize on purpose for a read pattern that demands it.

Modeling one-to-many and many-to-many

For one-to-many, the foreign key goes on the many side. An order belongs to one customer, so orders.customer_id references customers(id). Don't try to hold a list of order ids on the customer row.

Many-to-many needs a third table:

CREATE TABLE enrollment (
  student_id  INT NOT NULL REFERENCES students(id),
  course_id   INT NOT NULL REFERENCES courses(id),
  enrolled_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (student_id, course_id)   -- one enrollment per pair
);

The composite primary key (student_id, course_id) is what stops the same student from being enrolled in the same course twice. That junction row is also the right home for facts about the relationship itself, like enrolled_at or a role.

Keys and constraints

A surrogate key is a synthetic id with no business meaning: an auto-increment integer or a UUID. A natural key is a real attribute that is already unique, like an email or an ISBN. Most tables run on a surrogate PK because it's narrow and stable, but keep a UNIQUE constraint on the natural key too, or the surrogate will let you insert the same customer twice without complaint.

Constraints are the guarantees the database enforces whatever the app forgets: NOT NULL, UNIQUE, CHECK (amount >= 0), and the foreign key. One trap catches people: a UNIQUE column still allows several NULL rows in standard SQL, because NULLs are treated as distinct. If you need "at most one row without a value," UNIQUE alone won't give it to you.

When to denormalize a read-heavy schema

Normalize until it hurts, then denormalize for a read pattern you have measured. The moves are narrow: precompute a join into a wider table, store a derived aggregate (orders.total_cents instead of summing lines on every read), or copy one hot attribute to skip a join. Every copy you introduce is a fact stored twice, so every copy is now a consistency obligation. You keep it in sync in application code, in a trigger, or by refreshing a materialized view. Choose the mechanism and know its staleness window before you ship it.

wrong "denormalize because joins are slow." A well-indexed join over a few thousand rows is not your bottleneck. Denormalization earns its keep on genuinely hot read paths where the join fan-out or the aggregate cost shows up in a profile, and you pay for it on every write plus the risk of the copies drifting apart.

Point-in-time values are the exception people miss. An order line's unit_price looks like duplicated product data, yet it is the price at the moment of sale. Copying it is correct modeling, because the product's current price is a different fact from what the customer paid.

Choosing a primary key at scale

A surrogate integer PK joins and indexes cheaper than a wide composite natural key that every child table has to carry and cascade on rename. The subtle cost is the key's insertion order. On a clustered-index engine like InnoDB the table is physically ordered by the primary key, so a random UUIDv4 PK scatters inserts and hurts write locality, while a time-ordered UUIDv7 or ULID keeps inserts append-mostly. The B-tree mechanics behind that live in db-indexing. Whatever you choose for the surrogate, keep a UNIQUE on the natural key so the synthetic id can't wave business duplicates through.

Migrating a schema without downtime

The rule is that every migration step stays backward compatible, so old and new application code run against one schema during the rollout. That kills the in-place rename. You expand, then later contract:

-- expand: additive, old code ignores it
ALTER TABLE orders ADD COLUMN total_cents BIGINT;   -- nullable, no table rewrite
-- backfill in batches, then dual-write from the app
-- switch reads to total_cents, deploy
-- contract, only once nothing reads the old column:
ALTER TABLE orders DROP COLUMN total;

Two hazards are worth naming. Adding a NOT NULL column with no default fails immediately, because existing rows would violate it. Adding one with a non-constant default is the trap that rewrites the whole table under lock; a constant default is cheap on modern Postgres. Adding a foreign key or CHECK validates every existing row under lock, so on Postgres you add it NOT VALID first and run VALIDATE CONSTRAINT as a separate, non-blocking step.

Interviewers grade this on whether you volunteer backward compatibility before being asked, and whether you know a rename means add-new, backfill, dual-write, switch reads, then drop-old.