Chapter 14 · Time, History, Hierarchies, and Recursive Structures
Slowly Changing Business Data in Operational Systems
Design operational history for business entities whose attributes change over time, including ownership, status, pricing, assignment, and slowly changing master data.
Learning outcomes
Operational systems contain master data that changes slowly but meaningfully: Customer segment, Asset owner, Technician team, Part classification, contract tier, or preferred service center. If you overwrite every change, historical reports can become misleading. If you version everything indiscriminately, the schema becomes difficult to use. This lesson develops practical operational-history patterns.
Decide which master-data changes require history.
Separate current-state access from historical reconstruction.
Model effective versions and avoid overlapping records.
Understand slowly changing dimension concepts without forcing warehouse patterns into OLTP blindly.
Overwrite pattern
UPDATE customerSET segment_code = 'enterprise'WHERE customer_id = 17;This is correct when only current segment matters. It destroys prior segment history.
Versioned operational history
CustomerSegmentHistory( customer_id, segment_code, valid_from, valid_to)Current segment can be obtained from the open interval or denormalized on Customer for fast reads.
Type 1 / Type 2 intuition
Warehouse literature often describes:
- Type 1: overwrite old value;
- Type 2: create a new version row preserving history.
Operational systems can use similar ideas, but transaction semantics and integrity usually matter more than warehouse terminology.
Ownership history
AssetOwnership( asset_id, customer_id, valid_from, valid_to, reason_code)One Asset may change owners many times.
Current-owner shortcut
You may also keep:
Asset.current_customer_idfor fast current-state access, provided updates to ownership history and current owner remain consistent.
Who owns the truth?
Choose one:
- history table is authoritative and current_customer_id is derived;
- Asset is authoritative and history is an audit copy;
- a command transaction updates both as one invariant.
Ambiguity creates drift.
Version row identity
customer_segment_history_idcustomer_idsegment_codevalid_fromvalid_toA surrogate history-row ID can simplify references to a specific version.
Effective dating rules
Define whether:
- intervals can have gaps;
- intervals can overlap;
- future-dated changes are allowed;
- backdating is allowed;
- only one current version can exist.
Future-dated changes
A contract may change next month:
current: [2026-01-01, 2026-09-01)future: [2026-09-01, ...)The schema should allow this if the business requires scheduled changes.
Backdating
Backdating can split an existing interval. This operation is more complex than a normal update because neighboring versions must be adjusted consistently.
Corrections versus business changes
Suppose a Customer name was entered incorrectly. Is correcting the spelling a historical business event, or merely fixing bad data? Different domains answer differently. Model “what changed in reality” separately from “what data-entry error was corrected” when audit requirements demand it.
Do not version every typo unless the business needs it. Do not overwrite true business changes when history matters.
Status history
Status is often better represented as events because order matters:
open -> scheduled -> in_progress -> closedA versioned status interval is also possible when “status at time T” queries dominate.
Price history
PartPriceHistory( part_id, currency_code, unit_price, valid_from, valid_to)But WorkOrder PartUsage should still store the charged price as a transaction fact. Historical price tables answer catalog-price history, not billing history.
Classification history
If Part category changes, historical reports may need either:
- current classification applied retrospectively; or
- classification as of transaction time.
Choose explicitly.
Operational versus analytical history
OLTP history may record exact business transitions. Warehouses may copy those histories into dimension versions optimized for analysis. Do not force analytical surrogate-key practices into operational tables unless they solve an operational need.
WorkshopHub history map
| Attribute | Likely strategy |
|---|---|
| Customer legal name | Current + audit/correction history as required |
| Asset owner | Effective-dated history |
| WorkOrder status | Transition event history |
| Part catalog price | Effective-dated history if required |
| Charged price | Immutable transaction snapshot |
Practice: classify the change
Three changes
- Customer moves from Standard to Enterprise segment.
- Customer's name was misspelled at entry.
- Part catalog price changes next month.
Review answer
Segment change is a real business-history event if historical segmentation matters. The misspelling may be a correction rather than a business version, though audit can still record it. The future price is naturally an effective-dated version.
Summary and next lesson
Operational history requires selective versioning: preserve facts whose past states matter, distinguish corrections from genuine changes, and define effective-time constraints. The next lesson changes direction from time to structure and introduces recursive hierarchies with adjacency lists.
References
- Richard T. Snodgrass, temporal database literature.
- Ralph Kimball and Margy Ross, The Data Warehouse Toolkit.
- Martin Fowler, temporal object patterns.