title: How to store availability information in SQL, including recurring items
body: <p>So I'm developing a database for an agency that manages many relief staff. </p>
  
  <p>Relief workers set their availability for each day in one of three categories (day, evening, night). </p>
  
  <p>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.</p>
  
  <p>We also need to be able to query the database, and determine if an employee is available for a given day. </p>
  
  <p>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.</p>
  
  <p>My SQL prototype table looks like this:</p>
  
  <pre><code>TABLE Availability Day
      employee_id_fk | workday (DATETIME) | day | eve | night (all booleans)| worksite_code_fk (can be null)
  </code></pre>
  
  <p>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.</p>
  
  <p>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. </p>