Data-modeling-made-easy: 149 ) EDM with on (core subject areaas ) insurance project

An Enterprise Data Model (EDM) is traditionally designed as an OLTP (Online Transaction Processing) database structured in Third Normal Form (3NF) (or higher), rather than a dimensional model.

Here is how it breaks down in enterprise architecture:

1. Enterprise Data Model (EDM) = 3NF / OLTP Structure

Purpose: An EDM (like the life insurance model discussed above) serves as the single source of truth for an entire enterprise's operational processes. It is designed to capture, process, and store atomic, transaction-level data with minimal redundancy.

Plaintext

Life Insurance Enterprise Data Model
│
├── 1. Party Management Domain
│   ├── T_PARTY
│   ├── T_PARTY_ADDRESS
│   └── T_PARTY_CONTACT
│
├── 2. Product & Policy Domain
│   ├── T_PRODUCT
│   ├── T_POLICY
│   ├── T_COVERAGE
│   └── T_BENEFICIARY
│
├── 3. Underwriting & Risk Domain
│   ├── T_UNDERWRITING_CASE
│   └── T_MEDICAL_REQUIREMENT
│
├── 4. Billing & Premium Domain
│   ├── T_BILLING_ACCOUNT
│   └── T_PREMIUM_TRANSACTION
│
├── 5. Claims Management Domain
│   ├── T_CLAIM
│   └── T_CLAIM_PAYMENT
│
├── 6. Agent & Commission Domain
│   ├── T_AGENT
│   └── T_COMMISSION_TRANSACTION
│
└── 7. Reference & Lookup Domain
    ├── R_POLICY_STATUS
    ├── R_CLAIM_STATUS
    └── R_PRODUCT_TYPE

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

PHASE 1 : Client meeting

( How client shares his OLTP requirement )

Client Meeting Transcript / Summary

Client: "Hi team. We are modernizing our core systems for our life insurance business. Right now, our data is heavily fragmented across legacy apps, spreadsheets, and third-party vendor tools. When a customer buys a policy, updates an address, or files a claim, things don't sync properly, and we run into data duplication and consistency issues. We need a centralized, rock-solid core database that can handle live, day-to-day transactions accurately without errors."

Architect / Technical Lead: "To make sure I understand correctly, you need a single source of truth that acts as the operational backbone for all live transactions—handling high-frequency data entries, rigorous data validation (like making sure a policy can't be issued without a valid insured party), and strict record-keeping?"

Client: "Exactly. It needs to capture every single operational detail cleanly. Specifically, we need to manage:"

Parties: Comprehensive profiles for our customers, agents, and beneficiaries, including their history of addresses and contact details.
Products & Policies: The actual life insurance products we sell, active policy contracts, riders/coverages attached to them, and designated beneficiaries.
Underwriting: Tracking the medical and financial risk assessment cases before a policy gets approved.
Billing & Premiums: Managing payment accounts, frequencies, and logging every single incoming payment transaction.
Claims: Handling the full lifecycle of a claim from the initial date of loss, through investigation, to final payout.
Agencies & Commissions: Tracking which agents sell what and calculating their commission payouts.

Architect / Technical Lead: "That gives us a complete picture. Based on what you've described—handling high-volume, concurrent operational transactions with zero redundancy and strict referential integrity—we should not build a reporting or analytical structure right now.

Instead, we need to design an Enterprise Data Model (EDM) built as an OLTP (Online Transaction Processing) database in Third Normal Form (3NF). This will ensure atomic data storage, eliminate anomalies for your daily operations, and cleanly organize everything into the core subject areas you outlined: Party Management, Product & Policy, Underwriting, Billing & Premium, Claims Management, and Agent & Commission. Once this operational foundation is live, we can feed it downstream into analytical star schemas for your executive dashboards."

Client: "That makes total sense. Let's proceed with that architectural approach

1.1 . Questions asked by Data Architect

Phase 1: Understanding Business Processes & Operational Pain Points
These questions uncover how the business runs day-to-day and why the current system is failing.
1)"Can you walk me through the lifecycle of a life insurance policy—from the moment a customer applies, to when it gets approved, billed, and eventually paid out or claimed?"

