openIDL - Data Models - Data Dictionary

Auto Territory Coverage

Section Term

	Attribute	Description
1	Basic Limit	the minimum limits of liability that can be purchased by an insured
2	Basic Limit Loss	a property insurance limit that is less than the total property values at risk but high enough to cover the total property values actually exposed to damage in a single loss occurrence.
3	Car Years	the proportion of a calendar year during which a private passenger motor vehicle can be identified as being insured for physical damage insurance coverage.
4	Coverage	Code that identifies the exposure coverage associated with the manual class code.
5	Earned Premium	The term earned premium refers to the premium collected by an insurance company for the portion of a policy that has expired.
6	Excess Limit Loss	Losses in Excess of Policy Limits — this is an expression used in reinsurance agreements that refers to damages awarded by a court against an insurer in favor of the insured, due to the insurer's having failed to settle a third-party claim against the insured within the policy limits by reason of bad faith, fraud, or gross negligence.
7	Excess Limit	the highest amount of insurance that will be offered in a given situation in excess of basic limits.
8	Incurred Count	Losses Incurred — the total amount of losses sustained during a given time period that are paid and unpaid but reserved.
9	Incurred Loss	The total amount of paid claims and loss reserves associated with a particular time period, usually a policy year. It does not ordinarily include incurred but not reported (IBNR) losses.
10	LOB	Line of Business
11	State	One of the United States
12	Territory	Geographical location, sub unit of state
13	Zipcode	5 digit postal code

Section I. Tables

Policy

Field	Table	Attribute	Definition
policy id	policy	id	pk for policy
policy number	policy	number	policy number from source system
	policy	f_line_of_business_id	fk for line_of_business
	policy	f_subline_code_id	fk for subline

Line of Business

Field	Table	Attribute	Definition
line of business id	line_of_business	id	pk for line_of_business
line of business code	line_of_business	code	short hand for LOB
line of business name	line_of_business	name	human readable line of business

Transaction

Field	Table	Attribute	Definition
transaction id	transaction	id	PK for transaction
deductible amount	transaction	deductible_amount	Deductible amount that applies to the Physical Damage coverages
accounting year	transaction	accounting_year	Reflects reporting year
exposure_amount	transaction	exposure_amount	The actual number of vehicles covered by the premium posted in the statistical record. Usually this will be one, although it is possible for the same coverage to be provided in the same policy on more than one vehicle, in which case the Exposure entry would be the number of vehicles covered under the coding entry.
transaction type	transaction	type
deductible_amount	transaction	deductible_amount	Deductible amount that applies to the Physical Damage coverages
zipcode	transaction	zipcode	policy zipcode
	transaction	f_transactional_code_id	FK for tbl: transaction_code
	transaction	f_state_id	FK for tbl: state
	transaction	f_coverage_code_id	FK for tbl: coverage_code
	transaction	f_policy_id	FK for tbl: policy

Claim Detail

Field	Table	Attribute	Definition
clam detail id	claim_detail	id	PK for claim details
claim count	claim_detail	claim_count	Actual number of new claims reported in a given reporting period. The rules for negative counts are the same as those for the Exposure field. The claim count on a paid loss record should be 1 only when the first payment is made provided no reserve had been established in a previous quarter
accident date year	claim_detail	accident_date_year	Date on which the accident occurred (except for grouped claims)
loss amount	claim_detail	loss_amount	Outstanding loss and outstanding allocated loss adjustment expense records should always have the amount reserved for the claim as of the end of the quarter. This amount must be reported each quarter it remains outstanding. If a partial payment is made on an outstanding loss, the new reserve amount must be reported if the claim is not closed by the end of the quarter.
	claim_detail	f_transaction_id	FK to relate to parent transaction

Premium Detail

Field	Table	Attribute	Definition
premium detail id	premium_detail	id	PK for premium details
premium amount	premium_detail	amount	Amount paid
	premium_detail	f_transaction_id	FK to parent transaction

Coverage Code

Field	Table	Attribute	Definition
coverage id	coverage_code	id	PK for coverage table
coverage code	coverage_code	code	Code that identifies the exposure coverage associated with the manual class code.
coverage name	coverage_code	name	Human readable name of coverage
coverage note	coverage_code	note	Notes on coverage

State

Field	Table	Attribute	Definition
state id	state	id	PK for state
state name	state	name	Human readable name
state code	state	code	State Abbreviation

Transaction Code

Field	Table	Attribute	Definition
transaction id	transaction_code	id	PK for transaction codes
transaction code	transaction_code	code	Identifies a loss transaction on limited coded records. The field will be populated on loss-related Transaction Codes if the record is not fully coded.
transaction name	transaction_code	name	Human readable name for transaction code

