Normalization Basics

7 examples to get you started with Normalization - 5 basic and 2 intermediate.

Prerequisites

• PostgreSQL 18.4 with a scratch database for DDL experiments.
• Familiarity with CREATE TABLE, PRIMARY KEY, and FOREIGN KEY.

Basic Examples

1. Update Anomaly in One Wide Table

Duplicate data forces multi-row updates when a fact changes.

CREATE TABLE order_lines_bad (
  order_id        integer,
  customer_email  text,
  customer_city   text,
  product_sku     text,
  product_name    text,
  quantity        integer,
  PRIMARY KEY (order_id, product_sku)
);
 
-- Customer moves cities: must update every historical row
UPDATE order_lines_bad SET customer_city = 'Portland' WHERE customer_email = 'a@example.com';

• Storing customer_city on every line creates an update anomaly.
• Forgetting one row leaves contradictory city values for the same email.
• Normalization splits stable facts into their own tables.

Related: 3NF & BCNF - formal normal forms

2. Decompose Customer from Order Lines

Move repeating customer attributes to a parent table.

CREATE TABLE customers (
  customer_id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email         text NOT NULL UNIQUE,
  city          text NOT NULL
);
 
CREATE TABLE orders (
  order_id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id   bigint NOT NULL REFERENCES customers (customer_id)
);
 
CREATE TABLE order_items (
  order_id    bigint NOT NULL REFERENCES orders (order_id),
  product_id  bigint NOT NULL,
  quantity    integer NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (order_id, product_id)
);

• Customer city lives in one row - update once.
• Orders reference customers by FK - insert anomaly avoided with explicit parent row.
• Line items reference orders - deletion rules controlled by ON DELETE.

Related: Entity-Relationship Modeling - FK placement

3. Insert Anomaly Without a Product Table

You cannot add a catalog product until someone orders it in a denormalized design.

CREATE TABLE products (
  product_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku         text NOT NULL UNIQUE,
  name        text NOT NULL
);
 
-- Catalog team can INSERT products before any order exists
INSERT INTO products (sku, name) VALUES ('WIDGET-1', 'Widget');

• Normalized product dimension prevents insert anomalies.
• order_items references products when a sale happens.
• UNIQUE on sku enforces catalog identity.

Related: Intentional Denormalization - when to break rules deliberately

4. Delete Anomaly Preserves Catalog

Deleting the last order for a product must not delete the product definition.

CREATE TABLE order_items (
  order_id    bigint NOT NULL REFERENCES orders (order_id) ON DELETE CASCADE,
  product_id  bigint NOT NULL REFERENCES products (product_id) ON DELETE RESTRICT,
  quantity    integer NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

• ON DELETE RESTRICT on product_id prevents removing products still referenced.
• ON DELETE CASCADE on order_id removes line items when an order is cancelled.
• Separating entities eliminates delete anomalies on shared reference data.

5. Functional Dependency Drives Column Placement

If sku -> product_name, product_name belongs with sku, not on every fact row.

CREATE TABLE products (
  product_id  bigint PRIMARY KEY,
  sku         text NOT NULL UNIQUE,
  name        text NOT NULL
);
 
-- name is determined by sku (and product_id), not by order_id

• A functional dependency A -> B means B belongs in the same table as A.
• Fact tables carry IDs and measures (quantity), not descriptive duplicates.
• Document dependencies in migration comments for future reviewers.

Intermediate Examples

6. Transitive Dependency (Toward 3NF)

City determined by zip code should not sit on the customer if zip determines city.

CREATE TABLE zip_codes (
  zip   text PRIMARY KEY,
  city  text NOT NULL,
  state text NOT NULL
);
 
CREATE TABLE customers (
  customer_id  bigint PRIMARY KEY,
  email        text NOT NULL UNIQUE,
  zip          text NOT NULL REFERENCES zip_codes (zip)
);

• zip -> city is transitive when both sat on customers with only customer_id as key.
• Reference table zip_codes holds location attributes once.
• Join cost is the trade-off; index zip on customers.

Related: 3NF & BCNF - transitive dependencies

7. Detect Duplication with a Query

Find contradictory duplicates before they reach production reports.

SELECT customer_email, COUNT(DISTINCT customer_city) AS city_variants
FROM order_lines_bad
GROUP BY customer_email
HAVING COUNT(DISTINCT customer_city) > 1;

• Normalization problems appear as multiple distinct values for the same determinant.
• Run after ETL imports and before denormalizing for performance.
• Zero rows is the goal; non-zero rows trigger decomposition work.

Related: Denormalization Best Practices - safe denorm rules

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.