Zupa Insurance Database Model Sheet DM01

 

Zupa Insurance Database Model

In the dynamic landscape of the insurance industry, efficient data management is paramount. A well-structured database not only streamlines operations but also empowers data-driven decision-making. This document outlines a comprehensive database model for Zupa Insurance, focusing on key entities, relationships, and considerations for optimal performance and scalability. We'll explore how to we get data modeling requests to create a robust foundation for Zupa's data assets.
This is how we might get information/Handover from Business Analysts and Data Modelers

I. Zift Tables

These tables form the foundation of the Zupa Insurance system.

1. Customers

Description: Stores information about individuals or entities who purchase insurance policies. This table is crucial for customer relationship management (CRM).

Column	Type	Notes
customer_id	INT	PRIMARY KEY, AUTO_INCREMENT
first_name	VARCHAR(255)	NOT NULL
last_name	VARCHAR(255)	NOT NULL
date_of_birth	DATE	-
gender	VARCHAR(10)	-
address	VARCHAR(255)	-
city	VARCHAR(100)	-
state	VARCHAR(50)	-
zip_code	VARCHAR(10)	-
email	VARCHAR(255)	UNIQUE, for data integrity
phone_number	VARCHAR(20)	-
customer_type	VARCHAR(50)	e.g., "Individual," "Business"
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP
updated_at	TIMESTAMP	ON UPDATE CURRENT_TIMESTAMP

Primary Key: `customer_id`

2. Policies

Description: Stores information about insurance policies. This is a core table for managing insurance products and policy details.

Column	Type	Notes
policy_id	INT	PRIMARY KEY, AUTO_INCREMENT
customer_id	INT	FOREIGN KEY REFERENCES Customers(customer_id), NOT NULL
policy_type	VARCHAR(100)	e.g., "Auto," "Home," "Life."
policy_number	VARCHAR(255)	UNIQUE, NOT NULL
issue_date	DATE	NOT NULL
effective_date	DATE	NOT NULL
expiration_date	DATE	NOT NULL
premium_amount	DECIMAL(10, 2)	NOT NULL
payment_frequency	VARCHAR(50)	e.g., "Monthly," "Annually"
policy_status	VARCHAR(50)	e.g., "Active," "Inactive," "Cancelled."
deductible_amount	DECIMAL(10, 2)	-
coverage_amount	DECIMAL(10, 2)	-
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP
updated_at	TIMESTAMP	ON UPDATE CURRENT_TIMESTAMP

Primary Key: `policy_id`

Foreign Key: `customer_id` (references `Customers`)

3. Claims

Description: Stores information about insurance claims. Efficient claims processing is vital in the insurance sector.

Column	Type	Notes
claim_id	INT	PRIMARY KEY, AUTO_INCREMENT
policy_id	INT	FOREIGN KEY REFERENCES Policies(policy_id), NOT NULL
customer_id	INT	FOREIGN KEY REFERENCES Customers(customer_id), NOT NULL
claim_number	VARCHAR(255)	UNIQUE, NOT NULL
claim_date	DATE	NOT NULL
claim_status	VARCHAR(50)	e.g., "Open," "Pending," "Approved," "Denied."
claim_amount	DECIMAL(10, 2)	NOT NULL
claim_description	TEXT	-
incident_date	DATE	-
incident_location	VARCHAR(255)	-
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP
updated_at	TIMESTAMP	ON UPDATE CURRENT_TIMESTAMP

Primary Key: `claim_id`

Foreign Keys: `policy_id` (references `Policies`), `customer_id` (references `Customers`)

7. Policy_Beneficiaries

Description: Links policies to their beneficiaries, allowing for multiple beneficiaries per policy.

Column	Type	Notes
policy_id	INT	FOREIGN KEY REFERENCES Policies(policy_id), NOT NULL
customer_id	INT	FOREIGN KEY REFERENCES Customers(customer_id), NOT NULL
beneficiary_percentage	DECIMAL(5, 2)	-

Primary Key: (`policy_id`, `customer_id`) (Composite primary key)

Foreign Keys: `policy_id` (references `Policies`), `customer_id` (references `Customers`)

III. Application-Specific Tables

Here are the tables for the other applications:

9. RCM_Data

Description: Stores detailed policy information, claims data, and related financial data from the RCM PostgreSQL database. This is important for financial data analysis in the insurance domain.

Column	Type	Notes
rcm_data_id	INT	PRIMARY KEY, AUTO_INCREMENT
policy_id	INT	FOREIGN KEY REFERENCES Policies(policy_id), NOT NULL
claim_id	INT	FOREIGN KEY REFERENCES Claims(claim_id)
financial_data_id	INT	FOREIGN KEY REFERENCES Financial_Data(financial_data_id), NOT NULL
detailed_policy_info	TEXT	Detailed information about the policy.
detailed_claim_info	TEXT	Detailed information about the claim.
financial_data	DECIMAL(10,2)	Financial data related to claim or policy.
created_at	TIMESTAMP	When the record was created.
updated_at	TIMESTAMP	When the record was last updated.

Primary Key: `rcm_data_id`

Foreign Keys: `policy_id` (references `Policies`), `claim_id` (references `Claims`), `financial_data_id` (references `Financial_Data`)

10. Financial_Data

Description: Stores financial data. This table is designed to remove the multi-valued dependency of `financial_data_id`

Column	Type	Notes
financial_data_id	INT	PRIMARY KEY, AUTO_INCREMENT
financial_value	DECIMAL(10,2)	Financial data related to claim or policy.
created_at	TIMESTAMP	When the record was created.

Primary Key: `financial_data_id`

11. Microsoft_CRM_Data

Description: Stores online transaction data, customer interactions, and marketing campaign data from Microsoft CRM Dynamics. Essential for marketing analytics and customer journey tracking.

Column	Type	Notes
crm_data_id	INT	PRIMARY KEY, AUTO_INCREMENT
customer_id	INT	FOREIGN KEY REFERENCES Customers(customer_id), NOT NULL
transaction_id	INT	Identifier for online transaction.
transaction_date	TIMESTAMP	Date and time of the online transaction.
interaction_type	VARCHAR(100)	Type of customer interaction (e.g., "Website Visit," "Email Click").
interaction_date	TIMESTAMP	Date and time of interaction.
marketing_campaign_id	INT	Identifier for marketing campaign.
campaign_response	VARCHAR(100)	Customer response to marketing campaign.
created_at	TIMESTAMP	When the record was created.
updated_at	TIMESTAMP	When the record was last updated.

Primary Key: `crm_data_id`

Foreign Key: `customer_id` (references `Customers`)

• 12. Sensitive_User_Data

  Description: Stores sensitive user information from CSV files. These CSV files are provided in a password-protected ZIP format. The solution must address data security and compliance requirements for handling this data. This table is critical for managing PII data.

  Action item: To check if we can use better way of storing PII data.

  13. SCD_Integration

  Description: This is not a table, but a placeholder for the integration with the existing Slowly Changing Dimension (SCD) system. The MOI will provide data to this system for master data management.

  • The specific tables and data exchange format will need to be defined in collaboration with the team managing the SCD system.
  • Key requirements include:
    • Identifying the common key(s) for identity resolution (e.g., `customer_id`, email, phone number).
    • Defining the data feed mechanism (e.g., API, batch file transfer).
    • Ensuring data quality and consistency between the Zupa system and the SCD system.
    • Handling data updates and changes according to the SCD system's requirements.
    • Consider using a separate staging area for data exchange with the SCD system.
  • A separate document or specification should detail the SCD integration.

  Please comment about the clarifying questions we should ask BA.