Third normal form (3NF)
Third normal form (3NF) is a database design rule requiring a table to already be in second normal form and to have no transitive dependencies — every non-key column must depend only on the primary key.
Overview
Third normal form is the third step in Codd's sequence of normal forms, and the level most operational database schemas target in practice. A table is in 3NF when:
- It is already in second normal form (1NF, plus every non-key column depends on the entire primary key, not just part of a composite key).
- It has no transitive dependencies — no non-key column depends on another non-key column instead of depending directly on the key.
The classic example: an orders table with columns order_id, customer_id, customer_city, and customer_state. Here customer_city and customer_state don't depend on order_id (the key) — they depend on customer_id, a non-key column. That's a transitive dependency, and it violates 3NF, because updating a customer's city means hunting down and fixing every order row for that customer.
Example: fixing a 3NF violation
Copy code
-- Violates 3NF: customer_city depends on customer_id, not order_id
CREATE TABLE orders_bad (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER,
customer_city VARCHAR,
amount DECIMAL(10,2)
);
-- 3NF: split out the transitively dependent columns
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
city VARCHAR
);
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customers(customer_id),
amount DECIMAL(10,2)
);
After the split, customer_id's city is stored exactly once. Update it in customers and every order automatically reflects the correct value — no anomaly possible.
Why it matters
3NF removes the update, insert, and delete anomalies that plague denormalized transactional schemas: you can't have inconsistent duplicates of a fact, and you can't lose data about an entity just because its last related row was deleted. It's the design target for most OLTP systems.
DuckDB angle
DuckDB enforces the standard relational constraints (PRIMARY KEY, FOREIGN KEY) needed to build and query a 3NF schema, but as with normalization generally, 3NF is a source-system and OLTP concern. Warehouses built on DuckDB or MotherDuck typically ingest 3NF data and deliberately denormalize it back into star schemas for analytical speed.
Related terms
Data normalization is the process of structuring relational tables to reduce data redundancy and prevent update anomalies, typically by applying a sequence of rules called normal forms.
primary key →A primary key uniquely identifies each database row. Learn SQL definitions, examples, UUID vs auto-increment best practices, and how to fix constraint errors.
Composite key →A composite key is a primary key made up of two or more columns whose combined values uniquely identify a row, used when no single column is unique on its own.
foreign key →A foreign key links one table to another by referencing its primary key. Learn how foreign keys enforce referential integrity, with SQL syntax, composite key examples, and data warehousing use cases.
UPDATE statement →The UPDATE statement modifies existing rows in a table, setting one or more column values for rows that match an optional WHERE condition.
OLTP →OLTP (Online Transaction Processing) refers to systems designed for fast, high-concurrency, small read/write transactions — like inserting an order or updating an account balance — typically backed by normalized relational schemas.
FAQS
A transitive dependency occurs when a non-key column depends on another non-key column rather than on the primary key directly. Third normal form requires removing these by moving the dependent columns into their own table.
