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) | - |
| 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.
Comments
Post a Comment