I am building a pto (paid time off) module for my company, and I'm not sure that my database schema is a sound one. I'm using MySQL.
users are employees,
pto_requests are the requests they make for time off, and
pto_balances is their time off balances in the form of a balance sheet with negative values for hours used and positive values for hours added. The user's pto balance is figured by summing their pto_balance records for the current
pto_type is basically Vacation, Sick, Compensation, etc. Balance line items could be spawned by a request the user made, a manual adjustment by an admin, or the system giving them the year's vacation hours.
Compensation time needs to be used within 30 days, so I added an expires_on date.
I've noticed a couple of problems so far:
- Vacation time that spans from December to January can't be counted in both years, only one.
- There's no way to know if they "used" time that was going to expire. So excluding it from my calculation will just leave them with a negative balance if they did.
- Getting the "base hours" an employee gets will require selecting a specific balance sheet item that they system added at the beginning of the year. The system automatically inserts 96 hours for each employee at the start of the year.
Is this a sound data model?