Re: Functions performed on intervals

Jimmie,

> I hope this is not the wrong list for this type of question...

Nope.  You're come to *exactly* the right list.

> 
> I'm about to start development on a small app to track employee leave
> and
> vacation time.  Based on a simple formula, each employee gets x
> number of
> days at the end of each month.  x is a function of time-in-service
> and
> employee type: part-time, full-time, and salary.  I could just write
> a view
> to display total time accumulated from their start date to current
> date
> (minus leave taken), but what happens when the employee moves from
> part-time
> to full-time?  Their entire leave is recalculated with the new
> formula and
> the employee gets some extra time off.  The solution I've thought of
> is to
> call a function once a month with cron to update each employee's
> leave
> balance.  Is this the proper way to accomplish this task or are there
> better
> methods or approaches to getting the desired effect?

Actually, I can think of at least 3 different approaches.  What's "best"
depends on:

1) your control over the data structure (e.g. can you add an
"employee_history" table?)
2) What changes to leave time calcualtions do you want to be time-bound,
and what do you want to be retroactively re-calculated for all active
employees?
3)  What other factors are likely to change over time.

That being said, any solution you come up with will involve *some* kind
of history table/fields being added to the application.  It's a question
of *what* kind:

1) You can add a "leave time history" that journals leave time
calculations on a daily, monthly, or weekly basis;
2) You can add an "employee history" table that journals an employees
status on a periodic basis;
3) You can add/extend the relational sub-tables governing the
characterisitcs that are peculiar to the different types of employees
(full-time, part-time, contract) (there's a good example of this in
Practical Issues in Database Design by F. Pascal) to include date
ranges;
4) You can even add a "leave time rule history" table to keep track of
how leave time is calculated over the history of the company (e.g. what
if leave time was 14 days per year through 1999, but decreased to 10
days per year in 2000?)
5) Any/all of the above.

-Josh Berkus


______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco