Обсуждение: Functions performed on intervals
I hope this is not the wrong list for this type of question... 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? Thanks for any advice you can give, Jimmie Fulton Systems Administrator Emory University School Of Medicine
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
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) >
Jimmie, > Thanks for your response, You're welcome. > 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. Yes. Absolutely you will. Unless, of course, your users are already forced to go though a manual month-end procedure, in which case it could be tied to that. This is actually really easy to set up: 1. Create a shell script that calls psql and a SQL script in 2; 2. Create a SQL script that calls all of the functions that you want to run on a (daily/weekly/monthly) basis. 3. Create a cron item that calls the shell script (1.). Handling errors etc. obviously adds a layer of complexity to the scripting. I also have the functions write to a schedule_log table showing what they've done. -Josh ______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