Vacation App

The company I work for has this awful database for logging vacation.
We've talked about recreating it and I am having a bit of a difficult time dealing with another developer. Basically the application allows our employees to log vacation time.

There are some rules however:

Benefit
Non Exempt Status
•Less than 5 years – 2 weeks or 80 hours-per-year (3.08 hours-biweekly),
•5 – 10 years – 3 weeks or 120 hours-per-year (4.62 hours-biweekly),
•More than 10 years – 4 weeks or 160 hours-per-year (6.16 hours-biweekly).

Exempt Status
•Less than 5 years – 3 weeks or 120 hours-per-year (4.62 hours-biweekly),
•5-10 years – 4 weeks or 160 hours-per-year (6.16 hours-biweekly),
•More than 10 years – 5 weeks or 200 hours-per year (7.70 hours-biweekly).

Basically an employee is either exempt or non-exempt and based on those rules you get a certain amount of accural. One of the developers here wants to store running totals and amount of total annual benefit hours right inside the employees table. I keep explaining that this sort of thing is wrong and referred him to some normalization tips.

My thinking was a master employee table with just employee information and a field as to whether the employee is Exempt or Non-Exempt.

Then the rules that I pasted above would be in their own seperate table linking the employee with the status as well as doing a EmployeesTable.HireDate BETWEEN TodaysDate - EmployeesTable.HireDate to find out which rule the employee would follow (<5 years, 5-10 years, >10 years). Hopefully that BETWEEN wont bite my thinking :|, as I've never had to deal with such an app.

In any event in addition to this I thought that the actual transactions (accruing vacation, taking vacation, selling vacation off) all of these transactions would go into one table. This way you may have + / - values and the SUM of these values would give you your available vacation hours. Basically each line item will have an identifier as to what "Type" the line item is (vacation day, accrual, accural benefit, sell vacation, etc).

My counterpart thinks otherwise and to have a table of just accurual vacation hours, a table of used vacation hours, etc.

The thing that may be a bit more difficult for me is the part where an employee gets accrued bi-weekly hours. For instance, if the employee is non-exempt and he / she has been here <5 years then they accrue 3.08 hours bi-weekly. Plus the maximum number of vacation hours that can be accrued is double the eligible annual benefit. So in this case:

Non Exempt Employees
•Less than 5 years – 4 weeks or 160 hours maximum accrual,
•5 – 10 years – 6 weeks or 240 hours maximum accrual,
•More than 10 years – 8 weeks or 320 hours maximum accrual.

Exempt Employees
•Less than 5 years – 6 weeks or 240 hours maximum accrual,
•5-10 years – 8 weeks or 320 hours maximum accrual,
•More than 10 years – 10 weeks or 400 hours maximum accrual.

Im wondering if I should just setup 2 jobs one to handle the biweekly stuff and the other to check maximum accruals?

How does this sound, or what may / may not bite me?

Anyone develop something similiar that can give me some pointers...surely Jeff's around :).

Thanks,
Jon


Programmers HowTo's -- http://jhermiz.googlepages.com