2) "Where are you experiencing the most friction or data inconsistency today across your current legacy systems or spreadsheets?" (Identifies operational bottlenecks and data silos).

3) "Do you need multiple systems to write data simultaneously, and how critical is it that a change in customer details updates across everything instantly?" (Justifies the need for an OLTP transactional backend rather than a batch-loaded data warehouse).

Phase 2: Defining the Scope and Subject Areas
Here is how the discovery questions read with the bracketed notes removed:
Phase 1: Understanding Business Processes & Operational Pain Points
Can you walk me through the policy lifecycle from application to claim?
Where are you experiencing data inconsistencies in your current systems?
How critical is instant data synchronization across multiple systems?
Phase 2: Defining the Scope and Subject Areas
What distinct types of people and entities interact with your system?
How complex are your products, riders, and coverages?
How are premium payments, grace periods, and lapses tracked?
What risk assessment and medical data are required during underwriting?
What details and payout tracking are needed when a claim is filed?
Phase 3: Uncovering Current Infrastructure & Existing Tables
What does your current data landscape look like?
Are there existing database schemas or ERDs from legacy systems?
What regulatory or compliance standards must these tables support?

Phase 1

Initial Discovery and Process Walkthrough

We first meet with the client's managers and key project leads to kick off the engagement and schedule a comprehensive walkthrough of all current business processes.
During these sessions, we observe live demonstrations of legacy applications and operational workflows to understand how daily transactions are handled.

Requirements Consolidation and Subject Area Mapping

We gather and document the complete list of high-level subject areas required for the enterprise model, ensuring all operational domains like party management, policies, and claims are accounted for.
We finalize the initial project scope and boundary definitions with the leadership team before transitioning into technical database design.

Phase 1 Duration and Timeline

For a comprehensive enterprise data model project of this scale, Phase 1 typically takes 2 to 3 weeks to complete.
This timeframe allows enough breathing room to schedule and conduct thorough discovery workshops with cross-functional teams—such as underwriters, actuaries, and operations leads—without disrupting their day-to-day business routines.

Phase 2: Logical Data Model (LDM) Finalization & Sign-Off

Step 1: Draft the Comprehensive Master Document

We compile all normalized entities, relationships, and business rules into a single master deliverable (often titled the Logical Data Model Specification Document).
Within this document, we map out the complete data architecture using three core components:
- Entity-Relationship Diagram (ERD): A visual schematic showing how all tables connect across domains (e.g., how T_POLICY relates to T_PARTY and T_BILLING_ACCOUNT).
- Data Dictionary: A catalog listing every single table, attribute, definition, data type, and primary/foreign key constraint.
- Business Rationale & Mapping: A written breakdown explaining why each table exists, the normalization logic applied, and how it satisfies the insurance workflows gathered in Phase 1.

Step 2: Conduct Internal Peer Review & Actuarial Walkthrough

Before presenting the model to the main business client, our technical team and data architects review the LDM for normalization compliance (ensuring 3NF standards are met).
We also do a quick check with domain experts (like actuaries or senior operations leads) to verify that fields like face amounts, risk classifications, and premium tracking attributes are accurately accounted for.

Step 3: Present the LDM Package to the Client

We schedule a formal review meeting with the client leadership, data stewards, and project managers to walk through the complete LDM document and ERD visual.
We explain how the model resolves their legacy data fragmentation issues, enforces strict referential integrity, and covers all agreed-upon subject areas.

Step 4: Gather Feedback, Iterate, and Secure Formal Approval

We collect any requested adjustments or edge-case modifications from the client (e.g., adding a specific rider attribute or adjusting a relationship cardinality).
Once updates are made, we officially issue a Sign-Off Document / Change Request Acceptance Form for the client to formally approve the Logical Data Model, unlocking the green light to move into physical database design.

Phase 3: Physical Database Design and Schema Generation

