Why Stable Identifiers Matter
Exposure records are useful only when they can be connected to the policy, term, coverage, or insured item they describe. A policy number may remain the same at renewal, while the term changes. A vehicle’s garaging address or territory may change during a term, while the vehicle remains the same insured item. If a linkage relies on a changeable description instead of a stable identifier, records can be missed or attached to the wrong subject.
The earlier material on joining policy, coverage, and exposure tables establishes that records have different levels of detail. Identifiers make those links precise: a policy identifier identifies a policy, a term identifier distinguishes a particular period of that policy, and coverage and item identifiers distinguish records at their respective levels.
Match the Key to the Record Level
A key must be chosen for the entity represented by a record. A policy-level key can identify a policy, but it cannot by itself distinguish that policy’s different terms, coverages, or insured items. At a more detailed level, the key often includes the identifier of the parent record.
| Record level | Possible key | What it identifies |
|---|---|---|
| Policy | Policy ID | One policy within the issuing system |
| Policy term | Term ID, or policy ID plus term sequence | One specific term of a policy |
| Coverage | Term ID plus coverage ID | One coverage record within a term |
| Insured item | Term ID plus item ID | One insured item within a term |
| Coverage on an insured item | Term ID plus coverage ID plus item ID | One coverage applying to one item within a term |
These are patterns, not universal field names. A system may assign a globally unique ID to every term or coverage, in which case that ID alone may be a sufficient key. Conversely, a field called “coverage ID” may be unique only within one term. The key must be tested against the data model, not inferred from the field label.
A descriptive attribute answers a different question from an identifier. The insured’s name, mailing address, vehicle description, territory, and rating classification describe or characterize a record. Such fields may change, may be shared by multiple records, or may be entered inconsistently. They can be useful for analysis or review, but they should not be treated as stable keys unless the system explicitly defines and controls them as such.
How a Composite Key Establishes a Precise Link
Suppose each coverage-on-item record belongs to exactly one term, one coverage within that term, and one insured item within that term. The tuple \((\text{term ID}, \text{coverage ID}, \text{item ID})\) identifies the record if no two records have the same tuple. The term ID supplies the scope, while the coverage and item IDs distinguish the particular combination inside that scope.
This is the reason for using the full key rather than a convenient subset. If coverage IDs repeat across terms, then coverage ID alone cannot tell which term is intended. If item IDs also repeat across terms, item ID alone cannot resolve the ambiguity either. The composite key works because equality of all its components is required for a match.
Here \(T\) is the term identifier, \(C\) is the coverage identifier, and \(I\) is the insured-item identifier. This key is valid only if the combination is unique at the record level being linked. If a system stores multiple separate records for the same term, coverage, and item—for example, one per transaction—then a transaction or record-sequence identifier may also be needed to identify each row. The correct key depends on what one row represents.
Worked Example: Linking Exposure by Term, Coverage, and Item
A hypothetical insurer stores exposure records for one policy term, identified as T884. Within the term, coverage IDs C-PD and C-LI represent physical damage and liability. Item IDs V17 and V22 identify two insured vehicles. The exposure table contains the following vehicle-year records. Determine the appropriate key and the physical-damage exposure total.
| Term ID | Coverage ID | Item ID | Vehicle-years |
|---|---|---|---|
| T884 | C-PD | V17 | 0.50 |
| T884 | C-PD | V22 | 0.75 |
| T884 | C-LI | V17 | 0.50 |
| T884 | C-LI | V22 | 0.75 |
Step 1: Identify the record level. Each row represents one coverage applied to one vehicle during one term. The natural composite key for that level is \((T,C,I)\), or here \((\text{Term ID},\text{Coverage ID},\text{Item ID})\).
Step 2: Check that the keys distinguish the rows. The two physical-damage rows differ by item ID, and the two rows for V17 differ by coverage ID. All four tuples are distinct, so each exposure row has an unambiguous key in this extract.
Step 3: Select the requested coverage. Physical damage has coverage ID C-PD. Its matching rows are \((\text{T884},\text{C-PD},\text{V17})\) and \((\text{T884},\text{C-PD},\text{V22})\).
Step 4: Add the matched exposure amounts. The total is \(0.50+0.75=1.25\) vehicle-years. As a check, the two liability records total \(0.50+0.75=1.25\), and all four records total \(1.25+1.25=2.50\) vehicle-years.
Method and trap avoided. The method is linkage using the composite key at the coverage-on-item level. Matching on term ID alone would return all four rows, not only physical damage. Matching on item ID alone would also return both coverages for each selected vehicle.
Stable Keys Versus Fields That Can Change
A stable identifier should continue to point to the same entity within its defined scope, even when an endorsement changes one of that entity’s attributes. Stability does not mean that every identifier lasts forever or is globally unique. A term-specific item ID may be stable within a term and replaced at renewal. A policy number may identify a policy across several terms but be insufficient to identify one term. Always state the scope in which an identifier is intended to work.
Commonly changeable fields include named-insured spelling, mailing or garaging address, territory, classification, vehicle description, and coverage limit. Changes in these fields can be important to rating, but do not automatically mean a new entity exists. Conversely, a genuinely new item should receive a distinct item identifier even if its description resembles an item already insured. A description is evidence to review, not a substitute for the system’s entity key.
A transaction ID identifies an event or row of activity, such as an endorsement transaction. It is not necessarily the identifier of the policy term or insured item affected by that event. A linkage may need both: the transaction ID to identify the change record and the policy, term, coverage, or item key to identify what was changed.
Worked Example: Avoiding a False Match at Renewal
A hypothetical policy number 6402 has two terms. The administrative system assigns term IDs PT91 and PT104. In both terms, the collision coverage code is COLL and the item number is 1. The exposure records are 0.40 vehicle-years for PT91 and 0.60 vehicle-years for PT104. Determine the correctly linked total across the two terms and compare it with a linkage made only on policy number.
Step 1: State the row-level key. Each row is a collision exposure for one item in one term. The key is \((\text{term ID},\text{coverage ID},\text{item ID})\). Thus the two records have keys \((\text{PT91},\text{COLL},1)\) and \((\text{PT104},\text{COLL},1)\). They are different term-specific records even though the policy number, coverage code, and item number are the same.
Step 2: Compute the correct total. Include each term’s record once: \(0.40+0.60=1.00\) vehicle-year.
Step 3: Show the policy-only mistake. If a policy-level table contains one row for each term but the exposure records are linked only by policy number 6402, each exposure row can match both term rows. The resulting matched amounts are \(0.40, 0.40, 0.60,\) and \(0.60\), for a misleading total of \(0.40+0.40+0.60+0.60=2.00\) vehicle-years.
Step 4: Correct the linkage. Include the term identifier in the match, and include coverage and item identifiers when the target record is at that more detailed level. The correct total is 1.00 vehicle-year, not 2.00.
Method and trap avoided. This is a term-specific composite-key match. The trap is treating a policy number as if it uniquely identified a policy-term record. It does not when a policy has more than one term.
Linking Identifiers Across Systems
Identifiers created in separate administrative systems may not be directly comparable. One system might call an item A-0081 while another calls the same item N-552. Matching those strings cannot establish that they refer to the same insured item. A controlled crosswalk, also called a mapping table, records the relationship between each source-system identifier and a common identifier. The source-system name is part of the key when source IDs can repeat between systems.
A crosswalk should preserve enough scope to identify the entity unambiguously. For example, a source item number that is unique only within a policy term should be mapped using the system, term, and source item number—not the item number alone. The common identifier should point to the same entity consistently, while the source-specific identifiers remain available for tracing a record back to its origin.
Worked Example: Using a Crosswalk Between Systems
Two hypothetical administrative systems supply non-overlapping exposure records to a common analysis. System L reports item A-0081 with 0.30 vehicle-years and item A-0082 with 0.25 vehicle-years. System N reports item N-552 with 0.40 vehicle-years. A controlled crosswalk maps \((\text{L},\text{A-0081})\) and \((\text{N},\text{N-552})\) to common item E510, and maps \((\text{L},\text{A-0082})\) to E772. Calculate exposure by common item and check the grand total.
Step 1: Retain the source-system scope. The source keys are L/A-0081, L/A-0082, and N/N-552. The system label matters because an identifier from one system is not assumed to be unique across all systems.
Step 2: Apply the crosswalk. L/A-0081 maps to E510, L/A-0082 maps to E772, and N/N-552 maps to E510. The first and third records therefore refer to the same common item; the second refers to a different item.
Step 3: Aggregate the linked amounts. E510 has \(0.30+0.40=0.70\) vehicle-years. E772 has \(0.25\) vehicle-years.
Step 4: Check the result against the input total. The source records total \(0.30+0.25+0.40=0.95\) vehicle-years. The linked totals also equal \(0.70+0.25=0.95\) vehicle-years, so the mapping and aggregation preserve the total.
Method and trap avoided. The method is mapping source-system keys to a common entity identifier before grouping. The trap is adding records by their raw ID strings: that would fail to recognize that A-0081 and N-552 represent the same item.
A Practical Key-Selection Sequence
A disciplined linkage starts by stating what one row represents. Then select identifiers that distinguish records at exactly that level. The following sequence makes the choices explicit and helps separate entity identification from later analysis.
Specify whether each row represents a policy, a term, a coverage, an item, or a coverage-on-item combination.
Use a system-assigned identifier where available. Add parent identifiers when a child identifier is unique only within its parent.
Names, addresses, territories, and descriptions can change; use them to describe or review records, not as presumed stable identifiers.
Retain the source system and source key, then use a controlled crosswalk to connect records to a common entity identifier.
Check that the selected key identifies the intended records and that the resulting matches do not broaden to a parent level unintentionally.
Common Exam Trap
A frequent error is to choose the easiest available field rather than the key that matches the record grain. Policy number alone may be adequate for a policy-level record, but not for a term-level record. A transaction number may identify the change but not the item changed. A vehicle description may look distinctive but can change or be repeated. Before calculating a linked total, write down what one row represents and the complete key for that row.
Check Your Understanding
For each problem, identify the appropriate linkage or compute the requested result.
- A policy has term IDs T31 and T32. Each term has a coverage code GL and item number 4. Exposure is 0.20 vehicle-years for T31 and 0.35 for T32. What key distinguishes the records, and what is their combined exposure? (Answer: Use term ID, coverage code, and item number; combined exposure is 0.55 vehicle-years.)
- A property system assigns location ID 7 within each policy term. What additional field should be included if location ID 7 can recur in different terms? (Answer: Include the term identifier, giving a key such as term ID plus location ID.)
- An endorsement changes an insured item’s territory from 3 to 5 but the system retains the same item ID. Which field should ordinarily link the item across the change: territory or item ID? (Answer: Item ID, scoped to the relevant policy term; territory is a changeable attribute.)
- System A’s source key A-14 and System B’s source key B-88 both map to common item E203. Their non-overlapping exposure records are 0.18 and 0.27 vehicle-years. What is the linked exposure for E203? (Answer: \(0.18+0.27=0.45\) vehicle-years.)
- A term table has two rows for policy P900, one for each term. An exposure row has 0.65 vehicle-years for the second term. If it is matched only on policy number, how many matching term rows can result, and what total would arise if the exposure amount were summed once per match? (Answer: Two matches can result, producing \(0.65+0.65=1.30\) vehicle-years; including the term identifier gives the correct 0.65.)