Why the Join Structure Matters
Exposure calculations are often stored in more than one table. A policy table may hold attributes shared across the contract, a coverage table may hold the terms and exposure basis for each coverage, and an exposure-interval table may hold the portions of coverage observed during particular time spans. The actuarial task is not simply to combine columns: it is to preserve what each row represents.
The target for this tutorial is one output record for each exposure interval. Each record should carry the correct policy attributes, the correct coverage attributes, and the exposure information for that particular interval. If the join uses a key that is too broad, a policy’s attributes or exposure may be repeated across unrelated coverages. If it is too narrow or inconsistent, valid records may fail to match.
This basic relational structure supports exposure analysis aligned with the CAS Exam 5 (Basic Ratemaking and Estimated Claim Liabilities) syllabus. It assumes the policy, coverage, and interval records have already been assembled; the focus here is how to connect them without changing their intended grain.
Three Tables, Three Grains
A simple data structure has three levels. A policy table has one row per policy. A coverage table has one row per coverage on a policy. An exposure-interval table has one row per interval for a particular coverage. The fields at each level should be stored at the level they describe.
| Table | One row represents | Typical key | Examples of fields |
|---|---|---|---|
| Policy | One policy | policy_id | State, policy effective date, policy expiration date |
| Coverage | One coverage on one policy | policy_id + coverage_id | Coverage type, deductible, coverage-specific exposure basis |
| Exposure interval | One interval for one coverage | policy_id + coverage_id + interval_id | Interval dates, exposure amount, interval-specific rating attributes |
The keys shown are an example of a workable design, not universal field names. For instance, a coverage identifier may be unique across the entire data set, or it may only be unique within a policy. If it is only unique within a policy, the pair of policy and coverage identifiers is needed to identify a coverage row. The key must reflect what is actually unique in the data.
A policy can have several coverages, and a coverage can have several exposure intervals. The relationship is therefore one-to-many from policy to coverage and one-to-many from coverage to interval. To produce interval-level records, start from the interval table, match each interval to its coverage using the coverage key, and then match that coverage to its policy using the policy key.
The Join in Two Steps
Suppose the tables use the following keys. The policy table’s primary key is policy_id. The coverage table is identified by the pair (policy_id, coverage_id). Each exposure-interval row carries those two fields plus an interval_id. The interval row’s policy and coverage fields are foreign keys to the corresponding parent records.
Each row already represents the exposure interval to retain in the output. Preserve its interval identifier, dates, exposure, and interval-specific attributes.
Match on both policy_id and coverage_id when that pair identifies a coverage row. This carries over coverage terms and the relevant coverage-specific basis.
Match the resulting records to the policy table on policy_id. This adds attributes shared by the policy without altering which coverage or interval the row represents.
Each source interval should correspond to exactly one output row. If one interval appears more than once, inspect whether a join matched multiple parent rows or used an incomplete key.
The order describes the logic of the relationship; a database system may execute the operations differently. What matters is that the join conditions are complete and that the result retains the interval-level grain.
Worked Example: Adding Policy and Coverage Attributes
A hypothetical insurer stores one policy, two coverage records, and three exposure intervals. The policy table contains policy \(P701\), with state North. The coverage table contains \((P701, A)\), liability, and \((P701, B)\), property. The interval table is shown below.
| policy_id | coverage_id | interval_id | Exposure |
|---|---|---|---|
| P701 | A | 1 | 0.50 vehicle-years |
| P701 | A | 2 | 0.25 vehicle-years |
| P701 | B | 1 | $120,000 insured value-years |
Produce one record per interval with state and coverage type. Method: interval-first relational join. The method preserves the interval table’s three rows, matches coverage on \((policy_id, coverage_id)\), and then matches policy on \(policy_id\).
Step 1: Identify the interval rows. There are three: \((P701,A,1)\), \((P701,A,2)\), and \((P701,B,1)\). These are the three records the output should contain.
Step 2: Match coverage. The first and second rows match the liability record \((P701,A)\); the third matches the property record \((P701,B)\). The distinct coverage identifier prevents the property interval from inheriting the liability label.
Step 3: Match policy. All three rows match policy \(P701\), so each receives state North.
The output is therefore: \((P701,A,1,\text{North},\text{liability},0.50)\), \((P701,A,2,\text{North},\text{liability},0.25)\), and \((P701,B,1,\text{North},\text{property},\$120{,}000)\). The two liability intervals together have \(0.50+0.25=0.75\) vehicle-years; the property interval retains its separate insured-value basis. There are three output records, exactly matching the three input intervals. Common mistake avoided: treating all records under policy P701 as though they share one coverage type or one exposure basis.
Why Complete Keys Prevent Extra Rows
A join returns a match for every pair of rows satisfying its condition. If a key does not uniquely identify the intended parent record, one row can match multiple rows. That creates a one-to-many or many-to-many result where one-to-one matching was intended. The resulting row count can grow, and copied exposure values may then be mistaken for additional exposure.
For example, if coverage_id is only unique within a policy, joining on coverage_id alone does not fully identify a coverage. Conversely, joining only on policy_id when attaching coverage attributes matches every interval to every coverage on that policy. The required join condition follows the key of the table being attached: use the full coverage key for coverage attributes and the policy key for policy attributes.
The conditions are essential. If a foreign key has no matching parent, the interval cannot receive the required parent attributes. If a key matches multiple parent rows, the interval may be repeated. The one-row-per-interval conclusion holds only when the stated matching relationships are valid and the join uses the correct keys.
Worked Example: Finding and Correcting a Fan-Out Join
A hypothetical commercial policy has two coverage rows: coverage \(L\) for liability and coverage \(P\) for property. Its interval table contains one interval for each: \(L\) has 0.40 exposure units and \(P\) has 0.60 exposure units. A policy table has one row for the policy. An analyst joins the interval table to the coverage table using only policy_id.
Method: compare the join condition with the coverage key. The coverage key is \((policy_id, coverage_id)\), not policy_id alone. Joining only on policy_id matches each interval to both coverage rows.
Step 1: Count matches. The liability interval has two matching coverage rows, and the property interval also has two. Thus the join creates \(2+2=4\) rows rather than the intended two.
Step 2: See the exposure effect. If the 0.40 value is copied to both matches, the apparent liability-interval exposure is \(0.40+0.40=0.80\). If the 0.60 value is copied to both matches, the apparent property-interval exposure is \(0.60+0.60=1.20\). Across the four joined rows, the copied exposure sums to \(0.80+1.20=2.00\), although the source intervals total \(0.40+0.60=1.00\).
Step 3: Correct the join. Match intervals to coverages on both policy_id and coverage_id. The liability interval then matches only \(L\), and the property interval only \(P\). Each interval appears once, and the exposure total remains \(0.40+0.60=1.00\). Then attach the single policy row using policy_id.
The corrected output has two rows and the original interval exposures. Common mistake avoided: assuming that a policy-level match is sufficiently specific to identify a coverage-level record.
Keeping Attributes at the Right Level
A join is reliable only if each field is understood in relation to the table grain. A state field stored once per policy is a policy attribute. A deductible stored separately for each coverage is a coverage attribute. An exposure value measured for a particular portion of coverage belongs on the interval record. Carrying a field into the output does not make it applicable to every coverage or interval; its meaning comes from the record it matched.
The interval may also carry rating attributes that vary within a policy, such as a territory assigned to that interval. In that case, retain the interval’s value rather than replacing it with a single policy-level value. Likewise, do not sum unlike exposure bases merely because they appear together in one output table. Vehicle-years and insured-value-years remain different measures.
For a simple interval-level result, a practical output layout is:
- Policy identifiers and attributes: fields shared by the policy, attached through the policy key.
- Coverage identifiers and attributes: fields specific to a coverage, attached through the full coverage key.
- Interval identifier and fields: the dates, exposure amount, and any interval-specific attributes that define the output row.
These are structural principles, not a substitute for confirming the field definitions in a particular data system. If a field’s meaning is unclear, its table location alone is not proof of the intended use.
Common Mistakes and Practical Checks
- Joining at policy level when coverage level is needed. Match the interval to coverage using the complete coverage key, not just the policy identifier.
- Assuming a field name guarantees uniqueness. Determine whether an identifier is unique on its own or only together with another field.
- Starting from a table that changes the intended grain. For one record per interval, retain the interval rows as the base of the output.
- Combining unlike exposure bases. Preserve the type and unit of each coverage’s exposure rather than treating all numeric values as interchangeable.
- Checking only the total row count. A correct total count does not guarantee that attributes are attached to the right intervals. Inspect example matches as well as counts.
A useful check is to compare the number of output records with the number of input intervals and confirm that each interval identifier occurs once. Also inspect representative joined rows to verify that the coverage and policy attributes match their keys. These checks assess the join’s structure; separate data-quality procedures address invalid dates, missing values, duplicate records, or exposure reconciliation.
Worked Example: Joining Several Intervals Without Mixing Exposure Bases
A hypothetical policy \(Q88\) has one policy attribute, state South, and two coverages. Coverage \(C1\) is equipment breakdown with an exposure basis of insured value; its intervals have exposure values \$40,000 and \$20,000. Coverage \(C2\) is general liability with a payroll basis; its single interval has exposure of \$15,000. The coverage table identifies each coverage by \((policy_id, coverage_id)\).
Create an interval-level table containing policy state, coverage type, and exposure, while keeping the basis visible. Method: sequential keyed join with unit labels retained.
Step 1: List the interval records. There are three: \((Q88,C1,1,\$40{,}000)\), \((Q88,C1,2,\$20{,}000)\), and \((Q88,C2,1,\$15{,}000)\).
Step 2: Attach coverage information. The first two records match \(C1\), so each receives equipment breakdown and insured-value basis. The third matches \(C2\), so it receives general liability and payroll basis.
Step 3: Attach the policy information. Each of the three rows matches \(Q88\), so each receives state South.
The resulting records are: \((Q88,C1,1,\text{South},\text{equipment breakdown},\$40{,}000,\text{insured value})\), \((Q88,C1,2,\text{South},\text{equipment breakdown},\$20{,}000,\text{insured value})\), and \((Q88,C2,1,\text{South},\text{general liability},\$15{,}000,\text{payroll})\). There are three output rows for three intervals. Within the equipment-breakdown basis, \(40{,}000+20{,}000=\$60{,}000\); the \$15,000 payroll exposure remains separate, not part of a combined \$75,000 exposure total. Common mistake avoided: attaching correct amounts but dropping the basis label, which can make unlike units appear comparable.
Check Your Understanding
For each problem, identify the appropriate join logic and the resulting interval-level records or counts.
- A policy table has one row for policy \(R10\). Its coverage table has \((R10,A)\) and \((R10,B)\). The interval table has two intervals for \(A\) and one for \(B\). How many rows should the correctly joined output contain, and which key attaches coverage attributes? (Answer: 3 rows; join on \((policy_id, coverage_id)\).)
- A policy has three coverages, and each coverage has two interval records. An analyst joins intervals to coverage using policy_id alone. Assuming all six intervals carry the same policy_id, how many joined rows result, and how many should result with the full coverage key? (Answer: 18 rows from the incomplete join; 6 rows with the full coverage key.)
- A coverage table uses \((policy_id, coverage_id)\) as its key. The interval \((S5,D,2)\) has exposure of 0.30 units. Which coverage row should supply its coverage type, and how many output rows should this interval create if the key matches exactly one coverage row? (Answer: coverage row \((S5,D)\); 1 output row.)
- A policy has one interval with \$25,000 of insured-value exposure and another with \$8,000 of payroll exposure. Both match their coverage records. What is the number of output rows, and should the two exposure values be summed into one total exposure? (Answer: 2 rows; no, retain the insured-value and payroll bases separately.)
- A result has four rows for four source intervals, but one interval appears twice and another is absent. Is the row count alone sufficient to validate the join? What should be checked? (Answer: no; verify each interval identifier appears exactly once and inspect that each interval matched the correct coverage and policy attributes.)