Normalization of Reference and Classification Data

Learning outcomes

Reference and classification data—statuses, categories, countries, currencies, failure codes, priority levels, role types—often looks simple but can become one of the most inconsistent parts of a system. Normalization helps, but careless lookup-table design can be just as problematic as duplication.

Reference data

Reference data defines controlled values used to classify other records. Examples include:

• WorkOrderStatus;
• Currency;
• Country;
• FailureCategory;
• TechnicianRole;
• Priority.

Reference versus master data

Customer and Part are usually master data: they represent major business entities with independent lifecycle. WorkOrderStatus is reference data: it provides a controlled classification system used by transactions.

Why normalize reference data?

Bad design:

WorkOrder(  status_code,  status_name,  status_color,  status_is_terminal)

Every WorkOrder repeats reference metadata.

Normalized status reference

WorkOrderStatus(  status_code PRIMARY KEY,  display_name,  is_terminal,  sort_order)WorkOrder(  ...,  status_code REFERENCES WorkOrderStatus)

When a CHECK is enough

If Priority is permanently limited to:

low, normal, high, urgent

and no metadata is attached, a CHECK may be simpler than a reference table.

When a table is justified

Use a reference relation when values have:

• display labels;
• translations;
• sort order;
• activation/deprecation dates;
• parent/child classification;
• external standard mappings;
• administrative ownership.

The generic lookup-table anti-pattern

A tempting design is:

LookupValue(  lookup_type,  code,  label,  value1,  value2,  value3)

This centralizes everything but sacrifices semantic typing, foreign-key clarity, and domain-specific constraints.

Duplicate taxonomies

Different teams may create:

failure_codefailure_typefault_categoryrepair_reason

with overlapping meaning. Normalization cannot fix semantic duplication unless the organization agrees on authoritative vocabularies.

Classification hierarchies

FailureCategory may form a tree:

A normalized adjacency-list structure might use:

failure_category_idparent_failure_category_idcodedisplay_name

Do not duplicate path labels casually

Storing both parent_id and full path text can be useful for performance, but then the full path is derived/denormalized and needs a maintenance rule.

Many-to-many classification

If a Part can have many Categories and a Category contains many Parts:

PartCategory(  part_id,  category_id)

Do not store category IDs as a comma-separated list.

Classification attributes may belong to the association

Suppose a Part belongs to a Category with a category-specific display order. Then display_order belongs to PartCategory, not Part or Category independently.

External standards

Country and Currency codes often come from external standards. Preserve:

• authoritative code;
• version/effective dates when relevant;
• local display label separately;
• mapping to legacy/internal codes when needed.

Deprecating reference values

Historical rows may reference a status or category that should no longer be selected for new records. Instead of deleting the reference row, use:

active_fromactive_tois_selectable

This preserves history while controlling future use.

WorkshopHub reference-data choices

Practice: choose the representation

Summary and next lesson

Reference data should be normalized around authoritative codes and meaningful metadata without creating generic lookup-table sprawl. The final lesson of Chapter 9 synthesizes the chapter: how far should you normalize in a real production system?

References

• David C. Hay, Data Model Patterns.
• C. J. Date, Database Design and Relational Theory.
• Steve Hoberman, Data Modeling Made Simple.