ottenm
asked on
table design for tracking employee time off and accrual
We need to create an app to track the earning (and use) of vacation time and sick time. We've looked at many packages, and need to create our own (many reasons). Our employees accrue time off on a daily basis, split evenly throughout the year. For example, 80 hours of vacation time per year * 60 min per hour / 365 days = 13.15 min per day. Same for sick time, but 48 hours per year. Most of the reports are fairly standard: Managers need to see summarized reports of many employees at once. Employees need to see their vacation time as it accrues and as they use it, a bit like a bank account statement. They also need to be able to see "forward", to know how much time they will accrue over the next weeks/months.
Here's a couple of ideas, but both seem to have issues.
We could run a nightly script to "deposit" 13 minutes per day into each employee "account", one row per deposit then one row for each "withdraw" when they take time off. This way, it seems like it would be easy to bring up reports for any time frame (week at a time, month at a time, year at a time). It would be burdensome to report all 365 deposits to an employee if they look at an entire year (actually 730 entries considering vacation *and* sick time), but the end report could summarize consecutive deposits into a single line item (e.g., ".... then 38 days until your next day off, at 13.15 minutes per day = 499.7 minutes credit).
But with that approach, and 50 employees * 365 days (* 2, one for vacation time, and a separate one for sick time), we've got a table with 36,500 rows at the end of the first year! And that's just the daily deposits (additional rows for each withdraw).
So, what about "summarizing consecutive deposits" right in the table? In other words, have each row represent a series of consecutive days (start day, stop day, total number of hours/minutes accrued or used during that period). If an employee did nothing but accrue all year, and then use all the time at the very end of the year, they'd only have 2 rows in the DB and 2 rows in the report: (roughly) 355 days of accrual, and 10 days of vacation.
But with every employee splitting the year up into different segments, this approach is not going to support managerial reports that are weekly/monthly/yearly.
Thanks for any feedback, suggestions, or pointers. We're currently using SQL 2000.
Here's a couple of ideas, but both seem to have issues.
We could run a nightly script to "deposit" 13 minutes per day into each employee "account", one row per deposit then one row for each "withdraw" when they take time off. This way, it seems like it would be easy to bring up reports for any time frame (week at a time, month at a time, year at a time). It would be burdensome to report all 365 deposits to an employee if they look at an entire year (actually 730 entries considering vacation *and* sick time), but the end report could summarize consecutive deposits into a single line item (e.g., ".... then 38 days until your next day off, at 13.15 minutes per day = 499.7 minutes credit).
But with that approach, and 50 employees * 365 days (* 2, one for vacation time, and a separate one for sick time), we've got a table with 36,500 rows at the end of the first year! And that's just the daily deposits (additional rows for each withdraw).
So, what about "summarizing consecutive deposits" right in the table? In other words, have each row represent a series of consecutive days (start day, stop day, total number of hours/minutes accrued or used during that period). If an employee did nothing but accrue all year, and then use all the time at the very end of the year, they'd only have 2 rows in the DB and 2 rows in the report: (roughly) 355 days of accrual, and 10 days of vacation.
But with every employee splitting the year up into different segments, this approach is not going to support managerial reports that are weekly/monthly/yearly.
Thanks for any feedback, suggestions, or pointers. We're currently using SQL 2000.
I think it's pointless to save cumulative credit for each employee.....its the same for every emplolyee (if you don't take vacations, sickness etc. into account)
so you can store only these days when emloyee has day off.
Everything else can be computed in select (view)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Pointless storing credit?
At least hire/fire date plays a role. There will never be employees working parttime, lets say from monday till wednesday? Others working 5 half days?
Emergency weekend work that get's you extra credit?
At least hire/fire date plays a role. There will never be employees working parttime, lets say from monday till wednesday? Others working 5 half days?
Emergency weekend work that get's you extra credit?
nightly job adding some minutes every day seems to me very unreliable
what if number of hours of vacation time per year changes ??
what if the job will run twice by mistake ??
what if you insert some date off in the history ??
what if an employee is hired in half of the year ??
what if an employee has some benefits in form of extra vacation ??
etc...
Don't now the 'legal' system, but I hope it's fairer as in jvejskrab's example
Someone who's getting hired on a friday and fired next monday has credit for 4 days work? If its employee 2 in your simple example he will have credit for the whole year .... exept for '20110406'.
Storing credit i mean it 2 ways
- steering info : date hired/fired, parttime, ...
- cumulative credit per employee (eventualy monthly dateFrom/dateTo) with possibility to give maximum negative credit-range
You don't need to store what can be calculated that can be combined in a view with the steering info to get the whole absence-picture waterproof and reportable for managers.
Someone who's getting hired on a friday and fired next monday has credit for 4 days work? If its employee 2 in your simple example he will have credit for the whole year .... exept for '20110406'.
Storing credit i mean it 2 ways
- steering info : date hired/fired, parttime, ...
- cumulative credit per employee (eventualy monthly dateFrom/dateTo) with possibility to give maximum negative credit-range
You don't need to store what can be calculated that can be combined in a view with the steering info to get the whole absence-picture waterproof and reportable for managers.
I wrote
........to save cumulative credit......
jogos:
At least hire/fire date plays a role. There will never be employees working parttime, lets say from monday till wednesday? Others working 5 half days?
Emergency weekend work that get's you extra credit?
Sure, all of the examples are meaningful, anyway in both examples (computing cumulative credit every day with some job X storing day offs and ad hoc comuting) you will need the same data about employees (duty type, hire date, working days etc....) to compute accrued vacation minutes.
It was just an example, you have to take many more variables into account.....
I just wanted to point out that storing cumulative credit can be dangerous in some situations.
"what if the job will run twice by mistake ??"
A job may never just add or create things. The job has to see what should be the situation, if not correct then update/insert where necesairy. In every system there will be days someone lauches same job twice (because it previously gave error in 'another' part of that job) or there are days the job can't run because of maintenance or errors.
And everything that can quickly be calculated does not have to be stored but to avoid a different interpretation representing it in a view can make it easy to use the one and only correct number where you want. So if no job is needed the better.
A job may never just add or create things. The job has to see what should be the situation, if not correct then update/insert where necesairy. In every system there will be days someone lauches same job twice (because it previously gave error in 'another' part of that job) or there are days the job can't run because of maintenance or errors.
And everything that can quickly be calculated does not have to be stored but to avoid a different interpretation representing it in a view can make it easy to use the one and only correct number where you want. So if no job is needed the better.
You wrote it exactly:
jogos:
You don't need to store what can be calculated.......
and thats the point of my reaction
ASKER
Thanks for the help jogos and jvejskrab. This is enormously helpful. I really like the idea of storing only the exceptions to the standard accrual. With jvejskrab's approach we could also insert "bonus" times into EmployeeTimeOff for things like someone earning an extra day off (maybe flag them as negative, or just use a separate table).
I would also need to modify it to accommodate different accrual rates. The 80-hour example is only for the first year. Every year they earn a little more. But we can put those rates into another table, and pull their hire date from the employee table, and modify jvejskrab's code to apply different rates for different employees.
I need to think about manager's reports a bit. How to summarize for a particular month? I could have an entry in EmployeeTimeOff that starts at the end of the previous month, but stretches into the month I'm reporting. Plus entries that start and end within my report month. Plus entries that start at the end of the month, but stretch into the next month. Maybe break up month-strattling rows into two rows back when I do the insert?
Most of the functional specs are in good shape and already working for us (the daily formula, negative balances, partial years, carry overs, "take before you earn on the last day", etc. ).
I would also need to modify it to accommodate different accrual rates. The 80-hour example is only for the first year. Every year they earn a little more. But we can put those rates into another table, and pull their hire date from the employee table, and modify jvejskrab's code to apply different rates for different employees.
I need to think about manager's reports a bit. How to summarize for a particular month? I could have an entry in EmployeeTimeOff that starts at the end of the previous month, but stretches into the month I'm reporting. Plus entries that start and end within my report month. Plus entries that start at the end of the month, but stretch into the next month. Maybe break up month-strattling rows into two rows back when I do the insert?
Most of the functional specs are in good shape and already working for us (the daily formula, negative balances, partial years, carry overs, "take before you earn on the last day", etc. ).
So we end up where I started ... check what realy mathers for your credit-formula and the border-dates
-> then you can define what has to be stored in database and how it's treated
Days off must be stored in detail (but maybe consecutive days in one record) . Maybe that 2 questions about the timeoff.
Will it ever have any value to know if someone has vacation in the morning or went home sick in the afternoon? -> time or daypart indication
Can you ask your vacation for the future? Boss has to accept and when day passes you will see if the day was realy offtime or the demand was cancelled. -> keeping state (demanded, granted, cancelled). Will there be credit on the moment of demand?
It's getting fun :)
-> then you can define what has to be stored in database and how it's treated
Days off must be stored in detail (but maybe consecutive days in one record) . Maybe that 2 questions about the timeoff.
Will it ever have any value to know if someone has vacation in the morning or went home sick in the afternoon? -> time or daypart indication
Can you ask your vacation for the future? Boss has to accept and when day passes you will see if the day was realy offtime or the demand was cancelled. -> keeping state (demanded, granted, cancelled). Will there be credit on the moment of demand?
It's getting fun :)
ASKER
Great code jvejskrab, very useful. Thank you!
I see a functional problem. Is it allowed to take a vacation before your credit is created, if not everyone has to work on dec 31 isn't it? As nobody can have a full day vacation credit for jan 1th! So i think there must be some 'negative credit' be allowed.
So i think it's better to create a 'theoretical' maximum credit for the whole year (or other period like week or month). Beside this theoretical credit you constantly increase the deposited credit (and date until).
The withdrawals must be kept in detail, but detail can be 16 hours vacation from october 11 to oct 12.
Please think about your formula and the 365 days, people don't work 365 days (i hope) so how does your credit builds up for the non working days (weekend, vacation, sickness). Also keep in mind that it works for people who are hired or fired during the year.
So a daily maintenance and actions to corrcect the maximum theoretical credit if date of hire/fire changes and of course the earned credit. The 'credit left' or 'credit taken' must be maintained at the moment it is created or the credit changes. The mather of the negative credit can be hard coded, but better is to say for example you have default 8 hours negative credit but it's part of your definition so you can change it if someone needs 24 hours negative credit.