Database design for employee availability

I am trying to design a system, where employees would declare their availability and I would then be able to search through this data and find if there’s any conflict.

The employees can set their general availability like this: I am available from May 1st to July 22th from 10:00 to 16:00.

Then they can set exceptions such as: I am unavailable from May 5th to May 7th from 14:00 to 16:00.

I was thinking about converting these declarations into specific 10 minute time slots creating record for every day and every time and then deleting slots declared as unavailable. But I’m not sure how to keep the data consistent.

What is an efficient way to store and query this data?

Solution

Assuming you have an existing Employee table with an EmployeeID defined, you would create two tables, GeneralAvailability and Exemption.

GeneralAvailability
-------------------
GeneralAvailabilityID
EmployeeID
StartDate
EndDate
StartTime
EndTime

Where GeneralAvailabilityID is an incrementing integer default primary key. You would also create an index on (EmployeID, StartDate desc).

You would query this table with a SELECT statement.

SELECT *
    FROM GeneralAvailability
    WHERE EmployeeID = 'some id'
    AND StartDate <= current date
    ORDER BY EmployeeID, StartDate desc

Now, you'll probably retrieve multiple rows. The first row is the most recent general availability row with the dates and times.

I used a SELECT * for typing convenience. In your code, you would specify all of the columns you want to retrieve by name.

The Exemption table would have an identical structure and a slightly different query. Your application would process the multiple exemptions for an employee.

SELECT *
    FROM Exemption
    WHERE EmployeeID = 'some id'
    AND StartDate >= current date
    ORDER BY EmployeeID, StartDate