Chapter 08 · Normalization: First, Second, and Third Normal Forms
First Normal Form and Atomic Design
Understand First Normal Form as a discipline of scalar row values, explicit row grain, and eliminating repeating groups rather than as a simplistic rule about strings.
Learning outcomes
First Normal Form (1NF) is often summarized as “one value per cell,” but that phrase is only a beginner approximation. The deeper idea is that a relation has a well-defined row grain, attributes draw values from domains, and repeating groups are represented as rows rather than hidden lists of values inside one row.
Explain the practical meaning of 1NF in a relational design.
Recognize repeating groups and numbered-column designs.
Distinguish atomicity from “short” or “indivisible in the real world.”
Transform multivalued data into relations with clear grain and keys.
What 1NF is trying to prevent
Consider a bad Customer table:
Customer( customer_id, legal_name, phone1, phone2, phone3)The phone concept repeats horizontally. The schema imposes an arbitrary maximum of three phones and makes queries awkward.
Repeating groups become rows
Customer( customer_id, legal_name)CustomerPhone( customer_phone_id, customer_id, phone_number, phone_type)Now one phone is one row and the model naturally supports zero, one, or many phone numbers.
Comma-separated values are still repeating groups
phone_numbers = '+49..., +98..., +1...'This merely compresses multiple logical values into one string. It prevents normal foreign-key enforcement, indexing by individual phone, and reliable updates.
When the application needs to address individual members of a collection, represent those members structurally rather than hiding them inside a delimited value.
Atomic does not mean physically indivisible
A value is atomic relative to the model and operations. A full name may be atomic in one system if no subcomponents are ever queried. In another system, given name, family name, and honorific may need separate domains.
Address example
A postal address can be represented as one formatted text value or as several columns. 1NF does not demand a universal answer. The design question is whether the DBMS should treat the value as one domain value or whether subparts have independent meaning and operations.
Arrays and JSON need careful interpretation
Modern relational databases support arrays and JSON. Their existence does not automatically violate relational design, but using them for data that requires relational constraints, joins, uniqueness, or independent lifecycle can weaken the model.
For example, an immutable JSON snapshot of an external response may be appropriate. A JSON array of foreign-key IDs that the system must validate and query relationally is much harder to justify.
Row grain comes first
Suppose one row in WorkOrder is intended to mean one work order. Then storing:
technician_ids = [17, 23, 44]hides a separate many-to-many grain. WorkOrderAssignment should represent one technician's participation in one work order.
1NF and keys
Once repeating groups become rows, those rows need identity or uniqueness rules. For CustomerPhone:
UNIQUE (customer_id, phone_number)may be appropriate if a customer should not store the same normalized phone twice.
Bad table: PartUsage with repeated parts
WorkOrder( work_order_id, part1_id, part1_qty, part2_id, part2_qty, part3_id, part3_qty)This repeats a two-column group. Normalize to:
PartUsage( part_usage_id, work_order_id, part_id, quantity)Why 1NF improves querying
With rows, relational operations become natural:
SELECT work_order_id, SUM(quantity)FROM part_usageGROUP BY work_order_id;No dynamic parsing or numbered-column logic is required.
1NF does not remove all redundancy
This table may be in 1NF:
Enrollment( student_id, course_id, student_name, course_title, grade)Each cell is scalar and no repeating group exists, yet student and course facts are duplicated. 2NF and 3NF address those dependency problems.
Surrogate IDs do not create 1NF
Adding id INTEGER PRIMARY KEY to a row containing phone1, phone2, and phone3 does not fix the repeating-group design.
WorkshopHub 1NF review
| Concept | Bad representation | Relational representation |
|---|---|---|
| Customer phones | phone1, phone2, phone3 | CustomerPhone rows |
| Work-order technicians | comma-separated IDs | WorkOrderAssignment rows |
| Parts used | part1/qty1, part2/qty2 | PartUsage rows |
| Status history | status_history text blob | WorkOrderStatusHistory rows |
Practice: normalize the repeating group
Supplier contacts
Supplier( supplier_id, name, contact_name_1, contact_email_1, contact_name_2, contact_email_2)Transform this to a 1NF design and state the row grain of each relation.
Review answer
Supplier has one row per supplier. SupplierContact has one row per contact method/person associated with one supplier, for example supplier_contact_id, supplier_id, contact_name, contact_email. Additional rules determine whether contact person and contact method should themselves be separate entities.
Summary and next lesson
1NF gives the relation a clean structural foundation: explicit row grain, domain values, and repeating facts represented as rows. The next lesson applies 2NF to relations with composite candidate keys and removes dependencies on only part of those keys.
References
- E. F. Codd, foundational papers on the relational model and normalization.
- C. J. Date, Database Design and Relational Theory.
- Ramez Elmasri and Shamkant B. Navathe, Fundamentals of Database Systems.