Subline

Field	Table	Attribute	Definition
subline id	subline	id	PK for subline
subline name	subline	name	Human readable name for subline
subline code	subline	code	Short hand for subline code

Company

Field	Table	Attribute	Definition
company id	company	id	PK for company
company name	company	name	name of company
company description	company	description	company description

Type of Business

Field	Table	Attribute	Definition
type_of_business id	type_of_business	id	PK for type_of_business
type_of_business name	type_of_business	name	name of type_of_business
type_of_business description	type_of_business	description	type_of_business description

Class Group

Field	Table	Attribute	Definition
class_group id	class_group	id	PK for class_group
class_group name	class_group	name	name of class_group
class_group description	class_group	description	class_group description
class_group type	class_group	class_group_type	voluntary risk, assigned risk, miscellaneous personal vehicles

Class Group Type

Field	Table	Attribute	Definition
class_group_type id	class_group_type	id	PK for class_group_type
class_group_type name	class_group_type	name	name of class_group_type
class_group_type description	class_group_type	description	class_group description

Section II: Reports Covered

id	name	status	notes
1	AAIS Territory Coverage	Complete	initial report
2	Auto Stat Report	Incomplete

Section III: Data

class_group_type

id	name	description
1	Voluntary Risk	Utilize state assigned risk classification plans
2	Assigned Risk	Utilize state assigned risk classification plans
3	Miscellaneous Personal Vehicles	includes motorcycles

class_group

id	name	description	type
1	1	No Male driver under 25; non-business use	1
2	1F	No male driver under 25; Farm use	1
3	1A	No male driver under 25; non-business use, non farm, no driving to work	1
4	1B	No male driver under 25; non-business use; drives to work - limited number of miles each day	1
5	1C	No male driver under 25; non-business use; drives to work - over a limit of miles each way	1
6	1D	No male driver under 25; non-business use, drives to work - no distance restriction	1
7	3	No male driver under 25; business use	1
8	2	Driver under 25; business and non business use	1
9	2A	Male driver under 25; business and non business use; married or single male not owner or principal driver	1
10	2B	Married male driver under 25; business and non-business use	1
11	2C	Unmarried male driver under 25; business and non-business; owner or principal driver	1
12	2D	Female driver under 25	1
13	2E	Single male operator age 25-29 inclusive; owner or principal operator	1
14	Sr. Citizen	Operator age 65 and over	1
15	All Other PPNF	No class plan or other class plan used; military post coverage, all other private passener cars	1

type_of_business

id	name	description
1	Voluntary
2	Residual

state

	name	code
1	ALABAMA	AL
2	ALASKA	AK
3	AMERICAN SAMOA	AS
4	ARIZONA	AZ
5	ARKANSAS	AR
6	CALIFORNIA	CA
7	COLORADO	CO
8	CONNECTICUT	CT
9	DELAWARE	DE
10	DISTRICT OF COLUMBIA	DC
11	FLORIDA	FL
12	GEORGIA	GA
13	GUAM	GU
14	HAWAII	HI
15	IDAHO	ID
16	ILLINOIS	IL
17	INDIANA	IN
18	IOWA	IA
19	KANSAS	KS
20	KENTUCKY	KY
21	LOUISIANA	LA
22	MAINE	ME
23	MARYLAND	MD
24	MASSACHUSETTS	MA
25	MICHIGAN	MI
26	MINNESOTA	MN
27	MISSISSIPPI	MS
28	MISSOURI	MO
29	MONTANA	MT
30	NEBRASKA	NE
31	NEVADA	NV
32	NEW HAMPSHIRE	NH
33	NEW JERSEY	NJ
34	NEW MEXICO	NM
35	NEW YORK	NY
36	NORTH CAROLINA	NC
37	NORTH DAKOTA	ND
38	NORTHERN MARIANA IS	MP
39	OHIO	OH
40	OKLAHOMA	OK
41	OREGON	OR
42	PENNSYLVANIA	PA
43	PUERTO RICO	PR
44	RHODE ISLAND	RI
45	SOUTH CAROLINA	SC
46	SOUTH DAKOTA	SD
47	TENNESSEE	TN
48	TEXAS	TX
49	UTAH	UT
50	VERMONT	VT
51	VIRGINIA	VA
52	VIRGIN ISLANDS	VI
53	WASHINGTON	WA
54	WEST VIRGINIA	WV
55	WISCONSIN	WI
56	WYOMING	WY

Section IV: Acronyms/Definitions

id	Acronym	Explanation
1	PK	primary key, the unique identifier for a row
2	FK	foreign key, a reference to a unique row that is defined in another table.