Data modeling

Normalize until it hurts, denormalize until it works. Data modeling is the constant trade-off between integrity (one fact in one place) and read performance (the fact is already where the query needs it).

The big idea

Two pressures pull schemas in opposite directions:

Start normalised. Denormalise only where evidence shows a real performance problem you can't solve with an index.

Normal forms (just enough)

You don't need to recite the formal definitions. The intuitive rules:

1. 1

   1NF — atomic values

   No "tags: 'cooking,baking,vegan'" in a single column. Use a related table or an array type if your DB supports it.

2. 2

   2NF + 3NF — no transitive dependencies

   A field belongs in the table whose primary key it directly describes. If users.city determines users.country, country belongs to a cities table.

3. 3

   BCNF and beyond

   You don't need this in 90% of designs. Read it when you hit the 10%.

A worked example

E-commerce: users have orders, orders have line items, items reference products.

CREATE TABLE users (
  id            BIGSERIAL PRIMARY KEY,
  email         TEXT NOT NULL UNIQUE,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);
 
CREATE TABLE products (
  id            BIGSERIAL PRIMARY KEY,
  sku           TEXT NOT NULL UNIQUE,
  name          TEXT NOT NULL,
  price_cents   INTEGER NOT NULL CHECK (price_cents >= 0)
);
 
CREATE TABLE orders (
  id            BIGSERIAL PRIMARY KEY,
  user_id       BIGINT NOT NULL REFERENCES users(id),
  status        TEXT NOT NULL CHECK (status IN ('new','paid','shipped','cancelled')),
  total_cents   INTEGER NOT NULL,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);
 
CREATE TABLE order_items (
  order_id      BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id    BIGINT NOT NULL REFERENCES products(id),
  quantity      INTEGER NOT NULL CHECK (quantity > 0),
  price_cents   INTEGER NOT NULL,                 -- snapshot at time of order
  PRIMARY KEY (order_id, product_id)
);

When to denormalise

Three legitimate reasons:

Historical accuracy

Order line items snapshot the price at purchase time.

Hot read paths

A counter (users.unread_count) that would be slow to compute every page load. Keep it in sync via the write path.

Cross-shard reads

If a join would cross a shard boundary, duplicate the data into both shards.

Surrogate vs natural keys

Modeling many-to-many

The bread-and-butter pattern: a join table with two foreign keys.

CREATE TABLE user_roles (
  user_id  BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role_id  BIGINT NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
  granted_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (user_id, role_id)
);

If you find yourself adding columns to the join table (granted_by, expires_at), it has become a first-class entity — give it its own ID and treat it like one.

In practice

Sketch the schema with sample rows in a tool like dbdiagram.io before you write a migration. Write the 5–10 most important queries against it on paper. If a query is ugly, the schema is wrong. Fix it now, not in production.