Database Normalization

Database normalization is a way to design relational tables around facts and their dependencies. You separate facts that describe different things. You then connect those tables with keys.

The goal is not to create as many tables as possible. The goal is to give each fact one clear home. That structure reduces repeated data and makes changes less likely to produce contradictions.

Imagine one table that records orders, customers, and products:

order_id	customer_id	customer_name	product_id	product_name	quantity
501	C7	Amara Chen	P4	Desk Lamp	2
502	C7	Amara Chen	P9	Cable Tray	1

The customer name repeats for every order line. The product name repeats whenever someone orders that product. A spelling correction must reach every copy. Missing one copy leaves the database with conflicting versions of the same fact.

A normalized design gives each fact a home:

customers stores facts about a customer.
products stores facts about a product.
orders stores facts about an order.
order_lines records which products belong to an order and in what quantity.

The order line still contains identifiers. Those identifiers are not unwanted repetition. They are keys that connect related facts.

The problem normalization solves

Repeated facts create three common anomalies:

Update anomaly: You must change the same fact in several rows. Some copies may remain stale.
Insertion anomaly: You cannot record one fact until an unrelated fact also exists. A product may have nowhere to live until somebody orders it.
Deletion anomaly: Removing one fact accidentally removes the only record of another. Deleting the last order for a product may erase the product description.

Normalization exposes why those anomalies exist. It asks which attributes determine other attributes. It then splits a relation when different facts have different determinants.

Start with keys and dependencies

A candidate key is a minimal set of attributes that uniquely identifies a row. A table can have several candidate keys. You choose one candidate key as the primary key. The others remain alternate keys and usually need uniqueness constraints.

A composite key contains more than one attribute. For example, the pair (order_id, product_id) may identify one order line when a product can appear only once per order.

A functional dependency describes a rule about valid data. Write customer_id -> customer_name when one customer identifier determines one customer name. The left side is the determinant. Dependencies come from the meaning of the data, not from one convenient sample.

That distinction matters. Ten rows with unique names do not prove that a name is a key. You must know whether the business permits two people to share that name.

The first three normal forms

Normal forms are cumulative design conditions. A table must satisfy an earlier form before it can satisfy the next one.

First normal form

First normal form removes repeating groups and gives each row-column intersection one value. You model a variable number of items as rows, not as columns such as product_1, product_2, and product_3.

The meaning of one value depends on the domain. A postal address can be one value when the application always handles it as a whole. Split it when the application needs independently constrained or queried parts. Atomicity is a modeling decision, not a command to split every string into characters.

Second normal form

Second normal form requires first normal form and removes partial dependencies on a composite candidate key. Every non-key attribute must depend on the whole key.

Suppose an order-line table uses (order_id, product_id) as its key and also stores product_name. The dependency product_id -> product_name uses only part of the composite key. Move the product name to a product table keyed by product_id.

If every candidate key has one attribute, partial dependency cannot occur. A table in first normal form is then already in second normal form.

Third normal form

Third normal form requires second normal form and addresses transitive dependencies. A non-key fact should not depend on the key through another non-key fact.

Suppose employees contains employee_id, department_id, and department_name. If employee_id -> department_id and department_id -> department_name, the department name describes the department. Move it to a department table. Keep department_id in the employee table as the reference.

This is the practical center of normalization for many operational schemas. It removes the most common dependency-driven anomalies without requiring every design to pursue advanced normal forms.

Beyond third normal form

Boyce-Codd normal form strengthens the dependency rule. Every determinant must be a candidate key. It catches uncommon cases that can remain in third normal form when candidate keys overlap.

Fourth and fifth normal forms address multivalued and join dependencies. You need them when independent many-valued facts or subtle reconstruction rules still cause redundancy. Learn them after you can identify candidate keys and functional dependencies confidently.

Decomposition must preserve meaning

Splitting a table is useful only when you can reconstruct the intended facts correctly. A lossless decomposition lets a join rebuild the original relation without adding false combinations or losing valid rows.

You should also consider dependency preservation. A decomposition preserves a dependency when the database can enforce it within the resulting tables without joining them. Lossless decomposition is essential. Dependency preservation is highly desirable, but some higher-normal-form designs require a tradeoff.

Keys and foreign keys implement the design. A primary or unique constraint enforces candidate-key uniqueness. A foreign key requires a reference to match an existing row in the referenced table. Constraints turn the data model into rules the database can check.

A repeatable design method

Use this sequence when you review a relation:

State what one row represents.
List the candidate keys, including composite alternatives.
Write the functional dependencies required by the business rules.
Remove repeating groups to reach first normal form.
Remove dependencies on part of a composite key to reach second normal form.
Remove dependencies through non-key attributes to reach third normal form.
Check whether every remaining determinant is a candidate key.
Verify that each decomposition is lossless.
Add primary, unique, foreign-key, and other integrity constraints.
Test representative inserts, updates, deletes, and joins.

Do not begin by drawing arbitrary boxes. Begin with facts, keys, and dependencies. The table boundaries follow from those decisions.

When normalization is not enough

Normalization protects logical structure. It does not choose indexes, transactions, authorization, backup policy, or physical storage. A normalized schema can still be slow, insecure, or difficult to operate.

Normalization also does not freeze the schema forever. Business rules change. A dependency that was once valid may stop being valid, so revisit assumptions when requirements change.

Denormalization is a measured tradeoff

Denormalization deliberately introduces derived or repeated data, often to serve a measured read path. It trades simpler reads for more write coordination and a greater risk of inconsistency.

Start with a design whose facts are clear. Measure the real workload. Try appropriate queries, indexes, caching, or materialized views. Denormalize only when evidence justifies the extra integrity work. Document the source of truth and how every duplicate stays synchronized.

Normalization is therefore a reasoning tool, not a score. A useful design makes dependencies explicit, prevents harmful anomalies, and supports the workload without hiding integrity risks.