Tracking insurance elections

[Guest]

I'm trying to design a database to track life insurance elections. Employees
have the option of electing insurance for themselves, their spouse and or
children.

As far as table setup, here's what I was thinking:
tblEmployeeInfo
fields - name, address, dob, ssn etc
tblLifeElections
fields - EmployeeID, EmployeeElectCoverage (Y/N), CoverageAmt, Premium,
SpouseElect (Y/N), SpouseCoverageAmt, SpousePremium, ChildElect (Y/N),
ChildCoverageAmt, ChildPremium etc

What I'd like to know is where is the recommended place to store Spouse
basic info (name, gender, dob, ssn) and/or child(ren) info (names, dob etc).
This is only needed if they elect coverage for their spouse or dependents.
Should it be kept in a seperate table?

There is also beneficiary information which I was planning on storing in a
seperate table.

Thanks.

 

[Jeff Boyce]

Right off the top of my head, it seems to me that your table looks much like
.... a spreadsheet! Is there a reason you decided to use Access (a
relational database) instead of Excel?

If you are asking if you should have a separate table for the "personal"
information for each type of "person" (e.g., Insured, Spouse, Child,
Dependant Parent, ....), you can see the potential problem here. All of
these are persons, so adding a new "type" of person would require you to add
another (duplicate) table, modify all your queries, forms, reports, macros,
code, etc. This is a maintenance nightmare (but about what you'd do if you
were using a spreadsheet).

Instead, one table for Person (info) and one table for PersonRole (or type),
plus one more table for Person-in-Role would give you a way to add any new
role/type, any new person, and show that person in that role (relational,
normalized design).

You might also want to rethink using repeating fields for each "election" --
again, feeding Access 'sheet data will not get you the best use of Access'
features/functions. Instead, you could add a table of Elections (those
things that can be elected), and a table of Person's-Elections. Again, with
your design, a new 'election' will require a massive rewrite of your
application. With a normalized table structure, a new election will simply
be added to the list and available for People to elect.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Sorry, maybe I wasn't clear in my explanation. What table looks like a
spreadsheet?? The employee information table is just basic employee
information and while that could be done in Excel, the elections and
stipulations that come with the elections would be a nightmare to track in
Excel and I'd have no control over the checks and balances that need to
happen regarding each employees elections.

There are approx 10,000 employees. There will be basic info for each of
these employees (that will come from the EMPLOYER) that will be stored in the
Employees Table (tblEmployees).

The life insurance elections would be stored in tblLifeElections. Each of
these employees has the option of choosing Life Insurance for themselves,
Life Insurance for their Spouse and Life Insurance for their Dependents. All
in varying amounts and the increments of coverage and the allowed amounts of
coverage vary based on employee, spouse or dependent. For dependents, the
premium is the same regardless of how many children you have, but you have to
list name and birthdates for all children.

They also have the option of waiving coverage for themselves (and ultimately
the spouse and/or children).

I wasn't suggesting one table per person or even one table per person
'type'. My thought was that the EMPLOYEE basic information would be stored
in a table and all other people (IE spouse and/or dependent(s)) would be
stored in a seperate table with a type field that would indicate "Spouse" or
"Child". My reasoning for this was because:
1) there won't always be spouse or dependent info for every employee (why
have those columns in a spreadsheet and/or Access table if a % of the time
the info won't be populated)
2) required info collected regarding spouse and dependent is not nearly as
robust as information collected on the employee - for spouse and dependent
need about 4 fields of data for employee need about 15
3) spouse and/or dependents need to be linked to the employee and cannot
have coverage without employee coverage.

I tried to simplify the scenerio in the post, but perhaps I should have
given additional details. Also. the info I listed isn't the only information
being stored. There are guaranteed issue amounts that come in to play and
also other lines of coverage (which have very different 'field' requirements
than the life insurance). There is also the beneficiary information that
needs to be captured. The employee can elect multiple beneficiaries (primary
and contingent) and the % assigned to all benenficiaries should add to 100%.

Does that make more sense?

 

[Jeff Boyce]

It must have been the "elections" table that seemed to have multiple
election fields.

In a well-normalized relational database design, you are better off "going
deep, not wide". The issue I raised about modifying table, query, form, ...
applies to elections. Add an election to your design, add a column, modify
queries, ...

If you have a table of elections (not associated yet with any person),
adding an election (say, Eye Exams) means adding another row to the table.

You would need a "junction" table that stores a PersonID and an ElectionID.

If this were mine, I'd take another look at the information being stored
about persons. If it is largely the same, I'd use a single Person table
(spouses, children, ... all qualify as persons, right?). I can imagine a
situation in which two related persons both showed up in your list of
Employees (the agency where I work has at least two couples). Which one is
the "spouse"?!

Instead of associating the Role with the Person information, use a new table
that shows the Person, his/her Role, in-relation-to-which-Person. Then use
the IDs from THAT table to go into the coverage table. James Smith, spouse
of Jane Smith, has Dental Coverage?


CoveredIndividuals: CoveredID ... James'ID ... Spouse ... Jane'sID

Coverage:
CoverageID
CoveredID
ElectionID


Regards

Jeff Boyce
Microsoft Office/Access MVP