Harbour Mutual's data model
Eleven tables in four groups. Customers buy policies, directly or through brokers; each policy term is for one product and bills premium; losses become claims, which are paid over time and documented by adjusters.
Customers and distribution
Where customers live and brokers work: four regions in each of the three countries.
| Column | Type | Key | Meaning |
|---|---|---|---|
region_id |
serial |
PK | Identifies the region. |
country |
char(2) |
NL, PL or DE. | |
name |
text |
The region's name, e.g. North Holland. |
Private customers: the people who buy policies (the policyholders).
| Column | Type | Key | Meaning |
|---|---|---|---|
customer_id |
serial |
PK | Identifies the customer. |
region_id |
int |
FK | Where the customer lives. |
name |
text |
Full name. All names are invented. | |
birth_date |
date |
Date of birth; young drivers claim more often. | |
customer_since |
date |
When they first bought from Harbour Mutual. |
Intermediaries who sell Harbour Mutual's policies for a commission. A policy with no broker was sold directly.
| Column | Type | Key | Meaning |
|---|---|---|---|
broker_id |
serial |
PK | Identifies the broker. |
region_id |
int |
FK | The region the broker works in. |
name |
text |
The broker's firm. | |
commission_rate |
numeric(4,3) |
Share of the premium paid to the broker, e.g. 0.125 for 12.5%. |
Products
What Harbour Mutual sells: two products in each line of business.
| Column | Type | Key | Meaning |
|---|---|---|---|
product_id |
serial |
PK | Identifies the product. |
line |
text |
The line of business: motor, home or travel. | |
name |
text |
The product's name, e.g. Motor Comprehensive. | |
launched_on |
date |
When the product went on sale. |
What each product can cover: third-party liability, theft, storm and flood, medical expenses...
| Column | Type | Key | Meaning |
|---|---|---|---|
coverage_id |
serial |
PK | Identifies the coverage. |
product_id |
int |
FK | The product it belongs to. |
name |
text |
What is covered. | |
default_limit |
numeric(12,2) |
The most Harbour Mutual pays under this coverage, unless a policy says otherwise. |
Policies and premiums
One row per policy term: a year of cover (a single trip for travel). A renewal is a new row with a new policy number.
| Column | Type | Key | Meaning |
|---|---|---|---|
policy_id |
serial |
PK | Identifies the policy term. |
policy_number |
text |
UQ | The number on the customer's documents, e.g. HM-MC-004211-2024. |
customer_id |
int |
FK | The policyholder. |
product_id |
int |
FK | What was bought. |
broker_id |
int |
FK | The broker who sold it; null means sold directly. |
start_date |
date |
The first day of cover. | |
end_date |
date |
The last day of cover. | |
status |
text |
active, expired, lapsed (premium not paid) or cancelled. | |
annual_premium |
numeric(10,2) |
The price of the term, in euros. |
Which coverages each policy includes, with its own limit and deductible.
| Column | Type | Key | Meaning |
|---|---|---|---|
policy_id |
int |
PK | The policy. |
coverage_id |
int |
PK | The coverage. |
limit_amount |
numeric(12,2) |
The most paid under this coverage for this policy. | |
deductible |
numeric(10,2) |
The part of each claim the customer pays (the excess). |
Claims
A customer's request for payment after a loss: a storm, a theft, a collision.
| Column | Type | Key | Meaning |
|---|---|---|---|
claim_id |
serial |
PK | Identifies the claim. |
claim_number |
text |
UQ | The number the customer quotes, e.g. CL-2025-01240. |
policy_id |
int |
FK | The policy the claim is made under. |
loss_date |
date |
When the loss happened. | |
reported_date |
date |
When Harbour Mutual heard of it. | |
cause |
text |
What caused the loss: storm, burglary, collision... | |
status |
text |
open, closed or rejected. | |
reserve |
numeric(12,2) |
What Harbour Mutual still expects to pay on an open claim. |
Money paid out on claims, often in parts over months.
| Column | Type | Key | Meaning |
|---|---|---|---|
payment_id |
serial |
PK | Identifies the payment. |
claim_id |
int |
FK | The claim paid. |
paid_on |
date |
When it was paid. | |
amount |
numeric(12,2) |
The amount paid. |