MENU


Third Normal Form

Third Normal Form (3NF) builds on Second Normal Form. A table is in 3NF when:

It is already in 2NF.
No non-key column depends on another non-key column – every non-key column depends directly on a candidate key, and only on a candidate key. No transitive dependency.

A common short definition: 3NF requires that every non-key column depend on "the key, the whole key, and nothing but the key."

Symptom: Transitive Dependency

A transitive dependency exists when key → A and A → B, so B depends on the key only indirectly, through A. B ends up duplicated once per row that shares the same value of A, with the same update-anomaly risk as a partial dependency: change B in one place and forget another, and the data disagrees with itself.

The Fix

Move the transitively-dependent column, together with the column it actually depends on, into its own table. The original table keeps a foreign key pointing at the new table instead of the duplicated value itself.

Worked Example

Continuing from Second Normal Form, the orders table produced there still has:

order_id → customer_id → customer_city

customer_city depends on order_id only transitively, through customer_id. Every order placed by the same customer repeats that customer's city.


Before — if a customer moves to a new city, every one of their historical orders must be updated, or the data disagrees about where the customer lives depending on which order row is read.

After — customer_city moves into a customers table keyed on customer_id, and orders keeps only a foreign key to customers. See the worked CREATE TABLE statements below. With products, orders, order_items, and customers all separated out, this schema is now fully in 3NF: every non-key column in every table depends on that table's whole key and nothing else. 

-- BEFORE: violates 3NF (customer_city depends transitively on order_id,
-- through customer_id, rather than directly on order_id)
CREATE TABLE orders_2nf (
    order_id      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id   INT UNSIGNED NOT NULL,
    customer_city VARCHAR(80)  NOT NULL
) ENGINE=InnoDB;

-- A customer moving city requires updating every one of their orders:
-- UPDATE orders_2nf SET customer_city = 'Austin' WHERE customer_id = 42;

-- AFTER: 3NF-compliant. customer_city moved to its own table, keyed on
-- the column it actually depends on (customer_id alone).
CREATE TABLE customers (
    customer_id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_city VARCHAR(80) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE orders (
    order_id    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id INT UNSIGNED NOT NULL,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
) ENGINE=InnoDB;

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

-- A customer moving city is now a single-row UPDATE, reflected instantly
-- for every order they have ever placed:
UPDATE customers SET customer_city = 'Austin' WHERE customer_id = 42;

-- The full 3NF schema, assembled with the products/order_items tables
-- from Second-Normal-Form:
--   customers   (customer_id PK, customer_city)
--   orders      (order_id PK, customer_id FK -> customers)
--   products    (product_id PK, product_name, unit_price)
--   order_items (order_id, product_id PK, quantity; FKs -> orders, products)

SELECT o.order_id, c.customer_city, p.product_name, oi.quantity
FROM order_items oi
JOIN orders o    ON o.order_id = oi.order_id
JOIN customers c ON c.customer_id = o.customer_id
JOIN products p  ON p.product_id = oi.product_id;