What is database normalization? 1NF, 2NF, 3NF, and BCNF explained

A normalized relational database is one whose tables are organized to minimize duplicated data and to prevent the inconsistencies that duplication causes. The process of getting there is called normalization, and it works by applying a sequence of normal forms — progressively stricter rules about how columns may depend on keys. The first three normal forms, 1NF, 2NF, and 3NF, were introduced by Edgar F. Codd in 1970 and 1971; the Boyce–Codd form (BCNF) followed in 1974 from Codd and Raymond F. Boyce; later forms (4NF, 5NF, 6NF) were added in the 1970s and 2000s by Ronald Fagin and C. J. Date to handle subtler kinds of redundancy.

In practice, "normalized" most often means in third normal form (3NF), and most well-designed OLTP schemas stop there. Going further is mainly of academic interest, except in data warehouses and column-store engines where 6NF-style representations are used internally for compression and range-scan speed.

Why normalization matters

Codd stated the objectives of normalization beyond 1NF as: freeing the collection of relations from undesirable insertion, update, and deletion dependencies; reducing the need to restructure relations when new types of data are introduced; making the relational model more informative to users; and making relations neutral to the query statistics that change over time.

The motivation is concrete. Tables that are not sufficiently normalized suffer three classic anomalies:

• Insertion anomaly. Facts cannot be recorded at all unless other, unrelated facts are also present. In a single "Faculty and Their Courses" table, a newly hired professor who has not yet been assigned to teach any course cannot be inserted, because the Course Code column is part of the row and would have to be set to NULL.
• Update anomaly. The same fact appears on multiple rows, and a change has to be applied everywhere. If a professor's office address lives in every row of an "Employees' Skills" table (one row per skill), then a single address change must touch all of that professor's skill rows. If the update is partial, the table gives conflicting answers to "what is this person's address?".
• Deletion anomaly. Deleting one fact inadvertently deletes another. In the Faculty/Courses table, removing the last course a professor teaches also removes the professor from the table — unless the course column is set to NULL, which is its own kind of anomaly.

A fully normalized database can be extended to accommodate new types of data with minimal changes to existing structure, so applications interacting with the database are minimally affected. Normalized relations also tend to mirror real-world concepts and their interrelationships more closely than denormalized ones.

The normal forms at a glance

A normal form is a rule that a table must satisfy. Each form is strictly stronger than the previous one, and the forms are progressive: a table in 3NF is automatically in 2NF and 1NF; a table in BCNF is automatically in 3NF; and so on. The full list, ordered from least to most normalized, is:

• UNF — Unnormalized form (repeating groups or composite values in a column).
• 1NF — First normal form.
• 2NF — Second normal form.
• 3NF — Third normal form.
• EKNF — Elementary key normal form (rarely discussed; sits between 3NF and BCNF).
• BCNF — Boyce–Codd normal form.
• 4NF — Fourth normal form.
• 5NF — Fifth normal form.
• DKNF — Domain-key normal form.
• 6NF — Sixth normal form.

The informal meaning of "the table is normalized" in everyday database work is almost always "the table is in 3NF". C. J. Date has argued that only a database in 5NF is truly "normalized", but the practical convention is 3NF.

First normal form (1NF)

A table is in 1NF when every field contains a single, atomic value — no sets of values, no nested records, no repeating groups. A column like Subject containing MySQL, Database, Design for one row violates 1NF because the column contains multiple values.

The fix is to extract the repeated values into a separate table. If a Book table has a Subject column that holds several subject names, the subjects are moved into a Title – Subject table with one row per (book, subject) pair. The original Book table loses the Subject column, and the relation between books and subjects is now in a separate, properly atomic table.

This is the level at which SQL itself imposes structure: a relational table is, by definition, a set of rows, and a column cannot legally contain a sub-relation.

Second normal form (2NF)

A table is in 2NF when it is in 1NF and every non-key column depends on the whole of every candidate key, not just part of it. 2NF only matters when the candidate key is composite — a primary key made of two or more columns.

Consider a Book table with a composite key of {Title, Format} and a Price column. Price depends on Title and Format (the same title in hardcover costs more than the same title in e-book). But other columns, like Author, Pages, or Publisher, depend on Title alone. Because those non-key columns depend on only part of the candidate key, the table is not in 2NF.

The standard fix is to split: keep Title, Author, Pages, Publisher, … in the Book table (keyed on Title alone), and move Price into a separate Price table keyed on {Title, Format}. After the split, every non-key column in each table depends on the whole key of its own table.

