The Big Picture
The Exploration Air Employee Benefits application stores its data in the database
ExAirBenefits, a SQL Server database created by Setup when IISSamples is installed.
What the Database Does
The database stores data pertaining to the employee
benefits. This data includes definition of the system (what benefits are available, how many
days an employee has to change their insurance choices, and so on), and information about the
individual employee's benefits choices (how much disability coverage, who the
primary care physician is, which dependents are covered, and so on).
A second purpose of the database is to ensure that the data stored is
valid and internally consistent. For example, it ensures that an employee's
primary care physician is actually one of the available physicians, or that the employee
cannot sign up for two different dental plans at the same time.
Another way the database ensures data consistency is by using identity columns for
primary keys (where appropriate). This allows the database itself to assign the key field
value, and ensures that no user or calling program action will result in a primary
key violation error.
Another thing the database does is contain stored procedures, which are programs
written in the database language. These programs are sometimes used as an alternative to
simply executing a string of SQL code. The advantage of a stored procedure is that they
are compiled in advance, as opposed to compiled on demand, so they make more efficient
use of database server resources, and therefore run faster.
Design Goals
The design goal for the database is to store data in an appropriately structured manner,
so that data integrity is maintained.
General Principles
Identity columns are used for the primary key whenever possible. Identity columns are
integer fields which are incremented by the database. The use of an identity column for
the primary key ensures that a primary key violation
will not occur in the table. If the primary key is not an identity column, it is a
compound key consisting of foreign keys to other tables in the database.
Most tables include a Name column and a Label column. These two columns may
contain the same value, but they have separate purposes. The Name column
is to show to the application administrator, and the Label column is to show to the
application user. For example, each
benefit is defined to apply to a specific year. If you want the site administration
pages to remind you which year the benefit is for, but you want the client browser to
display only the name of the benefit, you can use the Name and Label fields to show the Name
only on the administration page, and show only the Label on the client page.
Rank fields are used to define a sort order for records in the table. For example, if you
want to show dependents in order of dependent type, such as showing employee, then
spouse and then children, you can sort the recordset by DependentTypeRank.
Benefits Application Tables
The following information summarizes the structure of the tables in the Benefits application.
The notation for column names in other tables is TableName.ColumnName.
Benefit
Overview
The Benefit table stores information about the benefits that are part of the Benefits
application. Note that BenefitYear indicates the year to which the benefit applies.
Primary Key
BenefitId
Foreign Key
Benefit does not reference any columns in other tables:
The following columns reference Benefit:
BenefitPlan.BenefitId
BenefitQualifier.BenefitId
BenefitTaxStatus.BenefitId
EmployeeBenefit.BenefitId
Columns
The following fields are in the Benefit table:
|
Column Name
|
Data Type
|
Data Length
|
|
BenefitId
|
Integer
|
|
|
BenefitYear
|
Integer
|
|
|
BenefitName
|
Character
|
100
|
|
BenefitLabel
|
Character
|
100
|
|
BenefitDesc
|
Character
|
255
|
|
BenefitText
|
Text
|
|
|
BenefitRank
|
Integer
|
|
|
DetailTemplate
|
Character
|
100
|
|
MinimumPlanRanking
|
Integer
|
|
|
QualifierRequired
|
Tiny Integer
|
|
|
DependentCovered
|
Tiny Integer
|
|
|
SignatureRequired
|
Tiny Integer
|
|
|
BenefitStatus
|
Tiny Integer
|
|
BenefitPlan
Overview
The BenefitPlan table defines which Plans correspond to which Benefits, and the rank of
the plan within all the plans that are available for the benefit.
Primary Key
BenefitId
PlanId
Foreign Key
BenefitPlan references the following columns in other tables:
Benefit.BenefitId
Plan.PlanId
No columns in other tables reference BenefitPlan
Columns
The following fields are in the BenefitPlan table:
|
Column Name
|
Data Type
|
Data Length
|
|
BenefitId
|
Integer
|
|
|
PlanId
|
Integer
|
|
|
PlanRank
|
Integer
|
|
BenefitQualifier
Overview
The BenefitQualifier table defines which qualifiers make an employee eligible to change
their choices for the specified benefit, and for how many days after the occurrence of
the event.
Primary Key
BenefitId
QualifierId
Foreign Key
BenefitQualifier references the following columns in other tables:
Benefit.BenefitId
Qualifier.QualifierId
No columns in other tables reference BenefitQualifier
Columns
The following fields are in the BenefitQualifier table:
|
Column Name
|
Data Type
|
Data Length
|
|
BenefitId
|
Integer
|
|
|
QualifierId
|
Integer
|
|
|
ChangeDays
|
Integer
|
|
BenefitStatus
Overview
The BenefitStatus table defines the BenefitStatusId for the Benefit table.
Primary Key
BenefitStatusId
Foreign Key
BenefitStatus does not reference any columns in other tables:
The following columns reference BenefitStatus:
Benefit.BenefitStatusId
Columns
The following fields are in the BenefitStatus table:
|
Column Name
|
Data Type
|
Data Length
|
|
BenefitStatusId
|
Integer
|
|
|
BenefitStatusName
|
Character
|
100
|
|
BenefitStatusLabel
|
Character
|
100
|
BenefitTaxStatus
Overview
The BenefitTaxStatus table defines which tax status can apply to each benefit, and whether
a given tax status is the default for the benefit.
Primary Key
BenefitId
TaxStatusId
Foreign Key
BenefitTaxStatus references the following columns in other tables:
Benefit.BenefitId
TaxStatus.TaxStatusId
No columns in other tables reference BenefitTaxStatus
Columns
The following fields are in the BenefitTaxStatus table:
|
Column Name
|
Data Type
|
Data Length
|
|
BenefitId
|
Integer
|
|
|
TaxStatusId
|
Integer
|
|
|
TaxDefault
|
Integer
|
|
Dependent
Overview
The Dependent table stores information about dependents. The employee also has a
record in the Dependent table.
Primary Key
DependentId
Foreign Key
Dependent references the following columns in other tables:
Gender.GenderId
The following columns reference Dependent:
EmployeeBenefitDependent.DependentId
EmployeeDependent.DependentId
Columns
The following fields are in the Dependent table:
|
Column Name
|
Data Type
|
Data Length
|
|
DependentId
|
Integer
|
|
|
LastName
|
Character
|
100
|
|
FirstName
|
Character
|
100
|
|
MiddleName
|
Character
|
100
|
|
DependentBirthdate
|
Datetime
|
100
|
|
DependentGenderId
|
Integer
|
100
|
|
DependentSSN
|
Character
|
11
|
DependentType
Overview
The DependentType table defines dependent types. The rank column is used to define a sort
order of the various dependent types.
Primary Key
DependentTypeId
Foreign Key
DependentType does not reference any columns in other tables:
The following columns reference DependentType:
EmployeeDependent.DependentTypeId
Columns
The following fields are in the DependentType table:
|
Column Name
|
Data Type
|
Data Length
|
|
DependentTypeId
|
Integer
|
|
|
DependentTypeName
|
Character
|
100
|
|
DependentTypeLabel
|
Character
|
100
|
|
DependentTypeRank
|
Integer
|
|
EBDStatus
Overview
The EBDStatus table defines the EBDStatusId for the EmployeeBenefitDependent table.
Primary Key
EBDStatusId
Foreign Key
EBDStatus does not reference any columns in other tables:
The following columns reference EBDStatus:
EmployeeBenefitDependent.EBDStatusId
Columns
The following fields are in the EBDStatus table:
|
Column Name
|
Data Type
|
Data Length
|
|
EBDStatusId
|
Integer
|
|
|
EBDStatusName
|
Character
|
100
|
|
EBDStatusLabel
|
Character
|
100
|
EDStatus
Overview
The EDStatus table defines the DependentStatusId for the EmployeeDependent table.
Primary Key
DependentStatusId
Foreign Key
EDStatus does not reference any columns in other tables:
The following columns reference EDStatus:
EmployeeDependent.DependentStatusId
Columns
The following fields are in the EDStatus table:
|
Column Name
|
Data Type
|
Data Length
|
|
DependentStatusId
|
Integer
|
|
|
DependentStatusName
|
Character
|
100
|
|
DependentStatusLabel
|
Character
|
100
|
Employee
Overview
The Employee table stores information about employees. Some of the employee demographic
information, such as name and birth date, is stored in the Dependent table.
Primary Key
EmployeeId
Foreign Key
Employee does not reference any columns in other tables:
Employee references the following columns:
GeoArea.GeoAreaId
The following columns reference Employee:
EmployeeBenefit.EmployeeId
EmployeeDependent.EmployeeId
EmployeeQualifier.EmployeeId
Columns
The following fields are in the Employee table:
|
Column Name
|
Data Type
|
Data Length
|
|
EmployeeId
|
Integer
|
|
|
KnownAs
|
Character
|
100
|
|
NTUserName
|
Character
|
100
|
|
Address1
|
Character
|
100
|
|
Address2
|
Character
|
100
|
|
City
|
Character
|
100
|
|
State
|
Character
|
100
|
|
PostCode
|
Character
|
100
|
|
Country
|
Character
|
100
|
|
HomePhone
|
Character
|
100
|
|
BusinessPhone
|
Character
|
100
|
|
Fax
|
Character
|
100
|
|
Email
|
Character
|
100
|
|
PeriodEarnings
|
Money
|
100
|
|
Exemptions
|
Integer
|
|
|
EmployeeGeoAreaId
|
Integer
|
|
|
EmployeeStatus
|
Tiny Integer
|
|
EmployeeBenefit
Overview
The EmployeeBenefit table defines which benefits each employee has, and stores
information about the employee's benefit choices.
Primary Key
EmployeeId
BenefitId
Foreign Key
EmployeeBenefit references the following columns in other tables:
Benefit.BenefitId
Employee.EmployeeId
TaxStatus.TaxStatusId
Plans.PlanId
The following columns reference EmployeeBenefit:
EmployeeBenefitDependent.EmployeeId
EmployeeBenefitDependent.BenefitId
Columns
The following fields are in the EmployeeBenefit table:
|
Column Name
|
Data Type
|
Data Length
|
|
EmployeeId
|
Integer
|
|
|
BenefitId
|
Integer
|
|
|
PlanId
|
Integer
|
|
|
CreditAmount
|
Money
|
|
|
TaxStatusId
|
Integer
|
|
|
EligibilityDate
|
Datetime
|
|
EmployeeBenefitDependent
Overview
The EmployeeBenefitDependent table defines which benefits each employee has, and
information about the employee's benefit choices.
Primary Key
EmployeeId
BenefitId
DependentId
Foreign Key
EmployeeBenefitDependent references the following columns:
EmployeeBenefit.EmployeeId and BenefitId
Dependent.DependentId
Physician.PhysicianId
No columns in other tables reference EmployeeBenefitDependent
Columns
The following fields are in the EmployeeBenefitDependent table:
|
Column Name
|
Data Type
|
Data Length
|
|
EmployeeId
|
Integer
|
|
|
BenefitId
|
Integer
|
|
|
DependentId
|
Integer
|
|
|
PhysicianId
|
Integer
|
|
|
EBDStatus
|
Integer
|
|
EmployeeDependent
Overview
The EmployeeDependent table defines the relationship between employees and dependents.
Primary Key
EmployeeId
DependentId
Foreign Key
EmployeeDependent references the following columns in other tables:
Employee.EmployeeId
Dependent.DependentId
DependentType.DependentTypeId
No columns in other tables reference EmployeeDependent
Columns
The following fields are in the EmployeeDependent table:
|
Column Name
|
Data Type
|
Data Length
|
|
EmployeeId
|
Integer
|
|
|
DependentId
|
Integer
|
|
|
DependentTypeId
|
Integer
|
|
|
DependentStatus
|
Integer
|
|
EmployeeQualifier
Overview
The EmployeeQualifier table contains one record for each change qualifier that each
employee has.
Primary Key
EmployeeQualifierId
Foreign Key
EmployeeQualifier references the following columns in other tables:
Employee.EmployeeId
Qualifier.QualifierId
No columns in other tables reference EmployeeQualifier
Columns
The following fields are in the EmployeeQualifier table:
|
Column Name
|
Data Type
|
Data Length
|
|
EmployeeQualifierId
|
Integer
|
|
|
EmployeeId
|
Integer
|
|
|
QualifierId
|
Integer
|
|
|
QualifierDate
|
Datetime
|
|
EmployeeStatus
Overview
The EmployeeStatus table defines the EmployeeStatusId for the Employee table.
Primary Key
EmployeeStatusId
Foreign Key
EmployeeStatus does not reference any columns in other tables:
The following columns reference EmployeeStatus:
Employee.EmployeeStatusId
Columns
The following fields are in the EmployeeStatus table:
|
Column Name
|
Data Type
|
Data Length
|
|
EmployeeStatusId
|
Integer
|
|
|
EmployeeStatusName
|
Character
|
100
|
|
EmployeeStatusLabel
|
Character
|
100
|
Field
Overview
The Field table holds information that applies to a specific Plan, such as copayment
amounts and pre-authorization requirements. Instead of creating a number of seldom-used
columns in another table, records are added to the Field table only as needed for a
given plan. The PlanField table
defines which Plan each record in the Field table pertains to.
Primary Key
FieldId
Foreign Key
Field does not reference any columns in other tables:
The following columns reference Field:
PlanField.PlanId
Columns
The following fields are in the Field table:
|
Column Name
|
Data Type
|
Data Length
|
|
FieldId
|
Integer
|
|
|
FieldName
|
Character
|
100
|
|
FieldLabel
|
Character
|
100
|
Gender
Overview
The Gender table defines gender records, such as Male or Female.
Primary Key
GenderId
Foreign Key
Gender does not reference any columns in other tables:
The following columns reference Gender:
Dependent.DependentGenderId
Columns
The following fields are in the Gender table:
|
Column Name
|
Data Type
|
Data Length
|
|
GenderId
|
Integer
|
|
|
GenderName
|
Character
|
100
|
|
GenderLabel
|
Character
|
100
|
|
GenderRank
|
Integer
|
|
GeoArea
Overview
The GeoArea table defines geographic areas.
Primary Key
GeoAreaId
Foreign Key
GeoArea does not reference any columns in other tables:
The following columns reference GeoArea:
Employee.EmployeeGeoAreaId
PlanGeoArea.GeoAreaId
Columns
The following fields are in the GeoArea table:
|
Column Name
|
Data Type
|
Data Length
|
|
GeoAreaId
|
Integer
|
|
|
GeoAreaName
|
Character
|
100
|
|
GeoAreaLabel
|
Character
|
100
|
Physician
Overview
The Physician table contains a list of approved physicians.
Primary Key
PhysicianId
Foreign Key
Physician does not reference any columns in other tables:
The following columns reference Physician:
EmployeeBenefitDependent.PhysicianId
Columns
The following fields are in the Physician table:
|
Column Name
|
Data Type
|
Data Length
|
|
PhysicianId
|
Integer
|
|
|
PhysicianName
|
Character
|
100
|
|
PhysicianLabel
|
Character
|
100
|
PlanField
Overview
The PlanField table defines the relationship between Plan records and Field records.
Primary Key
PlanId
FieldId
Foreign Key
PlanField references the following columns in other tables:
Field.FieldId
Plans.PlanId
No columns in other tables reference PlanField
Columns
The following fields are in the PlanField table:
|
Column Name
|
Data Type
|
Data Length
|
|
PlanId
|
Integer
|
|
|
FieldId
|
Integer
|
|
|
FieldValue
|
Character
|
100
|
|
FieldRank
|
Integer
|
|
PlanGeoArea
Overview
The PlanGeoArea table defines the relationship between Plan records and GeoArea records.
Primary Key
PlanId
GeoAreaId
Foreign Key
PlanGeoArea references the following columns in other tables:
GeoArea.GeoAreaId
Plans.PlanId
No columns in other tables reference PlanGeoArea
Columns
The following fields are in the PlanGeoArea table:
|
Column Name
|
Data Type
|
Data Length
|
|
PlanId
|
Integer
|
|
|
GeoAreaId
|
Integer
|
|
Plans
Overview
The Plans table stores information about each plan.
Primary Key
PlanId
Foreign Key
Plans does not reference any columns in other tables:
The following columns reference Plans:
BenefitPlan.PlanId
EmployeeBenefit.PlanId
PlanField.PlanId
PlanGeoArea.PlanId
Columns
The following fields are in the Plans table:
|
Column Name
|
Data Type
|
Data Length
|
|
PlanId
|
Integer
|
|
|
PlanName
|
Character
|
100
|
|
PlanLabel
|
Character
|
100
|
|
PlanDesc
|
Character
|
255
|
|
PlanText
|
Text
|
|
|
PlanCost
|
Money
|
|
|
PhysicianRequired
|
Tiny Integer
|
|
|
PlanStatus
|
Tiny Integer
|
|
PlanStatus
Overview
The PlanStatus table defines the PlanStatusId for the Plans table.
Primary Key
PlanStatusId
Foreign Key
PlanStatus does not reference any columns in other tables:
The following columns reference PlanStatus:
Plans.PlanStatusId
Columns
The following fields are in the PlanStatus table:
|
Column Name
|
Data Type
|
Data Length
|
|
PlanStatusId
|
Integer
|
|
|
PlanStatusName
|
Character
|
100
|
|
PlanStatusLabel
|
Character
|
100
|
Qualifier
Overview
The Qualifier table defines change qualifiers. A valid change qualifier is required before
an employee can change their benefit options.
Primary Key
QualifierId
Foreign Key
Qualifier references the following columns in other tables:
QualifierClass.QualifierClassId
The following columns reference Qualifier:
BenefitQualifier.QualifierId
EmployeeQualifier.QualifierId
Columns
The following fields are in the Qualifier table:
|
Column Name
|
Data Type
|
Data Length
|
|
QualifierId
|
Integer
|
|
|
QualifierName
|
Character
|
100
|
|
QualifierLabel
|
Character
|
100
|
|
QualifierClassId
|
Integer
|
|
|
QualifierRank
|
Integer
|
|
|
QualifierStatus
|
Integer
|
|
QualifierClass
Overview
The QualifierClass table defines classes of change qualifiers. Examples are administrative
change qualifiers, system-applied change qualifiers, and employee-accessible change qualifiers.
Primary Key
QualifierClassId
Foreign Key
QualifierClass does not reference any columns in other tables:
The following columns reference QualifierClass:
Qualifier.QualifierClassId
Columns
The following fields are in the QualifierClass table:
|
Column Name
|
Data Type
|
Data Length
|
|
QualifierClassId
|
Integer
|
|
|
QualifierClassName
|
Character
|
100
|
|
QualifierClassLabel
|
Character
|
100
|
QualifierStatus
Overview
The QualifierStatus table defines the QualifierStatusId for the Qualifier table.
Primary Key
QualifierStatusId
Foreign Key
PlanStatus does not reference any columns in other tables:
The following columns reference QualifierStatus:
Qualifier.QualifierStatusId
Columns
The following fields are in the QualifierStatus table:
|
Column Name
|
Data Type
|
Data Length
|
|
QualifierStatusId
|
Integer
|
|
|
QualifierStatusName
|
Character
|
100
|
|
QualifierStatusLabel
|
Character
|
100
|
TaxStatus
Overview
The TaxStatus table defines tax status codes such as Pre-tax and After-tax.
Primary Key
TaxStatusId
Foreign Key
TaxStatus does not reference any columns in other tables:
The following columns reference TaxStatus:
BenefitTaxStatus.TaxStatusId
EmployeeBenefit.TaxStatusId
Columns
The following fields are in the TaxStatus table:
|
Column Name
|
Data Type
|
Data Length
|
|
TaxStatusId
|
Integer
|
|
|
TaxStatusName
|
Character
|
100
|
|
TaxStatusLabel
|
Character
|
100
|
" TITLE="Return to Exploration Air Benefits">
B A C K