openIDL - Data Models - Data Dictionary

Auto Territory Coverage

Section I. Tables

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

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

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

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

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

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

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

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

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: 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

Section III: 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.