In databases where every table is keyed on a single column (a synthetic id, for example), 2NF violations are usually impossible by construction — a non-key column trivially depends on a single-column key. This is one reason synthetic keys are so common in modern schemas: they make 2NF concerns moot.

Third normal form (3NF)

A table is in 3NF when it is in 2NF and no non-key column depends on another non-key column. This is the rule that removes transitive dependencies.

After the 2NF split, the Book table has Title as its key, but Author Nationality depends on Author, and Author depends on Title. That is a transitive dependency: Title → Author → Author Nationality. The same transitive chain exists for Publisher Country (depends on Publisher, which depends on Title) and for Genre Name (depends on Genre ID, which depends on Title).

To reach 3NF, those transitively-dependent columns are pulled out into their own tables:

• Author (Author, Nationality)
• Publisher (Publisher, Country)
• Genre (Genre ID, Name)

The Book table is left with Title, Author, Pages, Thickness, Publisher, Genre ID — only columns that depend on Title directly. After the split, changing a publisher's country is a one-row update in the Publisher table, not a sweep across the Book table; a new genre can be added without inserting a new book; and a book can be inserted before its author is recorded by leaving Author NULL (or empty), without losing the book record itself.

Informally, a relational database is often described as "normalized" if it meets 3NF, and most 3NF relations are free of insertion, update, and deletion anomalies.

Boyce–Codd normal form (BCNF)

BCNF is a slightly stricter version of 3NF. A table is in BCNF if and only if, for every one of its functional dependencies X → Y, either X → Y is trivial (Y is a subset of X) or X is a superkey for the table. Since BCNF is strictly stronger than 3NF, every BCNF table is automatically in 3NF, but not every 3NF table is in BCNF.

The classical example is a "Today's court bookings" table for a tennis club with one hard court (Court 1) and one grass court (Court 2). Each row stores the booking plus a Rate type (SAVER, STANDARD, PREMIUM-A, PREMIUM-B), where each rate type implies a specific court and a specific member/non-member flag. The candidate keys of the table are {Court, Start time}, {Court, End time}, {Rate type, Start time}, and {Rate type, End time}. All four attributes are prime (each belongs to some candidate key), so 2NF and 3NF are both satisfied.

The table is not in BCNF because of the dependency Rate type → Court. Rate type is not a superkey (a single rate type can be in many rows), and Court is not a subset of Rate type. The fix is to split the table into a Rate types table (keyed on Rate type, with the implied Court and member flag) and a Today's bookings table that no longer carries the redundant Rate type → Court information. Both resulting tables are in BCNF.

BCNF has a known cost: not every non-BCNF table can be decomposed into BCNF tables and preserve all the functional dependencies of the original. Beeri and Bernstein showed in 1979 that a set of functional dependencies such as {AB → C, C → B} cannot be represented by any BCNF schema. In practice, this is rare, but it explains why some teams stop at 3NF even when BCNF is theoretically reachable.

BCNF also has a computational footnote: it is NP-complete, given a database schema in 3NF, to determine whether the schema actually violates BCNF. The check is cheap for any one schema but the worst case is hard.

Beyond 3NF: 4NF, 5NF, 6NF

The higher normal forms address redundancy that 3NF and BCNF do not catch.

Fourth normal form (4NF) removes redundancy caused by independent multivalued dependencies in the same table. The Wikipedia 4NF example has a Franchisee – Book – Location table where each franchisee offers certain books in certain locations; the combination is not constrained — a franchisee may carry any subset of books in any subset of locations. The table has no non-key attributes and is therefore in BCNF, but the same (Franchisee ID, Title) pair appears with every location, and the same (Franchisee ID, Location) pair appears with every title. Decomposing into Franchisee – Book and Franchisee – Location removes the redundancy and the table is in 4NF.

Fifth normal form (5NF) removes redundancy that surfaces only when a table can be reconstructed losslessly from a join of three or more projections, but not from any two of them. The classic example, again from the Wikipedia article, involves a supplier / book / franchisee relationship where the constraint is that a supplier supplies a title to a franchisee if and only if the supplier supplies the title, the title is supplied to the franchisee, and the franchisee is supplied by the supplier. Without that constraint, the three-way join produces spurious rows; with the constraint encoded, the table cannot be decomposed into two tables without losing information, and is already in 5NF.

