SQL, joins,
and the index that saves you.

A relational database organizes data into tables — rows (records) and columns (fields) with a fixed schema. Two ideas make it "relational." A primary key uniquely identifies each row (a user's id). A foreign key in one table references the primary key of another, encoding a relationship (an orders row has a user_id pointing at users).

This lets you store each fact once and link records instead of duplicating them: a user's name lives in one place, and a thousand orders reference it by id. Relationships come in shapes — one-to-many (a user has many orders), many-to-many (students and courses, via a join table). The whole power of SQL is querying across these relationships, which is what joins are for.

Every schema eventually runs into the same question: when the same fact could appear in five different rows, do you store it once and point at it, or copy it into every row that needs it? The first is a master copy in a filing cabinet — go look it up when you need it. The second is a photocopy left on every desk — faster to grab, but now there are copies to keep straight. Normalization is the discipline of keeping one master copy; denormalization is the deliberate decision to make photocopies anyway. The normal forms are just a precise way of naming which duplicates are dangerous.

Start where almost every schema starts: someone models the business as one wide table, the way a spreadsheet would.

-- orders: one flat table carrying the whole world on every row
order_id  customer_email  customer_name  customer_city  item_skus  product_name  unit_price  category  category_manager
1001      ana@shop.io     Ana Reyes      Lisbon         "77, 91"   Trail Shoe    129.00      Footwear  M. Okafor
1002      ana@shop.io     Ana Reyes      Lisbon         "77"       Trail Shoe    129.00      Footwear  M. Okafor
1003      bo@shop.io      Bo Tran        Porto          "91"       Wool Sock      18.00      Apparel   J. Lindqvist

Nothing here is wrong yet — every query you can imagine is answerable, and none of them need a join. What's wrong is that three unrelated facts (who Ana is, what a Trail Shoe costs, who manages Footwear) are now stored once per order. Duplication isn't a tidiness problem; it's a correctness problem, and it shows up as three specific failure modes.

The anomalies normalization exists to prevent

These three are why Codd's 1970 paper and the normal forms that followed exist at all. The forms aren't academic hygiene — each one removes a specific class of anomaly.

What 1NF, 2NF and 3NF actually say

The old mnemonic compresses all three: every non-key column must depend on the key, the whole key, and nothing but the key. Applied to our spreadsheet, that mechanically produces this schema:

CREATE TABLE customers (
  id           bigserial PRIMARY KEY,
  email        text NOT NULL UNIQUE,
  name         text NOT NULL,
  city         text NOT NULL
);

CREATE TABLE categories (
  id           bigserial PRIMARY KEY,
  name         text NOT NULL UNIQUE,
  manager      text NOT NULL
);

CREATE TABLE products (
  id           bigserial PRIMARY KEY,
  sku          text NOT NULL UNIQUE,
  name         text NOT NULL,
  list_price   numeric(10,2) NOT NULL,
  category_id  bigint NOT NULL REFERENCES categories(id)
);

CREATE TABLE orders (
  id           bigserial PRIMARY KEY,
  customer_id  bigint NOT NULL REFERENCES customers(id),
  placed_at    timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  order_id     bigint NOT NULL REFERENCES orders(id),
  product_id   bigint NOT NULL REFERENCES products(id),
  qty          int NOT NULL CHECK (qty > 0),
  PRIMARY KEY (order_id, product_id)
);

All three anomalies are now structurally impossible. Ana's city lives in exactly one row, so it cannot disagree with itself. A product or a category can exist before anyone orders it. Deleting an order can't take a category down with it. You didn't add discipline — you removed the ability to be inconsistent, which is a much stronger guarantee than a code review.

When denormalizing is the right engineering call

The bill arrives on the read path. Rendering one line of a customer's order history now touches five tables:

SELECT o.id, o.placed_at, p.name, p.list_price, c.name AS category
FROM   orders o
JOIN   order_items oi ON oi.order_id   = o.id
JOIN   products    p  ON p.id          = oi.product_id
JOIN   categories  c  ON c.id          = p.category_id
JOIN   customers   cu ON cu.id         = o.customer_id
WHERE  cu.id = $1
ORDER  BY o.placed_at DESC
LIMIT  20;

One join is nearly free. A five-way join on a page served ten thousand times a minute is a different animal: more rows touched, more index descents, and a plan the optimizer can get wrong as statistics drift (you can watch that happen in the query planner lab). Three situations genuinely justify copying data in:

So the denormalized version of our hot path is a narrow, deliberate edit — two columns, one table, for one query:

ALTER TABLE order_items
  ADD COLUMN product_name text          NOT NULL,  -- copy of products.name
  ADD COLUMN unit_price   numeric(10,2) NOT NULL;  -- price AT PURCHASE TIME

-- the same page, now two tables instead of five
SELECT o.id, o.placed_at, oi.product_name, oi.unit_price
FROM   orders o
JOIN   order_items oi ON oi.order_id = o.id
WHERE  o.customer_id = $1
ORDER  BY o.placed_at DESC
LIMIT  20;

The bill you just signed

Denormalizing doesn't remove the tradeoff, it moves the cost from reads to writes, and you pay it in two currencies. The first is write amplification: renaming one product used to be a single-row UPDATE; now it rewrites every order_items row that copied the name — plus every index entry on those rows, plus the write-ahead log, plus the bytes shipped to every replica. A cheap write became an unbounded one, scaling with how popular the product is. (That amplification is the same force that shapes storage engines — see the B-tree vs LSM race.)

The second is worse: you now own consistency yourself. A foreign key can enforce that a reference exists; nothing in SQL can enforce that a copy still matches. The database will happily hold a row saying "Trail Shoe" forever after the product was renamed. Whatever keeps the copies in step is now your code — a dual write inside the same transaction, a trigger, a materialized view you refresh, or a reconciliation job that sweeps for drift — and every one of those has a failure mode and a staleness window you have to name out loud. This is the same bargain a cache makes; denormalization is just caching baked into the schema, with the same invalidation problem and none of the TTL.

Where you land also depends on the engine. Document stores denormalize by default — that's the whole shape of the SQL vs NoSQL trade — and relational engines differ in what they give you for free (Postgres vs MySQL on materialized views, generated columns, and index-organized tables). Once copies live on more than one node, keeping them in step stops being a schema question and becomes a CP vs AP one.

▶  Watch it explained

Normalization vs denormalization: one master copy, or photocopies everywhere?

A join combines rows from two tables by matching a condition, usually a foreign key equal to a primary key. The join type decides what happens to rows that don't match.

The most common bug is expecting a LEFT JOIN but writing an INNER JOIN and silently dropping rows that have no match — a user with zero orders vanishes from the report. Pick the type by asking: "do I want to keep rows that have no partner on the other side?"

Without an index, finding rows where email = ... means a full table scan: read every row and check. On a million rows, that's slow. An index is a separate, sorted data structure — almost always a B-tree — that maps a column's values to the rows containing them, so the database can jump straight to matches in O(log N) instead of O(N).

Why a B-tree? It keeps keys sorted and balanced, so lookups, range scans (BETWEEN, >), and ordered reads (ORDER BY) are all fast. But indexes aren't free: they take space, and every INSERT/UPDATE/DELETE must also update the index — so indexes speed reads and slow writes. Index the columns you filter, join, and sort on; don't index everything.

The gap is enormous and exact. On a million rows a full scan checks all million; a B-tree descends its height, ⌈log₂N⌉ ≈ 20 steps:

But an index isn't always the fast plan: for a query returning most of the table, a sequential scan beats a million random index fetches — which is exactly the tradeoff the planner weighs. The runnable version below counts both and lets the planner choose.

You write what you want (declarative SQL); the database's query planner decides how to get it — which indexes to use, join order, and algorithm. It estimates the cost of each plan using table statistics and picks the cheapest. The same query can run in milliseconds or minutes depending on the plan.

EXPLAIN (and EXPLAIN ANALYZE) shows you the plan the database chose. The single most important thing to spot: a sequential scan on a big table where you expected an index scan — that usually means a missing index, or a query written so the index can't be used (a function on the column, a leading wildcard LIKE '%x', an implicit type cast). Reading EXPLAIN is how you turn "the query is slow" from a mystery into a fix.

A transaction groups several statements so they succeed or fail as a unit. Relational databases guarantee ACID:

The classic example is a money transfer: debit one account and credit another must both happen or neither. Atomicity makes that safe; durability makes it stick. This is why financial and inventory systems live on relational databases — ACID is the guarantee.

Perfect isolation (every transaction as if it ran alone) is expensive, so databases offer isolation levels that trade correctness against concurrency. Each level prevents certain anomalies:

The levels — READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE — progressively prevent more of these. Most databases default to READ COMMITTED (no dirty reads), and you raise it where you need stronger guarantees. SERIALIZABLE prevents all anomalies but costs the most concurrency. Pick the weakest level that keeps your logic correct.

A handful of mistakes cause most SQL performance pain.

Underneath all of them is one habit: know what the database is actually doing. Read the plan, index intentionally, fetch in bulk not in loops, and reach for transactions when correctness matters. That's the difference between SQL that works on your laptop and SQL that works in production.