In Phase 3, we transition the approved Logical Data Model (LDM) into an actual, deployable database schema tailored to the client's chosen Relational Database Management System (such as PostgreSQL, Oracle, SQL Server, or Snowflake).

Step 1: Physical Schema Translation and DDL Script Generation

We convert all logical tables, attributes, and data types into physical Data Definition Language (DDL) scripts.
We explicitly define database-specific parameters, such as primary key constraints, foreign key referential integrity rules, check constraints, and unique indexes to ensure the transactional engine enforces strict data quality.

Step 2: Performance Tuning and Indexing Strategy

We design a robust indexing strategy, creating non-clustered indexes on frequently searched foreign keys (such as policy_id in billing or claim tables) to optimize join and query performance.
For high-volume transactional tables like T_PREMIUM_TRANSACTION or audit logs, we plan partitioning strategies (such as range partitioning by transaction date) to maintain fast read/write speeds as data scales.

Step 3: Security, Auditing, and Compliance Implementation

We implement database-level security protocols, including role-based access controls (RBAC) and column-level encryption or data masking for sensitive fields like Social Security Numbers and personal medical records.
We embed automated audit columns (such as created_by, created_timestamp, updated_by, and updated_timestamp) across all physical tables to track every operational change for compliance and auditing.

Step 4: Physical Deployment and Client Sign-Off

We deploy the generated DDL scripts into a non-production or sandbox database environment (such as Dev/QA).
We conduct automated script validations and database testing to ensure there are no syntax errors, missing constraints, or orphaned relationships, followed by a final review and sign-off from the client's database administration (DBA) team before moving toward data migration and application integration.

Phase 3 Duration and Timeline

This phase typically takes 2 to 3 weeks to complete, depending on the complexity of the security policies, indexing requirements, and target database platform setup.

Phase 4: Data Migration, Integration, and System Testing

In Phase 4, we take the newly deployed physical OLTP database and connect it to the client's operational ecosystem, ensuring data flows correctly from legacy sources and applications can successfully read and write to the new tables.

Step 1: Data Migration Strategy and ETL Mapping

We design a comprehensive data migration blueprint to extract historical data from the client's legacy systems, spreadsheets, or disparate databases.
We build ETL (Extract, Transform, Load) or ELT pipelines to cleanse, map, and transform legacy records into the strict 3NF structure of our new enterprise data model without losing historical integrity.

Step 2: Application and API Integration

We collaborate with the client’s software development and application teams to connect core policy administration systems, billing portals, and agent interfaces to the new database.
We validate that backend APIs and operational front-ends can successfully execute CRUD (Create, Read, Update, Delete) operations while respecting all database constraints and referential integrity rules.

Step 3: End-to-End System Testing and Validation

We conduct rigorous system integration testing (SIT) and data validation checks, running test scenarios such as issuing a mock policy, processing a premium payment, and filing a test claim.
We perform data reconciliation scripts to verify that migrated historical data matches source records precisely and that transaction performance meets operational benchmarks.

Phase 5: Post-Implementation Review and Ongoing Governance

In Phase 5, we transition from active project delivery into long-term maintenance, performance monitoring, and enterprise data governance to ensure the new OLTP database continues to support the insurance business seamlessly as it grows.

Step 1: Performance Monitoring and Tuning

We set up continuous monitoring tools to track database query execution times, CPU utilization, and lock contention on high-frequency tables like T_PREMIUM_TRANSACTION and T_CLAIM.
We review execution plans and dynamically tune indexes or partition boundaries as operational data volume grows in the live environment.

Step 2: Data Governance and Quality Enforcement

We implement ongoing data quality monitoring frameworks to catch anomalous transactions, orphan records, or incomplete party profiles early.
We establish a formal Data Governance Board or appoint data stewards responsible for approving future schema changes, adding new product types, or updating reference tables.

Step 3: Ongoing Support and Handover Transition

We provide a hypercare support window immediately following go-live to swiftly resolve any operational bottlenecks or edge-case integration bugs reported by the client's engineering teams.
We hand over final operational runbooks, maintenance schedules, and backup/recovery protocols to the client's internal database administration and IT teams.

