title: Database Scheme: Reoccurreing Weekly Tasks - Multiple Days of the Week
body: <p>So, I have found resources such as this <a href="https://stackoverflow.com/questions/14412527/design-option-for-recurring-tasks">Stack Overflow Question</a> and this <a href="https://wiki.mozilla.org/Calendar:Sql_Calendar_Schema" rel="nofollow noreferrer">wiki</a> that recommend database schemas Calendar Events or Reoccurring tasks. They explained how to create a schema that handles the following scenarios:</p>
  
  <ul>
  <li>Daily Tasks</li>
  <li>Weekly Tasks (i.e. every Tuesday)</li>
  <li>Monthly Tasks (i.e. Every 15th or every 2nd Monday)</li>
  </ul>
  
  <p>However, I would really like to to have my users be able to schedule tasks for every Monday, Wednesday, and Friday, or every Saturday and Sunday. (Similar to the way Google Calendar handles weekly repeating events where users can checkmark the different days).</p>
  
  <p>I would like to be able to do this, because I don't want users to have to create two separate tasks to accomplish this (because if they need to make a change, they have to remember to update not one but two tasks). </p>
  
  <p>Ideas:</p>
  
  <ul>
  <li>Just have multiple entries in my table, one for each day of the week the user wants the task to repeat, but this would make the application level a bit of a pain to manage</li>
  <li>Have a set number for each combination of days (Monday, Monday Tuesday, Monday Tuesday Wednesday), but thats a total of 127 combinations which would also be a pain to account for</li>
  </ul>
  
  <p>Anybody have any good ideas for implementing this design?</p>
  
  <p>My current schema:</p>
  
  <pre><code>ReoccurringRules
  -------
  RuleID PK int
  Frequency varchar(7)
  RepeatEvery int
  RepeatBy varchar(4)
  RepeatAt int
  DayOfTheWeek int
  </code></pre>
  
  <p><strong>Frequency</strong> Options:</p>
  
  <ul>
  <li>Daily</li>
  <li>Weekly</li>
  <li>Monthly</li>
  <li>Yearly</li>
  </ul>
  
  <p><strong>RepeatBy</strong> Options:
  (only used for monthly tasks)</p>
  
  <ul>
  <li>Day</li>
  <li>Date</li>
  </ul>
  
  <p><strong>DayOfTheWeek:</strong> 
  Only used for Monthly Tasks and if day is selected for RepeatBy (Sunday = 0, Saturday = 7)</p>
  
  <p><strong>RepeatAt</strong> meaning is different depending on repeat options</p>
  
  <ul>
  <li>Daily - No meaning</li>
  <li>Weekly - Day of the week it is repeated on</li>
  <li>Monthly:Day - Week number (i.e. 1st Monday, 2nd Monday)</li>
  <li>Monthly: Date - Day of the monthy it is repeated on (i.e. 13th, 21st)</li>
  <li>Yearly: No meaning</li>
  </ul>
  
  <p>RepeatAt weekly is where my dilemma is. Should I have multiple entries in this table with different RepeatAt values? Should I input a number 1-127 to specify which combination of days? Is there a better way to do this?</p>
  
  <p>Hope this question makes sense. If not, let me know and I'll try to clarify.</p>
  
  <p>Thanks in advance!</p>