Normalization
1NF, 2NF, 3NF in plain English — and when to intentionally denormalize
Skills taught: pg.1nf, pg.2nf, pg.3nf, pg.denormalization, pg.schema-design
First Normal Form (1NF)
-- VIOLATION: comma-separated tags in one column
CREATE TABLE posts_bad (
id INTEGER PRIMARY KEY,
title TEXT,
tags TEXT -- 'postgresql,database,sql' — wrong!
);
-- Problem: cannot query by tag, cannot count tags, ordering is ambiguous
-- FIXED: separate tags table with a junction table
CREATE TABLE tags (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name TEXT NOT NULL UNIQUE
);
CREATE TABLE post_tags (
post_id BIGINT REFERENCES posts(id) ON DELETE CASCADE,
tag_id BIGINT REFERENCES tags(id) ON DELETE CASCADE,
PRIMARY KEY (post_id, tag_id)
);
-- VIOLATION: repeating groups
CREATE TABLE orders_bad (
id INTEGER PRIMARY KEY,
product1 TEXT,
qty1 INTEGER,
product2 TEXT, -- repeating group — wrong!
qty2 INTEGER
);
Third Normal Form (3NF) — the practical target
-- VIOLATION: customer data stored in every order (update anomaly)
CREATE TABLE orders_bad (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER,
customer_name TEXT, -- depends on customer_id, not order_id!
customer_email TEXT, -- same problem
customer_city TEXT, -- same problem
total NUMERIC
);
-- If customer changes email, must update every order row
-- If you miss one, data is inconsistent
-- FIXED: customer data in customers table, reference by ID
CREATE TABLE customers (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
city TEXT
);
CREATE TABLE orders (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(id),
total NUMERIC(10,2) NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
When to denormalize
-- posts.comment_count is denormalized for fast display
-- Instead of: SELECT COUNT(*) FROM comments WHERE post_id = 1 (on every page load)
-- Store it: posts.comment_count INTEGER DEFAULT 0
-- Maintain with a trigger:
CREATE OR REPLACE FUNCTION update_comment_count()
RETURNS TRIGGER AS $
BEGIN
IF TG_OP = 'INSERT' THEN
UPDATE posts SET comment_count = comment_count + 1 WHERE id = NEW.post_id;
ELSIF TG_OP = 'DELETE' THEN
UPDATE posts SET comment_count = comment_count - 1 WHERE id = OLD.post_id;
END IF;
RETURN NULL;
END;
$ LANGUAGE plpgsql;
CREATE TRIGGER trg_comment_count
AFTER INSERT OR DELETE ON comments
FOR EACH ROW EXECUTE FUNCTION update_comment_count();
Frequently Asked Questions
Do I need to know all the normal forms?
In practice, aim for 3NF and you'll catch most problems. The rules simplify to: (1) no comma-separated values in a single column, (2) no repeating column groups, (3) each table represents one entity, (4) non-key columns describe the primary key and nothing else. These four rules will get you to 3NF on most schemas.