RE: Functions performed on intervals - Mailing list pgsql-sql

Thanks for your response,

> 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,
> That being said, any solution you come up with will involve 
> *some* kind
> of history table/fields being added to the application.  

I have full control over the project so it is not an issue to add
fields/tables.  I already know that a history will be needed, but I haven't
decided how I want to implement it, yet.  I've thought of several ways:

1) Having a total_leave field for each user, and having a vacation table
which keeps a history of days taken for each vacation.  Then subtract totals
of vacation from the total_leave field in reports.

2) Having a table which includes history items of each time leave is added,
and once again, a table for vacations they have taken.  Subtract sums from
later to former.

3) As you said, several ways...


> 1) You can add a "leave time history" that journals leave time
> calculations on a daily, monthly, or weekly basis;

What you are saying here is that I will indeed need an external timed event
(cron) to update a field/history table of some fashion, correct?  This is my
main question.


> 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;

The use of subtypes does not appeal to me in this particular instance.  I do
now own Pascal's book though, thanks to you in a previous posting. :)

> 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 information technology      josh@agliodbs.com
>    and data management solutions       (415) 565-7293
>   for law firms, small businesses        fax 621-2533
>     and non-profit organizations.      San Francisco
> 
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to 
> majordomo@postgresql.org)
>