Normalizing a Real Requirements Document

Learning outcomes

This lesson applies the previous material to a realistic mini-requirements document. Instead of starting from a textbook relation, we begin with stakeholder language, build a deliberately wide draft, identify dependencies and anomalies, then normalize step by step.

Requirements excerpt

A naive wide record

RepairRecord(  work_order_id,  asset_id,  serial_number,  customer_id,  customer_name,  status_code,  status_name,  technician_ids,  technician_names,  technician_roles,  part_ids,  part_skus,  part_descriptions,  quantities,  charged_prices)

This is not even 1NF because technician and part data are repeating collections.

Step 1: establish independent grains

At minimum we can identify:

• one row per Customer;
• one row per Asset;
• one row per WorkOrder;
• one row per Technician;
• one row per WorkOrderAssignment;
• one row per Part;
• one row per PartUsage;
• one row per WorkOrderStatus code.

Step 2: write core dependencies

customer_id -> customer_nameasset_id -> serial_number, customer_idwork_order_id -> asset_id, status_codestatus_code -> status_nametechnician_id -> technician_namesku -> part_id, part_descriptionpart_id -> sku, part_description(assignment_id) -> work_order_id, technician_id, role, started_at, ended_at(part_usage_id) -> work_order_id, part_id, quantity, charged_unit_price

Step 3: 1NF structures

Instead of arrays/lists inside WorkOrder:

WorkOrderAssignment(  assignment_id,  work_order_id,  technician_id,  technician_name,  role,  started_at,  ended_at)PartUsage(  part_usage_id,  work_order_id,  part_id,  part_sku,  part_description,  quantity,  charged_unit_price)

These are structurally 1NF but still redundant.

Step 4: identify partial/transitive facts

Inside WorkOrderAssignment:

\[ technician\_id \rightarrow technician\_name \]

Technician name does not belong to the assignment grain.

Inside PartUsage:

\[ part\_id \rightarrow sku,part\_description \]

Those facts belong to Part.

Step 5: extract entity relations

Customer(customer_id, customer_name)Asset(asset_id, customer_id, serial_number)Technician(technician_id, technician_name)Part(part_id, sku, part_description)WorkOrderStatus(status_code, status_name)

Step 6: keep relationship facts on associations

WorkOrderAssignment(  assignment_id,  work_order_id,  technician_id,  role,  started_at,  ended_at)PartUsage(  part_usage_id,  work_order_id,  part_id,  quantity,  charged_unit_price)

Role and assignment timing depend on the assignment instance. Charged price depends on the usage transaction, not on current Part state.

Step 7: normalize WorkOrder

Bad:

WorkOrder(  work_order_id,  asset_id,  serial_number,  status_code,  status_name)

Dependencies:

work_order_id -> asset_id, status_codeasset_id -> serial_numberstatus_code -> status_name

Normalized:

WorkOrder(work_order_id, asset_id, status_code)Asset(asset_id, ..., serial_number)WorkOrderStatus(status_code, status_name)

Step 8: preserve alternate keys

Technician.employee_number UNIQUEPart.sku UNIQUEAsset(manufacturer_id, serial_number) UNIQUE -- if guaranteedWorkOrder.work_order_number UNIQUE -- if business-defined

Normalization does not replace key design.

Step 9: apply integrity constraints

CHECK (quantity > 0)CHECK (charged_unit_price >= 0)CHECK (ended_at IS NULL OR ended_at >= started_at)FOREIGN KEY (asset_id) REFERENCES asset(asset_id)FOREIGN KEY (status_code) REFERENCES work_order_status(status_code)

Step 10: verify lossless reconstruction

The original reporting view can be rebuilt:

SELECT  wo.work_order_id,  a.serial_number,  c.customer_name,  s.status_name,  t.technician_name,  wa.role,  p.sku,  pu.quantity,  pu.charged_unit_priceFROM work_order woJOIN asset a ON a.asset_id = wo.asset_idJOIN customer c ON c.customer_id = a.customer_idJOIN work_order_status s ON s.status_code = wo.status_codeLEFT JOIN work_order_assignment wa ON wa.work_order_id = wo.work_order_idLEFT JOIN technician t ON t.technician_id = wa.technician_idLEFT JOIN part_usage pu ON pu.work_order_id = wo.work_order_idLEFT JOIN part p ON p.part_id = pu.part_id;

Beware multiplicative joins in reports

If one order has three technicians and four part-usage rows, joining both child collections directly can produce \(3 \times 4 = 12\) rows. The normalized schema is correct; the report query must respect independent child grains, aggregate them separately, or intentionally produce combinations.

Final normalized core

CustomerAssetWorkOrderWorkOrderStatusTechnicianWorkOrderAssignmentPartPartUsage

Each relation has a clear determinant and one primary row grain.

Review the anomalies again

• Changing customer name updates one Customer row.
• Adding a new status requires no fake WorkOrder.
• Deleting the last order using a status does not remove the status definition.
• Part descriptions are not repeated across usage rows.
• Historical charged price remains independent of current catalog price.

Practice: normalize a purchase record

Summary and next lesson

Real normalization begins with grain and business dependencies, not with splitting tables by intuition. By moving through 1NF, 2NF, and 3NF carefully, WorkshopHub arrives at a schema where facts have clear ownership and anomalies are greatly reduced. The final lesson examines why formal normalization is necessary but not sufficient for good database design.

References

• Ramez Elmasri and Shamkant B. Navathe, Fundamentals of Database Systems.
• C. J. Date, Database Design and Relational Theory.
• Thomas Connolly and Carolyn Begg, Database Systems.