Atomic Warehouse Model Data Model

Entity

Entity Coverage Agreement Detail

Description

An agreement detailing the circumstances and mechanisms under which healthcare benefits (money or services) will be delivered. Each benefit category in a benefit schedule will have an entry in coverage agreement that specifies the parameters for that benefit category.

For example:
Dental care coverage
Prenatal care
Routine exams

Attributes
Additional Member Designation Code Sk
Annual Deductible Amt
Annual Deductible Out-of-Network Amt
Annual Maximum Benefit Amt
Benefit Delivery Frequency Code Sk
Benefit Maximum Amt
Benefit Unit of Measure Code Sk
Bound Dt
Branded Product Co-Payment Amt
Co-Insurance Pct
Contract Year To Date Deductible Amt
Contract Year To Date Deductible Out-of-Network Amt
Coverage Agreement Sk
Coverage Agreement Status Code Sk
Coverage Agreement Status Reason Code Sk
Coverage Co-Payment Amt
Coverage Co-Payment Out-Of-Network Amt
Coverage Descr
Coverage End Dt
Coverage Out-of-Network Pct
Coverage Pct
Coverage Priority Code Sk
Coverage Start Dt
Coverage Type Code Sk
Dependent Coverage Age Limit
Effective From Dt
Effective To Dt
Eligibility Period
Eligibility Period Unit Code Sk
Exposure Amount Unit Of Measure Code Sk
Exposure Amt
Extended Reporting Period
Formulary Product Co-Payment Amt
Generic Product Co-Payment Amt
Lifetime Maximum Benefit Amt
Line Of Business Code Sk
Load Info Sk
Next Renewal Dt
Next Review Dt
Non-Formulary Product Co-Payment Amt
Planned End Dt
Plan Network Ind
Remaining Benefit Amt
Remaining Deductible Amt
Retroactive Start Dt
Selected Currency Code Sk
Source Code Sk
Tenant Sk
Valid From Ts
Valid To Ts
Waiting Period
Waiting Period Unit Code Sk

Relationship
Coverage Agreement Detail_Coverage Agreement_FK

Primary Key
Coverage Agreement Detail PK

Dependencies

Reverse Dependencies

Attribute Details

Additional Member Designation Code Sk

Description	Indicates how any additional members are identified. For example: By name Spouse and children
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Annual Deductible Amt

Description	The deductible amount to be applied under this coverage agreement in the contract year for 'in-network' benefits, where applicable.
Data Type	Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Annual Deductible Out-of-Network Amt

Description	The deductible amount to be applied under this coverage agreement in the contract year for 'out-of-network' benefits.
Data Type	Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Annual Maximum Benefit Amt

Description	The maximum amount that can be paid out in benefits under this coverage agreement in the contract year.
Data Type	Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Benefit Delivery Frequency Code Sk

Description	Indicates the benefit delivery frequency of coverage agreement. For example: - 1 exam every calendar year for mammograms - 6 exams from birth to 1 year of age for Well Child Care
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Benefit Maximum Amt

Description	The maximum amount that can be received in eligible benefits under the coverage agreement.
Data Type	Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Benefit Unit of Measure Code Sk

Description	Indicates the unit of measure of the coverage agreement benefit.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Bound Dt

Description	The date upon which the agreement is considered legally binding, or in force. Depending on the legislation the bound date can be the date when the coverage agreement is signed by both parties and/or when the first premium has been paid. Derived from the status related information.
Data Type	Standards - Data Domains.ddm/Data Domains/Date [DATE]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Branded Product Co-Payment Amt

Description	The amount of the co-pay for using a branded product under the terms of the pharmacy coverage.
Data Type	Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Co-Insurance Pct

