Normalization is the process of organizing a relational schema so that each fact is stored in exactly one place, by decomposing tables according to the dependencies between their columns. In data modeling, it is the main technique for removing redundancy and preventing update anomalies in transactional databases.
The method was introduced by Edgar F. Codd together with the relational model and is described as a sequence of normal forms, each stricter than the last. Most production schemas aim for third normal form (3NF) or Boyce-Codd normal form (BCNF), and then denormalize selectively where measured performance requires it.
Why normalization prevents anomalies
A table that stores the same fact in many rows invites inconsistency. If a customer’s address appears on every order row, changing it requires updating every copy, and missing one leaves the database contradicting itself. Normalization removes these copies by moving each fact to a table keyed by the thing it describes.
| Anomaly | What goes wrong | Example in an unnormalized orders table |
|---|---|---|
| Update anomaly | One fact must change in many rows | Customer address repeated on each order |
| Insertion anomaly | A fact cannot be stored without an unrelated one | New customer cannot exist before a first order |
| Deletion anomaly | Removing a row loses an unrelated fact | Deleting the last order erases the customer |
The formal basis is the functional dependency: column B depends on column A if each value of A determines exactly one value of B. Normalization checks whether every dependency points from a key of the table and splits the table where it does not.
The normal forms step by step
Each normal form adds one rule on top of the previous ones. The first three are the practical core; the higher forms address rarer kinds of dependency.
| Normal form | Requirement | Problem it removes |
|---|---|---|
| 1NF | Atomic values, no repeating groups | Lists packed into one column |
| 2NF | No non-key column depends on part of a composite key | Partial dependencies |
| 3NF | No non-key column depends on another non-key column | Transitive dependencies |
| BCNF | Every determinant is a candidate key | Remaining key-related anomalies |
| 4NF | No independent multi-valued dependencies in one table | Cross-product duplication |
| 5NF | No join dependencies beyond candidate keys | Redundancy from three-way relationships |
A common summary of 3NF is that every non-key column must depend on the key, the whole key and nothing but the key. In practice, a schema in 3NF is usually also in BCNF; the two differ only when a table has overlapping candidate keys.
Normalization in relational schema design
Normalization shapes the relationships drawn in an ER diagram. Splitting a repeated group out of a table creates a one-to-many relationship with a foreign key, and resolving a relationship where both sides repeat creates a junction table for a many-to-many relationship.
Normalization is not the same as adding surrogate keys or splitting tables arbitrarily. A decomposition is valid only if it is lossless, meaning the original data can be rebuilt by joining the parts, and ideally dependency-preserving, meaning every rule can still be enforced within single tables.
Normalization versus denormalization
A fully normalized schema optimizes write correctness, while queries may need many joins. Denormalization deliberately reintroduces controlled redundancy to speed up reads, and it is standard in analytical models such as the star schema.
| Aspect | Normalized schema | Denormalized schema |
|---|---|---|
| Primary goal | Integrity and single source of truth | Fast reads and simple queries |
| Redundancy | Minimal | Intentional |
| Write cost | Low, one place to update | Higher, copies must stay in sync |
| Read cost | More joins | Fewer joins |
| Typical workload | OLTP, order entry, accounting | Reporting, dashboards, data warehouses |
| Main risk | Join-heavy queries on large tables | Copies drifting out of sync |
The sound sequence is normalize first, then denormalize with evidence. Redundancy added later should be maintained by the database itself, through triggers, generated columns or materialized views, rather than by application code that may forget to update every copy.
Common mistakes
Most problems come either from stopping too early or from going further than the data needs. The table pairs frequent mistakes with their consequences.
| Mistake | Consequence | Better approach |
|---|---|---|
| Comma-separated lists in a column | Cannot index or join values | Separate child table |
| Storing derived totals without control | Totals drift from detail rows | Compute on read or maintain by trigger |
| Splitting every attribute into its own table | Excessive joins, no integrity gain | Stop at 3NF or BCNF |
| Treating history as an update anomaly | Past invoices change when prices change | Store the price valid at the time |
| Normalizing a reporting warehouse | Slow analytical queries | Use a dimensional model |
The invoice case is a frequent misunderstanding: a price copied onto an order line is not redundancy but a separate fact, the price agreed at the time of sale.
Stripped to the essentials
Normalization means writing each piece of information down only once. Imagine a club that writes a member’s phone number on every attendance sheet. When the number changes, someone must fix dozens of sheets, and some will be missed.
A better system keeps one member list with the phone number and lets the attendance sheets refer to the member by an ID. Databases follow the same rule. One fact, one place keeps the data consistent and easy to update.
Where it is applied
A small training company stores course registrations in one spreadsheet-like table with columns for student name, student email, course title, course price and trainer name. When a trainer changes for a course, staff must edit every registration row, and errors leave the same course with two different trainers.
A developer normalizes the data to 3NF with four tables: students, courses, trainers and registrations, where registrations hold only student ID, course ID, date and the price paid at the time. Updating a trainer now touches one row in the courses table, duplicate emails disappear, and the registration table shrinks to a few narrow columns. Reports join the tables, and a monthly summary view keeps them fast.