Composite PatternInteractive Example

Relationship History

Relationship History models associations between business entities that change over time.

Ownership, assignments, memberships and organizational links need their own temporal history whenever historical reporting depends on the relationship that existed at a specific date.

Time-varying entity relationshipsHistorized foreign key associationsTemporal join resolutionMulti-source relationship tracking

Problem

The relationship between two entities can change just like the entities themselves.

Most historical models focus on entities such as customers, contracts, policies or products. However, the relationships between these entities often change over time as well.

A customer may switch advisors. A policy may move to a different broker. An employee may change departments. Historical reporting therefore requires both the correct entity state and the correct relationship state.

Current-state attributionMissing relationship historyOverlapping assignmentsRelationship gaps

Example

A policy moves from Broker A to Broker B during the year.

Policy P123 relationship timeline

Reporting question

Who should receive attribution for an August snapshot: Broker A, Broker B or the broker that happens to be current today?

✓ Expected Result — Historical

Snapshot Date2024-08-31

PolicyP123

BrokerBroker B

× Common Wrong Result — Risk

Snapshot Date2024-08-31

PolicyP123

BrokerBroker A / Current Broker

Snapshot date: August 31

Historical reporting must attribute the policy to Broker B. A current broker field alone cannot answer this reliably.

Core concepts in practice

Relationships need independent temporal semantics.

Relationship history ensures that links between entities remain consistent across time, not only at the current state.

The relationship should usually be represented as a first-class historical object with its own business identity, valid-time interval and, when required, visible-time history.

Test case

Try this Relationship History case

Use these sample target tables to test whether historical attribution uses the correct relationship at the snapshot date.

Select one of the target tables below.
Start an investigation with the example data.
Review the table as the target output.
Check whether August is attributed to Broker B or incorrectly to Broker A.

Historized relationship target

Expected output: the August snapshot is attributed to Broker B.

policy_id,broker_id,snapshot_date,valid_from,valid_to,relationship_status
P123,Broker B,2024-08-31,2024-07-01,2024-12-31,historized_relationship

Wrong target table

Risky output: attribution uses the current or wrong broker.

policy_id,broker_id,snapshot_date,valid_from,valid_to,relationship_status
P123,Broker A,2024-08-31,2024-01-01,2024-12-31,current_relationship_used

Why it happens

Relationships are often treated as simple foreign keys even when they are time-dependent.

Many models store only a current broker, current advisor, current department or current sales organization. This works for current-state reporting but breaks historical attribution.

Once reporting asks who owned, managed or was responsible for an entity at a reporting date, the relationship itself must become historized.

Current foreign keysMissing valid-time periodsOne-to-many changesLate relationship updatesCross-system ownershipHistorical attribution

Common modeling approaches

Model the relationship as its own historized object.

Historized bridge

Store the association between two entities in a bridge table with valid_from and valid_to.

SCD2 relationship

Create a new relationship version whenever the linked entity changes.

Bitemporal tracking

Add visible-time intervals when late updates or corrections may change past attribution.

Attribution rule

Document whether reporting uses event-time, snapshot-date, current corrected truth or as-known attribution.

Validation checks

Validate that each reporting date resolves to the intended relationship.

✓ Detect overlapping relationship assignments✓ Detect required relationship coverage gaps✓ Validate one active relationship where required✓ Check facts against relationship validity✓ Compare attribution with expected ownership✓ Preserve relationship lineage

Why it matters

Historical attribution often depends more on relationship history than entity history.

Commission reporting, portfolio reporting, CRM analytics and organizational KPIs depend on knowing which relationship was active at a specific point in time.

Ignoring relationship history can produce historically incorrect reports even when every entity dimension is perfectly historized.

Related temporal models

Relationship History builds on state modeling and enables correct temporal joins.

State Modeling

Defines how each participating entity changes through valid time.

State-to-State Alignment

Aligns independently changing entity and relationship histories at every relevant boundary.

Identity Resolution

Preserves stable identities when keys, ownership or source-system identifiers change.

Historical Conformance

Checks whether the resolved relationship agrees with the intended historical business meaning.

This pattern is especially important in CRM systems, organizational hierarchies, portfolio ownership and commission attribution.

Related Patterns

Dimension Completion Snapshot Reproducibility State ↔ Event Alignment Historical Coverage Gap State ↔ State Alignment

Try it

Explore relationship history risks in the Workbench.

Use the Historical Modeling Workbench to reason about historized relationships, temporal joins, gaps, overlaps and historical attribution.

Open Historical Modeling Workbench →