3NF & BCNF

Practical normalization without academic overkill: decompose tables until non-key attributes depend on the whole key, nothing but the key.

Recipe

Quick-reference recipe card - copy-paste ready.

-- Violates 3NF: city depends on zip, not on customer_id alone
-- customers(customer_id, email, zip, city)
 
-- 3NF fix: remove transitive dependency zip -> city
CREATE TABLE zip_codes (zip text PRIMARY KEY, city text NOT NULL);
CREATE TABLE customers (
  customer_id bigint PRIMARY KEY,
  email       text NOT NULL UNIQUE,
  zip         text NOT NULL REFERENCES zip_codes (zip)
);
 
-- BCNF fix: when determinant is not a superkey
-- enrollments(student_id, course_id, instructor_id) with rule course_id -> instructor_id
CREATE TABLE course_instructors (
  course_id      text PRIMARY KEY,
  instructor_id  text NOT NULL
);
CREATE TABLE enrollments (
  student_id  text NOT NULL,
  course_id   text NOT NULL REFERENCES course_instructors (course_id),
  PRIMARY KEY (student_id, course_id)
);

When to reach for this:

• Importing CSVs into one wide staging table before production DDL.
• Reviewing whether a new column belongs on a fact table or a dimension table.
• Debugging contradictory duplicates in reports (same ID, different descriptions).

Working Example

BEGIN;
 
-- Starting point: not 3NF
CREATE TABLE employee_assignments_bad (
  employee_id    text,
  department_id  text,
  department_name text,
  department_head text,
  PRIMARY KEY (employee_id, department_id)
);
 
-- 3NF decomposition
CREATE TABLE departments (
  department_id    text PRIMARY KEY,
  department_name  text NOT NULL,
  department_head  text NOT NULL
);
 
CREATE TABLE employee_departments (
  employee_id    text NOT NULL,
  department_id  text NOT NULL REFERENCES departments (department_id),
  PRIMARY KEY (employee_id, department_id)
);
 
INSERT INTO departments (department_id, department_name, department_head)
VALUES ('D1', 'Engineering', 'Alex');
 
INSERT INTO employee_departments (employee_id, department_id)
VALUES ('E100', 'D1');
 
-- Verify: department attributes appear once
SELECT department_id, COUNT(*) FROM departments GROUP BY 1;
 
COMMIT;

What this demonstrates:

• department_name depends on department_id, not on (employee_id, department_id) - transitive dependency removed.
• Reference data (departments) separated from facts (employee_departments).
• Updates to department head touch one row.

Deep Dive

Normal Forms at a Glance

How It Works

• A functional dependency X -> Y means two rows with the same X must have the same Y.
• 3NF: every non-key column depends on the key, the whole key, and nothing but the key.
• BCNF: stricter - for every dependency X -> Y, X must be a superkey.
• OLTP schemas rarely need beyond BCNF; star schemas for analytics intentionally denormalize.

SQL Notes

-- Find duplicate determinants with conflicting values (3NF smell)
SELECT product_id, COUNT(DISTINCT product_name) AS names
FROM order_items_bad
GROUP BY product_id
HAVING COUNT(DISTINCT product_name) > 1;

Gotchas

• Over-normalizing dimensions - 6 joins to render a product card. Fix: denormalize read models or materialized views after measuring pain.
• Ignoring composite keys - partial dependencies hide in (order_id, line_no) tables. Fix: ask what each non-key column depends on.
• BCNF obsession on facts - splitting every dependency can harm clarity. Fix: stop at 3NF unless contradictions appear in data.
• Natural keys as sole PK across tenants - sku alone fails in multi-tenant SaaS. Fix: (tenant_id, sku) composite or surrogate plus scoped UNIQUE.
• Lookup table without FK - zip on customers but no zip_codes table. Fix: add reference table or CHECK against known set.

Alternatives

FAQs

Related

• Normalization Basics - anomaly examples
• Intentional Denormalization - controlled redundancy
• Materialized Views - read-optimized copies
• Fan-Out Join Explosion - bad joins from bad decomposition

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+.