DB design for employee working hours constraints I have a simple employee schedule database (I use PostgreSQL):

employees:
 - id
 - name
 - email

shifts:
- start
- stop
- employee_id

Now there is need to apply constraint for working hours. Employee can input when he/she is unavailable to work. It can be specific time period one time, or recurring once (er every 2,3...) days, weeks etc. The table looks like this:

constraints:
- start
- stop
- recurring (boolean)
- recurring_type (string)
- recurring_every_x (integer)
- employee_id

On my website i need to check quickly if employee is available for specific period. These are options I thought about:


Dynamic


When there is need to determine if employee can work within given time window we go through every instance of constraint connected with this employee, and calculate it. We have repeat this calculation every time.


Availabilities table


We create another table, which holds availabilities for each emplyee e.g. 1 year ahead, like this:

availabilities:
- start
- stop
- day
- employee_id

We fill data 1 year ahead with start=beginning of day, end=end of day, and when employee inputs new constraint we cut off time spans (removing some rows (days) from availabilities table or splitting them to two periods).

For example:

availability for 2015-05-05 is at beginning (day: 2015-05-05, start- 2015-05-05 00:00, end: 2015-05-05 23:59)

employee enters constraints "can't work 2015-05-05 10-15". We apply it:
(day: 2015-05-05, start- 2015-05-05 00:00, end: 2015-05-05 9:59)
(day: 2015-05-05, start- 2015-05-05 15:00, end: 2015-05-05 23:59)

This requires complex logic while new constraint is added. Also when removing constraint we have to remove all availabilities and recreate them, because constraints can overlap.

Which solution is best here, or maybe there is another way?
Use RFC 5545 RRules and ExRules to represent recurring events (days/times they can't work)

Use pg_rrule to turn recurring events into actual events. Use a materialized view.