219 ) Canonical Data Model : Life Insurance Sales

 

Canonical Data Model :  

Here we are saving all table data in a single table called POLICY

--------------------------------------------------------------------------------------------

What is a Canonical Model?

Canonical Flat Table (The Denormalized Summary)

To solve the slow multi-table JOIN performance issue, we combine your normalized tables into a single canonical summary table (canonical_policy_summary):

Instead of letting multiple tables or systems use separate formats, a canonical model standardizes fields and relationships into one common layout.

SEPERATE TABLES INTO SINGLE TABLE

• customer (customer_id (PK), first_name, last_name, date_of_birth, gender, email, phone_number, address, created_at)

• agent (agent_id (PK), first_name, last_name, agency_name, license_number, email, phone_number)

• policy (policy_id (PK), customer_id (FK), agent_id (FK), product_type, sum_assured, premium_amount, payment_frequency, status, start_date, end_date)

• transaction (transaction_id (PK), policy_id (FK), transaction_type, amount, transaction_date, status)

• payment (payment_id (PK), transaction_id (FK), policy_id (FK), amount_paid, payment_method, gateway_reference_id, payment_date)

• claim (claim_id (PK), policy_id (FK), claim_type, incident_date, claim_amount, status, settlement_date

CANONICAL MODEL FOR POLICY 

SQL

CREATE TABLE canonical_policy_summary (
    policy_id INT PRIMARY KEY,
    policy_number VARCHAR(50),
    product_type VARCHAR(50),
    policy_status VARCHAR(20),
    sum_assured DECIMAL(12,2),
    premium_amount DECIMAL(12,2),
    customer_id INT,
    customer_full_name VARCHAR(150),
    customer_email VARCHAR(100),
    agent_id INT,
    agent_name VARCHAR(150),
    agency_name VARCHAR(100),
    total_claims_count INT,
    total_claims_amount DECIMAL(12,2),
    total_paid_amount DECIMAL(12,2),
    last_updated_at TIMESTAMP
);

How to Populate It (Daily Job)

Once a day, a scheduled job runs this query to refresh the canonical model so apps don't have to run live joins:

SQL

INSERT INTO canonical_policy_summary
SELECT 
    p.policy_id,
    p.policy_number,
    p.product_type,
    p.status AS policy_status,
    p.sum_assured,
    p.premium_amount,
    c.customer_id,
    c.first_name || ' ' || c.last_name AS customer_full_name,
    c.email AS customer_email,
    a.agent_id,
    a.first_name || ' ' || a.last_name AS agent_name,
    a.agency_name,
    COUNT(DISTINCT cl.claim_id) AS total_claims_count,
    COALESCE(SUM(DISTINCT cl.claim_amount), 0) AS total_claims_amount,
    COALESCE(SUM(pay.amount_paid), 0) AS total_paid_amount,
    CURRENT_TIMESTAMP
FROM policy p
JOIN customer c ON p.customer_id = c.customer_id
JOIN agent a ON p.agent_id = a.agent_id
LEFT JOIN claim cl ON p.policy_id = cl.policy_id
LEFT JOIN payment pay ON p.policy_id = pay.policy_id
GROUP BY p.policy_id, p.policy_number, p.product_type, p.status, p.sum_assured, p.premium_amount,
         c.customer_id, c.first_name, c.last_name, c.email,
         a.agent_id, a.first_name, a.last_name, a.agency_name;

Why This Works

• Eliminates Live Joins: Dashboards and searches query canonical_policy_summary directly in milliseconds.
  
  
• Standardized Layout: Customer names, agent details, and financial totals live together in one predictable structure.