Normalization: 1NF, 2NF, 3NF
One messy table, split one rule at a time. Each split removes a specific kind of redundancy — and a specific kind of bug.
Stage
The raw table: one row per order, a comma-separated list of products.
The Table(s) At This Stage
—Redundancy
Overview
Suppose a table repeats a department's address on every employee row. If the address changes, every row must be updated; missing one creates inconsistent data. The design also makes it awkward to record a department before it has employees. These are update, deletion, and insertion anomalies.
Why split a table
Normalization uses relational structure and keys to reduce that duplication. It is not a requirement to split every table as far as possible: the goal is a clear model whose updates preserve the intended facts.
The practical meaning of common normal forms
First normal form (1NF) is commonly taught as keeping each column value atomic for the operations the database must perform, rather than storing repeating lists in one field.
Second normal form (2NF) requires 1NF and that non-key attributes depend on the whole candidate key, not just part of a composite key. It matters when a relation has a multi-column key.
Third normal form (3NF) removes transitive dependencies: a non-key attribute should describe the key, the whole key, and nothing but the key. For example, store department_name with a department record rather than repeating it in every employee row.
These summaries are useful design tests, not a substitute for formal functional-dependency analysis. Start by identifying candidate keys and what each row represents.
A small split
Model employees(employee_id, department_id, name) and departments(department_id, name, address). The department's address now has one authoritative row, and department_id links employees to it. A foreign-key constraint can enforce that reference; see primary and foreign keys.
When denormalizing can help
For a measured read bottleneck, systems sometimes duplicate or precompute data to reduce joins. Treat the duplicate as a consistency responsibility: define which value is authoritative, how copies are updated, and how they are repaired. Do not denormalize speculatively. See star schema for an analytics-oriented design.
Find the dependency before splitting
Before separating columns, state the functional dependency: for example, department_id determines department_name and department_address. If those department attributes are stored in each employee row, they depend on department_id rather than employee_id, which creates duplicated facts. Moving them to a department table gives the dependency one home. Do not split columns merely because they look related; identify the key and the business fact each row represents. Constraints then protect the relationship after the split.
Further reading
- Codd's original relational model paper introduced the foundations behind relational design.
- PostgreSQL table constraints documents keys used to express normalized relationships.