table design for tracking employee time off and accrual
Posted on 2011-10-13
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.