Phase 5 Duration and Timeline

Hypercare Support: Typically lasts 2 to 4 weeks immediately post-launch.
Ongoing Governance: Operates continuously as a permanent part of the enterprise’s routine IT operations.

Core Subject Areas for a Life Insurance Enterprise Data Model

Party Management (Customer & Stakeholders)
- Purpose: Manages all individuals and organizations interacting with the insurance company.
- Key Entities: Customers (Policyholders), Agents, Brokers, Beneficiaries, and Employees.
Product & Coverage Management
- Purpose: Defines the insurance offerings, rules, terms, and optional riders available for purchase.
- Key Entities: Base Insurance Products, Riders, Coverages, Benefit Schedules, and Premium Rate Tables.
Policy Administration (Contract Lifecycle)
- Purpose: Tracks the active legal contracts between the insurance company and the policyholders.
- Key Entities: Policy Header, Policy-Rider Associations, Beneficiary Designations, Endorsements, and Status History (Active, Lapsed, Surrendered).
Underwriting & Risk Assessment
- Purpose: Evaluates applicant risk, medical history, and financial background before policy issuance.
- Key Entities: Underwriting Cases, Medical Questionnaires, Lab Results, Risk Scores, and Approval/Rejection Decisional Logs.
Billing & Premium Accounting
- Purpose: Handles premium invoicing, payment collections, grace periods, and account balances.
- Key Entities: Billing Accounts, Premium Invoices, Payment Transactions, Payment Methods, and Commission Ledgers.
Claims Management & Payouts

Purpose: Manages the lifecycle of a claim from initial notification to final financial settlement.
Key Entities: Claim Intimation/First Notice of Loss (FNOL), Claim Adjuster Notes, Adjudication Details, and Claim Disbursements/Payouts.

Here is a high-level conceptual Entity-Relationship Diagram (ERD) visualizing the relationships between the six core subject areas for the Life Insurance Enterprise Data Model.

Following the diagram is the expanded, comprehensive list of operational tables organized by their respective subject area.

High-Level Conceptual ERD tree e diagram

Enterprise Data Model (EDM) Subject Area Hierarchy

Enterprise Data Model (EDM)
│
├── 1. Party Management Domain
│   ├── T_PARTY (Master table for Persons and Organizations)
│   ├── T_PARTY_ADDRESS
│   ├── T_PARTY_CONTACT (Email, Phone)
│   ├── T_PARTY_BANK_ACCOUNT (For Payouts/Premium Auto-draft)
│   ├── T_AGENT_PROFILE
│   ├── T_AGENT_AGREEMENT (Commission structures)
│   └── T_EMPLOYEE_PROFILE (Internal users)
│
├── 2. Product Management Domain
│   ├── T_PRODUCT (Master definitions)
│   ├── T_PRODUCT_COVERAGE
│   ├── T_PRODUCT_RIDER
│   ├── T_PRODUCT_STATE_RULE (Regulatory variations by state)
│   └── T_PREMIUM_RATE_TABLE (Actuarial tables)
│
├── 3. Policy Administration Domain
│   ├── T_POLICY (Header record)
│   ├── T_POLICY_STATUS_HISTORY
│   ├── T_POLICY_RIDER_ASSOC
│   ├── T_BENEFICIARY
│   └── T_POLICY_ALTERATION_LOG (Endorsements)
│
├── 4. Underwriting Domain
│   ├── T_UNDERWRITING_CASE
│   ├── T_MEDICAL_REQUIREMENT
│   ├── T_FINANCIAL_QUESTIONNAIRE
│   ├── T_UNDERWRITING_DECISION
│   └── T_RISK_CLASSIFICATION
│
├── 5. Billing & Premium Accounting Domain
│   ├── T_BILLING_ACCOUNT
│   ├── T_PREMIUM_INVOICE
│   ├── T_PAYMENT_TRANSACTION
│   ├── T_COMMISSION_LEDGER
│   └── T_SUSPENSE_ACCOUNT (Unallocated funds)
│
├── 6. Claims Management Domain
│   ├── T_CLAIM (FNOL entry)
│   ├── T_CLAIM_COVERAGE_ASSOC
│   ├── T_CLAIM_ADJUDICATION_LOG
│   ├── T_CLAIM_PAYMENT
│   └── T_RESERVE_HISTORY (Changes in claim liability reserves)
│
└── 7. Cross-Domain / Foundation
    ├── T_REF_DATA_CODE (Lookup tables for status, gender, country codes, etc.)
    └── T_AUDIT_LOG

