Third Normal Form (3NF)

Third Normal Form (3NF) builds on 2NF by removing a subtler kind of redundancy: transitive dependencies. A table is in 3NF when it is in 2NF and every non-key column depends only on the primary key, directly — not on another non-key column. If a non-key column's value can be derived from a different non-key column instead of from the key itself, that is a transitive dependency, and it is a sign that the derived information belongs in a table of its own.

A table that violates 3NF

Consider a customers table that stores each customer's address, including zip_code and city:

Violates 3NF: city is transitively dependent on zip_code

SQL

CREATE TABLE customers (
  customer_id   INT PRIMARY KEY,
  customer_name VARCHAR(100),
  zip_code      VARCHAR(10),
  city          VARCHAR(100)
);

INSERT INTO customers (customer_id, customer_name, zip_code, city) VALUES
  (1, 'Alice Nguyen', '94103', 'San Francisco'),
  (2, 'Bob Smith',    '10001', 'New York'),
  (3, 'Carol Diaz',   '94103', 'San Francisco'),
  (4, 'Dan Lee',      '94103', 'San Franciso');

The primary key is customer_id, and both zip_code and city depend on it — every customer has exactly one zip code and one city. But city does not depend on customer_id directly; it depends on zip_code, which in turn depends on customer_id. That chain — key → zip_code → city — is a transitive dependency. The data above already shows the damage: zip code 94103 is paired with "San Francisco" for two customers and with the misspelled "San Franciso" for a third. Nothing in the schema stops that inconsistency, because the relationship between a zip code and its city is never stated as a rule — it is just repeated, by hand, in every row.

Fixing it: a lookup table for the transitive fact

The fix is to recognize that "which city does this zip code belong to" is a fact about zip codes, not about customers, and give it its own table.

3NF-compliant: city lives with zip_code, not with the customer

SQL

CREATE TABLE zip_codes (
  zip_code VARCHAR(10) PRIMARY KEY,
  city     VARCHAR(100) NOT NULL
);

CREATE TABLE customers (
  customer_id   INT PRIMARY KEY,
  customer_name VARCHAR(100),
  zip_code      VARCHAR(10),
  FOREIGN KEY (zip_code) REFERENCES zip_codes(zip_code)
);

INSERT INTO zip_codes (zip_code, city) VALUES
  ('94103', 'San Francisco'),
  ('10001', 'New York');

INSERT INTO customers (customer_id, customer_name, zip_code) VALUES
  (1, 'Alice Nguyen', '94103'),
  (2, 'Bob Smith',    '10001'),
  (3, 'Carol Diaz',   '94103'),
  (4, 'Dan Lee',      '94103');

Now "94103 is San Francisco" is stated exactly once. It is no longer possible for two rows to disagree about which city a given zip code belongs to, and looking up the city for any customer is a simple join:

City is derived through the relationship, not duplicated

SQL

SELECT c.customer_name, z.city
FROM customers c
JOIN zip_codes z ON z.zip_code = c.zip_code;

Dependency	Type	3NF verdict
customer_id → zip_code	Direct (key to non-key)	Fine — stays in customers
zip_code → city	Direct, but zip_code is not the primary key	Fine — belongs in its own table keyed by zip_code
customer_id → city (via zip_code)	Transitive	Violation — must not be stored directly in customers

3NF is the practical target for most schemas

In everyday application design, 3NF is generally considered the sweet spot: it eliminates almost all update anomalies while keeping the schema reasonably easy to query, without chasing the more exotic edge cases that higher normal forms like BCNF exist to handle. Most production schemas aim for 3NF and then selectively denormalize specific tables for performance, rather than pushing further up the normalization ladder everywhere.

A table must be in 2NF before 3NF is meaningful.
A transitive dependency exists when a non-key column depends on another non-key column rather than on the key itself.
The fix is to move the transitively-dependent fact into a lookup table keyed by the column it actually depends on.
3NF is the normal form most real-world schemas target in practice.