Programmatically managing a 'balance' of time (sick/vacation)

Question

I'm using Python/Django, but this is more about the "data model" and how I interact with the information - I really just want to know if I'm crazy here.

I'm working on a small app at my company (~55 employees) that will keep track of available Vacation/Sick time. Part of the purpose is to integrate "self-service" into our intranet, so that employees can submit "Time Off Requests" electronically, instead of filling out and handing in paper to HR.

Obviously, this app needs to keep a running balance per employee, and will be validating that the employee has enough Vacation remaining for whatever they're requesting.

Like with financial/accounting software, I know that I shouldn't necessarily be storing float values, or just keeping a single running balance.

My idea is to use a database table structure like the following to store time "credits" and "debits":

Employee | Year | Credit/Debit | Amount | Timestamp

'Year' would be the year to which the credit/debit belong, because Vacation and Sick time are handled on a yearly basis, not on a running balance per employee.

To determine the employees available Vacation/Sick time, I would get the 'transactions' for the employee for the given year, and find the balance.

I know I'm leaving out lots of information, but I was wondering: Does this seem like a reasonable way to go about this, being as that it needs to be very accurate, or am I completely over-complicating this?

Answer 1

This looks like a good start. A couple of points:

• The credits would be autogenerated by the system at the begining of the year, and debits would be created by employees. Should there be a field to indicate who/what created the transaction?

• Do you have a mechanism for indicating what kind of time off is requested? I don't know what your company is like but some companies treat vacation and sick time differently. Then there's also caregiver time, time off for compassion grounds (such as a relative dies), time off for civic and statutory holidays, time off for religions holidays, time off for.... well you get the idea. Maybe you want different types of time off to be worth different amounts of credit. Some organizations do that. Do you plan to track these different time-off codes as well? Is it something that you should plan for if you think it will be an issue in the future?

Answer 2

I also agree it's a good start.

I don't see any fields for approval / disapproval. If this app is meant to be used also by HR, then their decisions need to be expressed in your model as well.

If HR is being taken out of the picture (which I doubt, but maybe) then there's no need to do this; the app can keep track of requests and the leave balance and say immediately whether the request is valid or not. But I suspect it can't be this simple. :)

Answer 3

If you think your solution is complicated, it's not. Modeling sick/vacation days as accounts that are linked to employees is a very good idea and can be dead easy.

In the simplest case, you can have a "transactions" table, and a "account" table, such that re-running all the transactions from the beginning of the year (for each account) will yield a sum that exactly matches the balance.

Transactions

ID | Account | Delta | Timestamp

Account

ID | Name | Employee | Year | Balance

The transactions provide an audit trail, and the balance provides a point of reference for your next transaction. By ensuring the two match, you've ensured consistency (though, not necessarily correctness - that's got to be checked with unit tests on each type of transaction, i.e. deposit, withdrawal)

I'd recommend a "Transaction Detail" table that refers to the Transactions.ID, and includes all the nice stuff you want like who initiated it, notes, etc.