Comprehensive Operational Table Name List (3NF)

This list expands upon the previous generic list to include tables necessary to support all six complex subject areas defined for a Life Insurance EDM.

1. Party Management

T_PARTY (Master table for Persons and Organizations)
T_PARTY_ADDRESS
T_PARTY_CONTACT (Email, Phone)
T_PARTY_BANK_ACCOUNT (For Payouts/Premium Auto-draft)
T_AGENT_PROFILE
T_AGENT_AGREEMENT (Commission structures)
T_EMPLOYEE_PROFILE (Internal users)

2. Product Management

T_PRODUCT (Master definitions)
T_PRODUCT_COVERAGE
T_PRODUCT_RIDER
T_PRODUCT_STATE_RULE (Regulatory variations by state)
T_PREMIUM_RATE_TABLE (Actuarial tables)

3. Policy Administration

T_POLICY (Header record)
T_POLICY_STATUS_HISTORY
T_POLICY_RIDER_ASSOC
T_BENEFICIARY
T_POLICY_ALTERATION_LOG (Endorsements)

4. Underwriting

T_UNDERWRITING_CASE
T_MEDICAL_REQUIREMENT
T_FINANCIAL_QUESTIONNAIRE
T_UNDERWRITING_DECISION
T_RISK_CLASSIFICATION

5. Billing & Premium Accounting

T_BILLING_ACCOUNT
T_PREMIUM_INVOICE
T_PAYMENT_TRANSACTION
T_COMMISSION_LEDGER
T_SUSPENSE_ACCOUNT (Unallocated funds)

6. Claims Management

T_CLAIM (FNOL entry)
T_CLAIM_COVERAGE_ASSOC
T_CLAIM_ADJUDICATION_LOG
T_CLAIM_PAYMENT
T_RESERVE_HISTORY (Changes in claim liability reserves)

Cross-Domain / Foundation

T_REF_DATA_CODE (Lookup tables for status, gender, country codes, etc.)
T_AUDIT_LOG

Sample Source-to-Target Mapping (STTM) Document

Mapping ID	Source System	Source Table / Field	Target System	Target Table / Field	Transformation Rule / Logic
STTM-001	Legacy CRM	`CUST_MASTER.CUST_ID`	Enterprise OLTP	`T_PARTY.PARTY_ID`	Direct Pass-Through; generate UUID if missing.
STTM-002	Legacy CRM	`CONCAT(FIRST_NAME, ' ', LAST_NAME)`	Enterprise OLTP	`T_PARTY.FULL_NAME`	Trim whitespace and convert to Title Case.
STTM-003	Legacy Billing	`POL_TBL.POL_NUM`	Enterprise OLTP	`T_POLICY.POLICY_NUMBER`	Direct Pass-Through with string length validation.
STTM-004	Legacy Billing	`POL_TBL.ISSUE_DT`	Enterprise OLTP	`T_POLICY.ISSUE_DATE`	Cast string date format (MM/DD/YYYY) to standard timestamp (YYYY-MM-DD HH:MI:SS).

ER - DIAGRAM :

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

SQL
-- =========================================================================
-- LIFE INSURANCE ENTERPRISE DATA MODEL - 3NF PHYSICAL DDL (SQL)
-- Target RDBMS: PostgreSQL / ANSI SQL
-- =========================================================================

