Schema Design and Normalization in PostgreSQL

Normalization has a reputation for being an academic topic, something you memorize for an exam and then forget. That's a shame, because the ideas behind it are practical and they map directly onto bugs you'll otherwise ship. A normalized schema is one where each fact lives in exactly one place. When a fact lives in two places, they eventually disagree, and now you have data you can't trust.

This is the second article in the PostgreSQL Masterclass. We covered data types last time. Now we arrange those columns into tables that stay correct as the product grows.

The problem normalization solves

Say you track orders, and you store the customer's email on every order row because it's handy for sending receipts:

-- the shape you regret later
CREATE TABLE orders (
  id             BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_email text NOT NULL,
  customer_name  text NOT NULL,
  total          numeric(12, 2) NOT NULL,
  created_at     timestamptz NOT NULL DEFAULT now()
);

A customer changes their email. Now you have a choice: update every historical order row, or accept that old orders show a stale address. Neither is good. The email is a fact about the customer, and you've copied it onto every order, so the truth is smeared across hundreds of rows instead of sitting in one.

The fix is to store the fact once and reference it:

CREATE TABLE customers (
  id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE,
  name  text NOT NULL
);

CREATE TABLE orders (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(id),
  total       numeric(12, 2) NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

Change the email in one place and every order reflects it. That's the whole point of normalization, stripped of the jargon.

The normal forms, in plain terms

You don't need to recite the formal definitions, but the first three normal forms are worth understanding because they cover almost everything you'll meet in practice.

First normal form (1NF): each column holds a single value, not a list. If you find yourself storing "red,green,blue" in a text column and splitting it in application code, that's a 1NF violation. In Postgres you might reach for an array or a related table instead, and which one depends on whether you'll query the individual values.

Second normal form (2NF): every non-key column depends on the whole primary key, not just part of it. This only bites when you have a composite key. If a table keyed on (order_id, product_id) also stores the product's name, that name depends only on product_id, so it belongs in a products table.

Third normal form (3NF): non-key columns depend on the key and nothing but the key. If your orders table stores customer_city and customer_country, those describe the customer, not the order. They've leaked in from another entity and should move out.

The one-line summary that carries you most of the way: every column should describe the thing the table is about, and each fact should be stored once. If a column is really describing some other entity, it belongs in that entity's table.

Foreign keys are not optional

A foreign key is the database enforcing that a reference points at something real. Without it, nothing stops an orders row from referencing a customer_id that was deleted last week, and you find out when a join silently drops rows or a page crashes on a null.

CREATE TABLE orders (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
  total       numeric(12, 2) NOT NULL
);

The ON DELETE clause is a design decision worth making deliberately:

• RESTRICT (or NO ACTION) blocks deleting a customer who still has orders. Usually what you want for financial records.
• CASCADE deletes the orders along with the customer. Right for genuinely owned children, like deleting a user's draft comments, and dangerous if you apply it to anything you'd want to keep.
• SET NULL clears the reference. Useful when the child can outlive the parent, like keeping audit rows after the actor is gone.

Pick per relationship. The default of doing nothing and hoping application code stays consistent is how you end up with orphaned rows.

When to denormalize on purpose

Normalization is the default, not a religion. There are real cases where copying data is the right call, and the distinction is that you do it deliberately, with a plan for keeping the copy correct.

The common one is a computed total. Recomputing an order's total by summing its line items on every read is wasteful once the order is finalized and will never change. Storing the total on the order row is a denormalization, and it's fine because a finalized order is immutable, so the copy can't drift.

The other common case is read performance at scale. If a dashboard query joins 6 tables and runs thousands of times a minute, a pre-joined summary table or a materialized view can be worth the redundancy. The rule is to reach for this when you have a measured performance problem, not on a hunch. We'll cover materialized views specifically later in the series.

When you do denormalize, write down how the copy stays in sync: a trigger, an application-level update, or an accepted rule that the source is immutable. A copy with no sync story is just a future bug.

A worked example

Here's a small but realistic slice of a multi-tenant SaaS schema that applies all of the above:

CREATE TABLE organizations (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE users (
  id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  org_id  BIGINT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  email   text NOT NULL,
  name    text NOT NULL,
  UNIQUE (org_id, email)   -- email unique within an org, not globally
);

CREATE TABLE projects (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  org_id     BIGINT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  owner_id   BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  name       text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

Notice the details. Each fact sits in one table. The UNIQUE (org_id, email) says an email is unique within an organization, which is what multi-tenant apps usually want. Deleting an organization cascades to its users and projects because they can't exist without it, while deleting a user who owns a project is restricted, so you don't accidentally orphan work.

Good schema design is mostly this: name your entities honestly, keep each fact in one place, let foreign keys enforce the relationships, and denormalize only when you can explain why. Get that right and the queries mostly write themselves.

Next, we settle a question every one of these tables raised: what to use for the primary key. UUID or BIGINT, and why the answer isn't the same for every table.