Sixth normal form (6NF) is the strictest in practical use: "a table is in 6NF when the row contains the Primary Key, and at most one other attribute". Every non-key column is isolated in its own table, joined back to the key when needed. This is rarely used in OLTP because of the proliferation of tables and the need to update many tables for a single conceptual change, but in data warehouses — Sybase IQ, Microsoft SQL Server columnstore indexes, and similar systems — an internal 6NF representation is exactly what a columnar store does. Each column is stored separately, queries that scan a single column read only that column's data, and range filters on a low-cardinality column benefit from the compression that isolated storage enables.

Codd's progressive property still holds: 6NF implies 5NF, 5NF implies 4NF, 4NF implies BCNF, BCNF implies 3NF, 3NF implies 2NF, and 2NF implies 1NF. The practical sweet spot for most transactional systems is 3NF or BCNF; analytics workloads often deliberately denormalize back toward 1NF, or even use a star schema, which knowingly violates 2NF/3NF for read performance.

Denormalization: deliberately breaking the rules

Denormalization is the strategy of adding redundant copies of data to a previously normalized schema in order to improve read performance, at the cost of write performance and consistency overhead. Wikipedia frames it as a read-vs-write trade-off: a normalized design often has to join many tables to answer a single question, and joins are expensive; denormalization adds precomputed or copied data so a question can be answered from one table.

There are two broad approaches:

• DBMS-managed denormalization. The database engine stores redundant copies and keeps them in sync. SQL Server's indexed views and Oracle/PostgreSQL's materialized views are the standard examples. The logical schema stays normalized; the physical layout does not.
• Designer-managed denormalization. The database administrator adds the redundancy directly to the schema (extra columns that could be derived from a join, replicated lookup values, precomputed counts). The DBMS does not keep these copies in sync; the application or a trigger has to, which adds a new class of bugs to manage.

A denormalized data model is not the same as a data model that was never normalized. Denormalization only helps if it is applied on top of a schema that was at least 3NF, with the resulting anomalies and constraints accounted for. The data-warehouse use case is the canonical one: heavy analytical reads, infrequent writes, and the operational discipline to keep the denormalized views consistent.

FAQ

What does it mean for a database to be normalized?

A database is normalized when its tables satisfy the rules of the normal forms — most commonly up to 3NF or BCNF. A normalized schema stores each fact in one place, so updates do not have to be made in many rows, and inserts or deletes cannot accidentally lose unrelated facts. The opposite is denormalized, where redundant copies of the same fact are stored in multiple places to make reads faster.

What is the difference between 1NF, 2NF, and 3NF?

1NF requires every column to hold a single, atomic value — no sets, no nested records. 2NF requires that, on top of 1NF, every non-key column depends on the whole of every candidate key (no partial key dependencies). 3NF requires that, on top of 2NF, no non-key column depends on another non-key column (no transitive dependencies). Each form is strictly stronger than the previous, so a 3NF table is automatically in 2NF and 1NF.

Why is 3NF usually the stopping point?

Most OLTP schemas aim for 3NF because most insertion, update, and deletion anomalies disappear once transitive dependencies are removed, and 3NF is much cheaper to reason about and maintain than BCNF or the higher forms. Higher normal forms (4NF, 5NF, 6NF) handle subtler kinds of redundancy — multivalued dependencies, join dependencies, isolated columns — that rarely appear in transactional data models, so the additional complexity is not usually worth it for an OLTP workload.

What is the difference between 3NF and BCNF?

BCNF is a stricter form of 3NF: a table is in BCNF if every non-trivial functional dependency starts with a superkey. A 3NF table can violate BCNF when there are overlapping candidate keys and a non-key dependency follows from one of them, as in the tennis-club "Rate type → Court" example. The fix is to split the table so the non-superkey determinant becomes a key in its own table. Some non-BCNF tables cannot be decomposed into BCNF and preserve all original functional dependencies; in those cases 3NF is the practical ceiling.

When should you denormalize a database?

Denormalize when the workload is read-heavy and the cost of repeated joins is the bottleneck, especially in reporting and analytics use cases. The most common pattern is to keep the normalized schema as the source of truth and expose denormalized views (materialized views, indexed views, or pre-aggregated tables) that the DBMS keeps in sync. Manual denormalization is also possible but adds consistency-management overhead: every redundant copy must be updated whenever the underlying data changes, or the database will quietly return inconsistent answers.

Is database normalization the same as SQL?

No. Normalization is a property of a schema (the table definitions, keys, and relationships). SQL is the language used to query a schema. You can run SQL against a denormalized schema, and you can use a non-SQL system to store a normalized schema. The two are independent — the choice of query language does not determine the schema's normal form, and the schema's normal form does not dictate the query language.