-- 1. PARTY MANAGEMENT DOMAIN
CREATE TABLE T_PARTY (
    PARTY_ID VARCHAR(36) PRIMARY KEY,
    PARTY_TYPE VARCHAR(20) NOT NULL, -- 'INDIVIDUAL' or 'ORGANIZATION'
    FIRST_NAME VARCHAR(100),
    LAST_NAME VARCHAR(100),
    ORGANIZATION_NAME VARCHAR(150),
    TAX_ID VARCHAR(50) UNIQUE,
    DATE_OF_BIRTH DATE,
    CREATED_TIMESTAMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UPDATED_TIMESTAMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE T_PARTY_ADDRESS (
    ADDRESS_ID VARCHAR(36) PRIMARY KEY,
    PARTY_ID VARCHAR(36) NOT NULL,
    ADDRESS_TYPE VARCHAR(30), -- 'RESIDENTIAL', 'MAILING', 'BUSINESS'
    STREET_LINE_1 VARCHAR(150) NOT NULL,
    STREET_LINE_2 VARCHAR(150),
    CITY VARCHAR(100) NOT NULL,
    STATE_PROVINCE VARCHAR(100) NOT NULL,
    POSTAL_CODE VARCHAR(20) NOT NULL,
    COUNTRY VARCHAR(100) NOT NULL,
    CONSTRAINT FK_ADDRESS_PARTY FOREIGN KEY (PARTY_ID) REFERENCES T_PARTY(PARTY_ID) ON DELETE CASCADE
);

CREATE TABLE T_PARTY_CONTACT (
    CONTACT_ID VARCHAR(36) PRIMARY KEY,
    PARTY_ID VARCHAR(36) NOT NULL,
    CONTACT_TYPE VARCHAR(30), -- 'EMAIL', 'PHONE', 'MOBILE'
    CONTACT_VALUE VARCHAR(150) NOT NULL,
    IS_PRIMARY BOOLEAN DEFAULT TRUE,
    CONSTRAINT FK_CONTACT_PARTY FOREIGN KEY (PARTY_ID) REFERENCES T_PARTY(PARTY_ID) ON DELETE CASCADE
);

CREATE TABLE T_AGENT_PROFILE (
    AGENT_ID VARCHAR(36) PRIMARY KEY,
    PARTY_ID VARCHAR(36) NOT NULL,
    LICENSE_NUMBER VARCHAR(50) UNIQUE NOT NULL,
    COMMISSION_RATE DECIMAL(5,2) NOT NULL,
    AGENT_STATUS VARCHAR(30) NOT NULL, -- 'ACTIVE', 'SUSPENDED'
    CONSTRAINT FK_AGENT_PARTY FOREIGN KEY (PARTY_ID) REFERENCES T_PARTY(PARTY_ID)
);


-- 2. PRODUCT MANAGEMENT DOMAIN
CREATE TABLE T_PRODUCT (
    PRODUCT_ID VARCHAR(36) PRIMARY KEY,
    PRODUCT_CODE VARCHAR(50) UNIQUE NOT NULL,
    PRODUCT_NAME VARCHAR(150) NOT NULL,
    PRODUCT_CATEGORY VARCHAR(50), -- 'TERM_LIFE', 'WHOLE_LIFE', 'UNIVERSAL_LIFE'
    EFFECTIVE_DATE DATE NOT NULL,
    EXPIRY_DATE DATE
);

CREATE TABLE T_PRODUCT_COVERAGE (
    COVERAGE_ID VARCHAR(36) PRIMARY KEY,
    PRODUCT_ID VARCHAR(36) NOT NULL,
    COVERAGE_NAME VARCHAR(100) NOT NULL,
    MIN_LIMIT DECIMAL(15,2),
    MAX_LIMIT DECIMAL(15,2),
    CONSTRAINT FK_COVERAGE_PRODUCT FOREIGN KEY (PRODUCT_ID) REFERENCES T_PRODUCT(PRODUCT_ID) ON DELETE CASCADE
);

CREATE TABLE T_PRODUCT_RIDER (
    RIDER_ID VARCHAR(36) PRIMARY KEY,
    PRODUCT_ID VARCHAR(36) NOT NULL,
    RIDER_NAME VARCHAR(100) NOT NULL,
    DESCRIPTION TEXT,
    CONSTRAINT FK_RIDER_PRODUCT FOREIGN KEY (PRODUCT_ID) REFERENCES T_PRODUCT(PRODUCT_ID) ON DELETE CASCADE
);


-- 3. POLICY ADMINISTRATION DOMAIN
CREATE TABLE T_POLICY (
    POLICY_ID VARCHAR(36) PRIMARY KEY,
    POLICY_NUMBER VARCHAR(50) UNIQUE NOT NULL,
    PRODUCT_ID VARCHAR(36) NOT NULL,
    POLICY_HOLDER_ID VARCHAR(36) NOT NULL,
    AGENT_ID VARCHAR(36),
    ISSUE_DATE DATE NOT NULL,
    EFFECTIVE_DATE DATE NOT NULL,
    EXPIRY_DATE DATE NOT NULL,
    FACE_AMOUNT DECIMAL(15,2) NOT NULL,
    POLICY_STATUS VARCHAR(30) NOT NULL, -- 'PENDING', 'ACTIVE', 'LAPSED', 'SURRENDERED'
    CREATED_TIMESTAMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT FK_POLICY_PRODUCT FOREIGN KEY (PRODUCT_ID) REFERENCES T_PRODUCT(PRODUCT_ID),
    CONSTRAINT FK_POLICY_HOLDER FOREIGN KEY (POLICY_HOLDER_ID) REFERENCES T_PARTY(PARTY_ID),
    CONSTRAINT FK_POLICY_AGENT FOREIGN KEY (AGENT_ID) REFERENCES T_AGENT_PROFILE(AGENT_ID)
);

CREATE TABLE T_POLICY_RIDER_ASSOC (
    ASSOC_ID VARCHAR(36) PRIMARY KEY,
    POLICY_ID VARCHAR(36) NOT NULL,
    RIDER_ID VARCHAR(36) NOT NULL,
    COVERED_AMOUNT DECIMAL(15,2) NOT NULL,
    CONSTRAINT FK_ASSOC_POLICY FOREIGN KEY (POLICY_ID) REFERENCES T_POLICY(POLICY_ID) ON DELETE CASCADE,
    CONSTRAINT FK_ASSOC_RIDER FOREIGN KEY (RIDER_ID) REFERENCES T_PRODUCT_RIDER(RIDER_ID)
);

CREATE TABLE T_BENEFICIARY (
    BENEFICIARY_ID VARCHAR(36) PRIMARY KEY,
    POLICY_ID VARCHAR(36) NOT NULL,
    PARTY_ID VARCHAR(36) NOT NULL,
    RELATIONSHIP_TYPE VARCHAR(50) NOT NULL, -- 'SPOUSE', 'CHILD', 'TRUST'
    BENEFIT_PERCENTAGE DECIMAL(5,2) NOT NULL,
    CONSTRAINT FK_BENEFICIARY_POLICY FOREIGN KEY (POLICY_ID) REFERENCES T_POLICY(POLICY_ID) ON DELETE CASCADE,
    CONSTRAINT FK_BENEFICIARY_PARTY FOREIGN KEY (PARTY_ID) REFERENCES T_PARTY(PARTY_ID)
);


-- 4. UNDERWRITING & RISK DOMAIN
CREATE TABLE T_UNDERWRITING_CASE (
    CASE_ID VARCHAR(36) PRIMARY KEY,
    POLICY_ID VARCHAR(36) NOT NULL,
    UNDERWRITER_ID VARCHAR(36),
    APPLICATION_DATE DATE NOT NULL,
    CASE_STATUS VARCHAR(30) NOT NULL, -- 'IN_REVIEW', 'APPROVED', 'DECLINED'
    RISK_SCORE INT,
    CONSTRAINT FK_UW_POLICY FOREIGN KEY (POLICY_ID) REFERENCES T_POLICY(POLICY_ID) ON DELETE CASCADE
);

CREATE TABLE T_MEDICAL_REQUIREMENT (
    REQ_ID VARCHAR(36) PRIMARY KEY,
    CASE_ID VARCHAR(36) NOT NULL,
    REQUIREMENT_TYPE VARCHAR(50) NOT NULL, -- 'BLOOD_WORK', 'ECG', 'PHYSICAL_EXAM'
    STATUS VARCHAR(30) NOT NULL, -- 'PENDING', 'RECEIVED', 'WAIVED'
    COMPLETION_DATE DATE,
    CONSTRAINT FK_MED_CASE FOREIGN KEY (CASE_ID) REFERENCES T_UNDERWRITING_CASE(CASE_ID) ON DELETE CASCADE
);


-- 5. BILLING & PREMIUM ACCOUNTING DOMAIN
CREATE TABLE T_BILLING_ACCOUNT (
    ACCOUNT_ID VARCHAR(36) PRIMARY KEY,
    POLICY_ID VARCHAR(36) NOT NULL,
    ACCOUNT_BALANCE DECIMAL(15,2) DEFAULT 0.00,
    BILLING_FREQUENCY VARCHAR(20) NOT NULL, -- 'MONTHLY', 'QUARTERLY', 'ANNUAL'
    NEXT_DUE_DATE DATE NOT NULL,
    CONSTRAINT FK_BILLING_POLICY FOREIGN KEY (POLICY_ID) REFERENCES T_POLICY(POLICY_ID) ON DELETE CASCADE
);

CREATE TABLE T_PAYMENT_TRANSACTION (
    TRANSACTION_ID VARCHAR(36) PRIMARY KEY,
    ACCOUNT_ID VARCHAR(36) NOT NULL,
    PAYMENT_DATE TIMESTAMP NOT NULL,
    AMOUNT_PAID DECIMAL(15,2) NOT NULL,
    PAYMENT_METHOD VARCHAR(30) NOT NULL, -- 'CREDIT_CARD', 'ACH', 'CHECK'
    TRANSACTION_STATUS VARCHAR(30) NOT NULL, -- 'SUCCESS', 'FAILED', 'PENDING'
    CONSTRAINT FK_TX_BILLING FOREIGN KEY (ACCOUNT_ID) REFERENCES T_BILLING_ACCOUNT(ACCOUNT_ID) ON DELETE CASCADE
);


-- 6. CLAIM MANAGEMENT DOMAIN
CREATE TABLE T_CLAIM (
    CLAIM_ID VARCHAR(36) PRIMARY KEY,
    POLICY_ID VARCHAR(36) NOT NULL,
    INCIDENT_DATE DATE NOT NULL,
    FNOL_DATE TIMESTAMP NOT NULL,
    CLAIM_STATUS VARCHAR(30) NOT NULL, -- 'SUBMITTED', 'UNDER_INVESTIGATION', 'APPROVED', 'REJECTED'
    CLAIMED_AMOUNT DECIMAL(15,2) NOT NULL,
    CONSTRAINT FK_CLAIM_POLICY FOREIGN KEY (POLICY_ID) REFERENCES T_POLICY(POLICY_ID)
);

CREATE TABLE T_CLAIM_PAYMENT (
    PAYMENT_ID VARCHAR(36) PRIMARY KEY,
    CLAIM_ID VARCHAR(36) NOT NULL,
    BENEFICIARY_ID VARCHAR(36) NOT NULL,
    PAYMENT_DATE TIMESTAMP NOT NULL,
    AMOUNT_PAID DECIMAL(15,2) NOT NULL,
    PAYMENT_REFERENCE_NO VARCHAR(100) UNIQUE NOT NULL,
    CONSTRAINT FK_CLAIM_PAYMENT_CLAIM FOREIGN KEY (CLAIM_ID) REFERENCES T_CLAIM(CLAIM_ID) ON DELETE CASCADE,
    CONSTRAINT FK_CLAIM_PAYMENT_BENEFICIARY FOREIGN KEY (BENEFICIARY_ID) REFERENCES T_BENEFICIARY(BENEFICIARY_ID)
);

PAGES

Monday, August 3, 2026

149 ) EDM with on (core subject areaas ) insurance project