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.

Time Off Schema

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 valid_year. 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:

  1. Vacation time that spans from December to January can't be counted in both years, only one.
  2. 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.
  3. 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?

  • I'd think you might want pto_requests and pto_granted as two separate tables, without a central pto_balances table tying them together. pto_requests would just hold the request for time off, pto_granted would hold the record of available time off granted to the user (in some cases, vacation time is added on a pay-period-by-pay-period basis). A view could consolidate the current available time. That said, you may need a "middle" table, to tie time used to the time granted (especially important when you have blocks of comp time that expire at different times). – RDFozz Dec 29 '17 at 16:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.