title: How should I (how would you) save schedules in database
body: <p>I have an application where users are able to set schedules for certain entity within a certain date range. Schedule editing will be similar to entering calendar appointments:</p>
  
  <ul>
  <li>when it starts and when it ends (hours)</li>
  <li>start date and set weekly recurrence days (ie. every Saturday and Sunday or every Moday only)</li>
  <li>end of recurrence date - sets date when recurrence should end</li>
  </ul>
  
  <h2>Example</h2>
  
  <p>I would like to set weekend schedule for January 2011 from 7am to 5pm.</p>
  
  <ol>
  <li>I set start date as 1.1.2011</li>
  <li>Set end date as 31.1.2011</li>
  <li>I set start at 7:00 and end at 17:00</li>
  <li>enable Monday on weekly recurrence mask</li>
  </ol>
  
  <p>Then I would also enter a new schedule that overlaps with existing one but just for the first half od January:</p>
  
  <ol>
  <li>Set start date 1.1.2011</li>
  <li>Set end date 14.1.2011</li>
  <li>Set time from 9:00 to 15:00</li>
  <li>Enable Monday.</li>
  </ol>
  
  <h2>Question</h2>
  
  <p>I could be storing schedules for each day in a table like:</p>
  
  <pre><code>create table EntitySchedule (
      EntityID int not null
          references Entity(EntityID),
      ForDay date not null,
      StartAt time not null,
      EndAt time not null
  )
  </code></pre>
  
  <p>But I will get lots of records in this table:</p>
  
  <blockquote>
    <p><strong># of records per schedule</strong> = (# of entity records) * (number of days in schedule range)</p>
  </blockquote>
  
  <p>Or I could store data similar to entering data:</p>
  
  <pre><code>create table EntitySchedule (
      EntityID int not null
          references Entity(EntityID),
      StartRange date not null,
      EndRange date not null,
      StartAt time not null,
      EndAt time not null,
      WeekdayMask tinyint not null -- bitmask of days (7 bits)
          default (0)
  )
  </code></pre>
  
  <p>This table would have a lot less records than the previous one.</p>
  
  <h2>Comparison</h2>
  
  <p>Each of them (per-day table and per-schedule) has its advantages and disadvantages:</p>
  
  <ol>
  <li>Per day table would be easy to get schedule for a particular day</li>
  <li>Per day schedule would have no possibility of editing recurrence data, you would always enter a new schedule overriding existing data</li>
  <li>After few years per-day table will have huge amount of records which would have to be mitigated</li>
  <li>In case of per-schedule table getting schedule on a particular day isn't possible without calculation</li>
  <li>Per-schedule table allows copying of schedule from one date range to another, which would be a very welcome feature that would make creating schedules simple by simply importing schedule data from a different date range and applying it to a new date range.</li>
  </ol>
  
  <h2>Usage scenario</h2>
  
  <p>Don't think of this as usual calendar appointments that we know from our personal calendars. Rather think of this as very flexible shop/store (entity) opening hours (schedule). So my database will have many many stores and they have very flexible opening hours that usually repeat on weekly basis.</p>
  
  <p>Editing schedules is usually an overwrite of existing data, so when opening hours within a defined date range already exist we wouldn't really update existing schedule definition but rather create a new one that would override the existing one. In case of having a per-day schedule this is very simple. I'd just overwrite those days that are applicable for the new schedule date range.</p>
  
  <p>But in the case of per-schedule table this becomes more complicated:</p>
  
  <ol>
  <li>I could just insert a new record in the table and then when reading schedule of a particular day newest record wins (kind of LIFO approach). This means that every schedule read (select) will include a bit more complex query where I would have to return all records related to a particular entity that define dates in correct range and then return the last one. This is fine for getting schedule of a particular day but makes my life hard when I'd want schedules for a date range...</li>
  <li>Defined schedule ranges don't overlap and when I would insert a new one It could as well mean that an existing schedule definition (or many of them) should be changed or even split into two.</li>
  </ol>
  
  <p>The first one seems a better approach. But query of getting schedules for a date range becomes rather complex and probably not very fast. Imagine of getting entity schedule for January 2011. <strong>Reading data should always yield results in the form of per-day table.</strong></p>
  
  <h2>Question</h2>
  
  <p>Is there any <em>standard</em> way of saving schedule data? How would you suggest I should be saving this data?</p>