Description	The specific co-insurance percentage applying to the coverage agreement on the policy. In other words the percentage of covered benefits that the member will be liable for up to their out-of-pocket or other maximums.
Data Type	Standards - Data Domains.ddm/Data Domains/Percentage [FLOAT(2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Contract Year To Date Deductible Amt

Description	Amount of covered medical expenses deductible already paid by the members for the coverage agreement during the contract year. Applies to 'in-network' benefits, where applicable.
Data Type	Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Contract Year To Date Deductible Out-of-Network Amt

Description	Amount of covered medical expenses deductible already paid by the members for the coverage agreement during the contract year. Applies to 'out-of-network' benefits, where applicable.
Data Type	Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Coverage Agreement Sk

Description	The generated surrogate key of the Coverage Agreement anchor.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key Large [LONG]
Is Part Of PrimaryKey	true
Is Required	true
Is Derived	false
Is Surrogate Key	false

Coverage Agreement Status Code Sk

Description	Indicates the coverage agreement's current status. For Example: Active Cancelled Expired Final In Force In Process Initial Lapsed Not taken up Offered Rejected Suspended Terminated Under Negotiation
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Coverage Agreement Status Reason Code Sk

Description	Agreement Status Reason classifies agreements according to the motivation for changing the Agreement Status of the agreement. For example: Not eligible (if agreement rejected) Not taken up (if agreement rejected)
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Coverage Co-Payment Amt

Description	The specific co-payment amount applying to all benefits under the coverage agreement. Applies to 'in-network' benefits where applicable.
Data Type	Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Coverage Co-Payment Out-Of-Network Amt

Description	The specific co-payment amount applying to all benefits under the coverage agreement. Applies to 'out-of-network' benefits.
Data Type	Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Coverage Descr

Description	Textual description of the Coverage.
Data Type	Standards - Data Domains.ddm/Data Domains/Description [VARCHAR(250)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Coverage End Dt

Description	The date on which the coverage actually terminates. This may pre- or post-date the Planned End Date. The termination date of the coverage is also represented in the status date for the terminated status.
Data Type	Standards - Data Domains.ddm/Data Domains/Date [DATE]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Coverage Out-of-Network Pct

Description	The percentage of eligible benefits that will be paid for under the coverage agreement. Applies to 'out-of-network' benefits, where applicable.
Data Type	Standards - Data Domains.ddm/Data Domains/Percentage [FLOAT(2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Coverage Pct

Description	The percentage of eligible benefits that will be paid for under the coverage agreement. Applies to 'in-network' benefits, where applicable.
Data Type	Standards - Data Domains.ddm/Data Domains/Percentage [FLOAT(2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Coverage Priority Code Sk

Description	Indicates the priority of the coverage in relation to other insurance policies that cover the same member for the same risks.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Coverage Start Dt

Description	The date upon which the coverage agreement first starts. For renewable coverage agreements, this date represents the date of the original coverage agreement before any renewal. Derived from the status related information.
Data Type	Standards - Data Domains.ddm/Data Domains/Date [DATE]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Coverage Type Code Sk

Description	Type of the coverage this specification is for. For example: Dental Coverage Hospital Indemnity Coverage Pharmacy Coverage
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Dependent Coverage Age Limit

Description	The maximum age that a person can attain before becoming ineligible to receive benefits under this coverage.
Data Type	Standards - Data Domains.ddm/Data Domains/Number Integer [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Effective From Dt

Description	Establishes a period where a set of attributes are true according to the business.
Data Type	Standards - Data Domains.ddm/Data Domains/Date [DATE]
Is Part Of PrimaryKey	false
Is Required	true
Is Derived	false
Is Surrogate Key	false

Effective To Dt

Description	Ends a period of effectivity.
Data Type	Standards - Data Domains.ddm/Data Domains/Date [DATE]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Eligibility Period

Description	Time following the eligibility date during which the member may apply for insurance without evidence of insurability. For example: 31 days
Data Type	Standards - Data Domains.ddm/Data Domains/Number Decimal Large [DECIMAL(15,6)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Eligibility Period Unit Code Sk

Description	Eligibility period unit specifies the unit of the coverage agreement eligibility period.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Exposure Amount Unit Of Measure Code Sk

Description	Exposure amount unit of measure specifies the unit of measure of the coverage agreement exposure amount.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Exposure Amt

Description	A number which represents the exposure on a coverage based on units, number of employees, predicted healthcare costs, etc. The basis for this number can be found in health plan product. For example: 10 (Full time employees ) 10, 000 (n USD 100's of insurance)
Data Type	Standards - Data Domains.ddm/Data Domains/Number Integer [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Extended Reporting Period

Description	Period following the termination date of the policy or coverage in which a claim can still be accepted, provided the claimed loss has occurred during the coverage period. For example: 2 years
Data Type	Standards - Data Domains.ddm/Data Domains/Number Decimal Large [DECIMAL(15,6)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Formulary Product Co-Payment Amt

Description	The amount of the co-pay for using a drug formulary product under the terms of the pharmacy coverage.
Data Type	Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Generic Product Co-Payment Amt

Description	The amount of the co-pay for using a generic product under the terms of the pharmacy coverage.
Data Type	Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Lifetime Maximum Benefit Amt

Description	Total maximum benefit amount reimbursable in a member's lifetime.
Data Type	Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Line Of Business Code Sk

Description	Indicates the nature of commercial enterprise of the modeled organization.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Load Info Sk

Description	The surrogate key of the load information entry describing the details regarding the loading of the row.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key Large [LONG]
Is Part Of PrimaryKey	false
Is Required	true
Is Derived	false
Is Surrogate Key	false

Next Renewal Dt

Description	The next date at which the enforce status of the coverage agreement must be re-evaluated or re-conducted.
Data Type	Standards - Data Domains.ddm/Data Domains/Date [DATE]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Next Review Dt

Description	The date upon which a review of the coverage agreement is planned.
Data Type	Standards - Data Domains.ddm/Data Domains/Date [DATE]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Non-Formulary Product Co-Payment Amt

Description	The amount of the co-pay for using a product that is not a drug formulary product under the terms of the pharmacy coverage.
Data Type	Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Planned End Dt

Description	The planned end date of the coverage.
Data Type	Standards - Data Domains.ddm/Data Domains/Date [DATE]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Plan Network Ind

Description	Indicates whether or not the related benefits are restricted to in-plan network providers. For example: Yes No
Data Type	Standards - Data Domains.ddm/Data Domains/Alphanumeric Small [VARCHAR(6)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Remaining Benefit Amt

Description	Remaining amount of benefit that can be paid out for a coverage agreement with a periodic maximum benefit.
Data Type	Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Remaining Deductible Amt

Description	Remaining amount of deductible for a coverage agreement with a periodic maximum deductible.
Data Type	Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Retroactive Start Dt

Description	Start date of policy coverage chosen as a date in the past preceding the policy inception date. For example: 1 June 2004 where inception date is 1 March 2005
Data Type	Standards - Data Domains.ddm/Data Domains/Date [DATE]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Selected Currency Code Sk

Description	Indicates the currency as selected by the customer to be used on any monetary information provided to the customer.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Source Code Sk

Description	The origin of the data identifying the actual load source, vendor, manual key entry, or context of the data in a specific row in the database.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	true
Is Derived	false
Is Surrogate Key	false

Tenant Sk

Description	The surrogate key of the entry identifying the legal owner of the data.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	true
Is Derived	false
Is Surrogate Key	false

Valid From Ts

Description	Establishes a period where a set of attributes are true in the source system. This would be populated with the transaction timestamp and would be used for the snapshot date.
Data Type	Standards - Data Domains.ddm/Data Domains/Timestamp [TIMESTAMP]
Is Part Of PrimaryKey	true
Is Required	true
Is Derived	false
Is Surrogate Key	false

Valid To Ts

Description	Ends a period of validity.
Data Type	Standards - Data Domains.ddm/Data Domains/Timestamp [TIMESTAMP]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Waiting Period

Description	The period of time after the start date of the coverage agreement during which the member is not covered. Any loss occurring before the end of the waiting period will not be subject to a benefit.
Data Type	Standards - Data Domains.ddm/Data Domains/Number Decimal Large [DECIMAL(15,6)]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Waiting Period Unit Code Sk

Description	Specifies the unit of measure of the coverage agreement waiting period.
Data Type	Standards - Data Domains.ddm/Data Domains/Surrogate Key [INTEGER]
Is Part Of PrimaryKey	false
Is Required	false
Is Derived	false
Is Surrogate Key	false

Relationship Details

Coverage Agreement Detail_Coverage Agreement_FK

Is Identifying Relationship	true
Child Table	Coverage Agreement Detail
Child Multiplicity	ZERO_TO_MANY
Child Referential Integrity: On Delete	NONE
Child Referential Integrity: On Insert	NONE
Child Referential Integrity: On Update	NONE
Parent Table	Coverage Agreement
Parent Multiplicity	ONE
Parent Referential Integrity: On Delete	NONE
Parent Referential Integrity: On Insert	NONE
Parent Referential Integrity: On Update	NONE

Primary Key Details

Coverage Agreement Detail PK

Key Attribute	Coverage Agreement Sk
Key Attribute	Valid From Ts

Overview

Top

Package

Entity

Atomic Warehouse Model Data Model