Hello,
I have developed an employee benefits database that currently contains over
3000 employees and 70+ companies. Having started the database as a really
simple solution for our clients, the last six months have demanded more
complexity and thus much of the recent work has been patch work to say the
least. I finally hit the "too many fields defined error" and now is the time
to go back and rebuild part of the database resulting from the last few
months of patchwork. The first two tables are simple:
1. Employee Info (name, address, gender, etc.)
2. Company Info (name, address, contact info, etc.)
From here, I don't have the best solution on where to go. Here is where I a
struggling:
- An employee can be categorized in to one of up to 7 classes.
- Some employers only have one class of employee, others 2, some 3, and so on
and so forth
- The employer will pay different amounts of health insurance premiums
depending on what class the employee is (some classes are paid 100%, others
50%, etc.)
- An employer may offer up to 4 different health insurance plans, each with
their own rates
- Each employer has different payroll schedules (weekly, bi-monthly, etc.)
- Each health plan has four premium elections (employee only, employee /
spouse, employee / children and family)
Basically, I need to set up my tables so I can run a query to determine what
the particular employee's payroll deduction is. So the logic is this:
- What company does the employee belong to?
- What is the company's payroll frequency?
- How many classes does the employer have?
- If more than one class, what class is the employee?
- Of the four premium elections, what election did the employee choose?
- How many health plans does the employer offer?
- If more than one health plan, what plan did the employer choose?
- What is the monthly premium for the election and plan the employee choose?
And the end result we are seeking...
- From the information above, what is the employee's deduction per payroll?
Mind you, this setup will be duplicated to handle dental, life, disability,
etc. products as well. Right now, I have query's (and forms/reports) doing
some pretty heafty formula calculations to determine the answer to the final
question. This alone is a red flag that the database queries are limited
because of a non-normalized database.
I am looking for some thoughts and feedback on how others like you would go
about building such a database. Thanks in advance!
B
--
Message posted via http://www.accessmonster.com
I have developed an employee benefits database that currently contains over
3000 employees and 70+ companies. Having started the database as a really
simple solution for our clients, the last six months have demanded more
complexity and thus much of the recent work has been patch work to say the
least. I finally hit the "too many fields defined error" and now is the time
to go back and rebuild part of the database resulting from the last few
months of patchwork. The first two tables are simple:
1. Employee Info (name, address, gender, etc.)
2. Company Info (name, address, contact info, etc.)
From here, I don't have the best solution on where to go. Here is where I a
struggling:
- An employee can be categorized in to one of up to 7 classes.
- Some employers only have one class of employee, others 2, some 3, and so on
and so forth
- The employer will pay different amounts of health insurance premiums
depending on what class the employee is (some classes are paid 100%, others
50%, etc.)
- An employer may offer up to 4 different health insurance plans, each with
their own rates
- Each employer has different payroll schedules (weekly, bi-monthly, etc.)
- Each health plan has four premium elections (employee only, employee /
spouse, employee / children and family)
Basically, I need to set up my tables so I can run a query to determine what
the particular employee's payroll deduction is. So the logic is this:
- What company does the employee belong to?
- What is the company's payroll frequency?
- How many classes does the employer have?
- If more than one class, what class is the employee?
- Of the four premium elections, what election did the employee choose?
- How many health plans does the employer offer?
- If more than one health plan, what plan did the employer choose?
- What is the monthly premium for the election and plan the employee choose?
And the end result we are seeking...
- From the information above, what is the employee's deduction per payroll?
Mind you, this setup will be duplicated to handle dental, life, disability,
etc. products as well. Right now, I have query's (and forms/reports) doing
some pretty heafty formula calculations to determine the answer to the final
question. This alone is a red flag that the database queries are limited
because of a non-normalized database.
I am looking for some thoughts and feedback on how others like you would go
about building such a database. Thanks in advance!
B
--
Message posted via http://www.accessmonster.com
Comment