Third normal form (3NF)

What is third normal form (3NF)?

Third normal form is a level of database design where every non-key column depends on the primary key, the whole key, and nothing but the key. It is the target most well-designed operational databases aim for. Each fact sits in exactly one place, tied directly to the thing it describes.

The phrase "the key, the whole key, and nothing but the key" is the popular summary of the first three normal forms, credited to Bill Kent. Third normal form is the part that handles "nothing but the key": no column may describe another non-key column instead of the key itself.

The idea comes from Edgar F. Codd, who defined the relational model at IBM and set out the early normal forms in the early 1970s. 3NF builds on second normal form, which in turn builds on first.

The rule 3NF removes: transitive dependency

A transitive dependency is when a non-key column depends on another non-key column, which then depends on the key. The dependency reaches the key indirectly, through a detour.

Take an orders table with these columns: order id (the key), customer id, customer city, and customer postcode. Order id identifies the order. But customer city and postcode do not really describe the order. They describe the customer. Postcode depends on customer id, and customer id depends on order id. That detour is the transitive dependency.

The fix is to move the customer details into a customer table and leave only customer id on the order. Now every column on the order describes the order directly. City and postcode live once, on the customer, where they belong.

Why it matters: in the un-normalised version, one customer moving house means updating every order they ever placed. Miss one row and the database holds two postcodes for the same person. In 3NF the postcode changes in one place.

The three forms in order

3NF assumes the two forms below it are already satisfied.

• First normal form. One value per field, no repeating groups. No cramming three phone numbers into a single cell.

• Second normal form. When a table has a combined key, every non-key column depends on the whole key, not just part of it.

• Third normal form. No non-key column depends on another non-key column. Every fact points straight at the key.

A stricter version, Boyce-Codd normal form, tightens 3NF further, but for most business systems 3NF is the practical target.

3NF versus a denormalised warehouse

3NF is built for systems that write data all day: a CRM, an ERP, a booking system. Splitting facts across tables means each update touches one place, so the data stays consistent.

Reporting has the opposite need. Analysts read and summarise huge numbers of rows and rarely write anything back. A pile of joins slows those queries down and makes the model hard to read. That is why warehouses deliberately break 3NF through denormalisation. A star schema copies descriptive values into dimension tables so a report can read customer, product and date without walking a chain of lookups.

Neither is wrong. Keep the operational source in 3NF for safe writing. Build a denormalised model for fast reading. The rule of thumb: normalise where data is written, denormalise where data is read.

What to watch out for with 3NF

3NF is a design target, not a religion. Splitting every small lookup into its own table can make simple queries unreadable. Normalise strictly where values change often, and stay pragmatic where they are stable.

Reporting straight off a 3NF source hurts. A chart that needs fifteen joins is hard to maintain and loads the operational database. Build an analytical copy once reporting becomes serious.

3NF says nothing about what the data means. It removes redundant storage, it does not define what an active customer or a cancelled order is. Those definitions still need an owner.