CODEWITHSUNDEEP
sqldatabase-design
Scuba Steve
Scuba Steve

Reputation: 1648

How to store availability information in SQL, including recurring items

So I'm developing a database for an agency that manages many relief staff.

Relief workers set their availability for each day in one of three categories (day, evening, night).

We also need to be able to set some part-time relief workers as busy on weekly, biweekly, and in one instance, on a 9-week rotation. Since we're already developing recurring patterns of availability here, we might as well also give the relief workers the option of setting recurring availability days.

We also need to be able to query the database, and determine if an employee is available for a given day.

But here's the gotcha - we need to be able to use change data capture. So I'm not sure if calculating availability is the best option.

My SQL prototype table looks like this:

TABLE Availability Day
    employee_id_fk | workday (DATETIME) | day | eve | night (all booleans)| worksite_code_fk (can be null)

I'm really struggling how to wrap my head around recurring events. I could create say, a years worth, of availability days following a pattern in 'x' day cycle. But how far ahead of time do we store information? I can see running into problems when we reach the end of the data set.

I was thinking of storing say, 6 months of information, then adding a server side task that runs monthly to keep the tables updated with 6 months of data, but my intuition is telling me this is a bad fix.

Upvotes: 1

Views: 2517

Answers (1)

Matt
Matt

Reputation: 14381

For absolutely flexibility in the future and keeping data from bloating my first thought would be something like

  • Calendar Dimension Table - Make it for like 100 years or Whatever you Want make it include day of week information etc.
  • Time Dimension Table - Hour, Minutes, every 15 what ever but only for 24 hour period
  • Shifts Table - 1 record per shift e.g. Day, Evening, and Night
  • Specific Availability Table - Relationship to Calendar & Time with Start & Stops recommend 1 record per day so even if they choose a range of 7 days split that to 1 record perday and 1 record per shift.
  • Recurring Availability Table - for day of week (1-7),Month,WeekOfYear, whatever you can think of. But again I am thinking 1 record per value so if they are available Mondays and Tuesday's that would be 2 rows. and if multiple shifts then it would be multiple rows.

Now and here is the perhaps the weird part, I would put a Available Column on the Specific and Recurring Availability Tables, maybe make it a tiny int and store something like 0 not available, 1 available, 2 maybe available, 3 available with notice.

If you want to take into account Availability with Notice you could add columns for that too such as x # of days. If you want full flexibility maybe that becomes a related table too.

The queries would be complex but you could use a stored procedure or a table valued function to handle it fairly routinely.

Upvotes: 3

Related Questions