title: What's the best way to store the days of the week an event takes place on in a relational database?
body: <p>We're writing a records management product for schools and one of the requirements is the ability to manage course schedules. I haven't looked at the code for how we deal with this (I'm on a different project at the moment), but nonetheless I started wondering how best to handle one particular part of this requirement, namely how to handle the fact that each course can be held one or more days of the week, and how best to store this information in the database. To provide some context, a bare-bones <code>Course</code> table might contain the following columns:</p>
  
  <pre><code>Course          Example Data
  ------          ------------
  
  DeptPrefix      ;MATH, ENG, CS, ...
  Number          ;101, 300, 450, ...
  Title           ;Algebra, Shakespeare, Advanced Data Structures, ...
  Description     ;...
  DaysOfWeek      ;Monday, Tuesday-Thursday, ...
  StartTime       
  EndTime           
  </code></pre>
  
  <p>What I'm wondering is, what is the best way to handle the <code>DaysOfWeek</code> column in this (contrived) example? The problem I'm having with it is that is a multi-valued field: that is, you can have a course on any day of the week, and the same course can take be held on more than one day. I know certain databases natively support multi-value columns, but is there a "best practice" to handle this assuming the database doesn't natively support it?</p>
  
  <p>I've come up with the following possible solutions so far, but I'm wondering if anyone has anything better:</p>
  
  <h3>Possible Solution #1: Treat DaysOfWeek as a bit field</h3>
  
  <p>This was the first thing that popped into my head (I'm not sure if that's a good thing or not...). In this solution, <code>DaysOfWeek</code> would be defined as a byte, and the first 7 bits would be used to represent the days of week (one bit for each day). A 1 bit would indicate that a class was held on corresponding day of the week.</p>
  
  <p><strong>Pros</strong>: Easy to implement (the application can deal with the bit manipulations), works with any database.</p>
  
  <p><strong>Cons</strong>: Harder to write queries that use the <code>DaysOfWeek</code> column (although you could deal with this at the application level, or create views and stored procedues in the database to simplify this), breaks relational database model.</p>
  
  <h3>Possible Solution #2: Store DaysOfWeek as a string of characters</h3>
  
  <p>This is essentially the same approach as using a bit field, but instead of dealing with raw bits, you assign a unique letter to each day of the week, and the <code>DaysOfWeek</code> column just stores a sequence of letters indicating what days a course is held on. For example, you might associate each weekday with a single-character code as follows:</p>
  
  <pre><code>Weekday      Letter
  -------      ------
  
  Sunday       S
  Monday       M
  Tuesday      T
  Wednesday    W
  Thursday     R
  Friday       F
  Saturday     U
  </code></pre>
  
  <p>In this case, a course held on Monday, Tuesday, and Friday would have have the value <code>'MTF'</code> for <code>DaysOfWeek</code>, while a class held only on Wednesdays would have a <code>DaysOfWeek</code> value of <code>'W'</code>.</p>
  
  <p><strong>Pros</strong>: Easier to deal with in queries (i.e. You could use <code>INSTR</code>, or its equivalent, to determine if a class is held on a given day). Works with any database that supports INSTR or an equivalent function (most, I would guess...). Also friendlier to look at, and easy to see at a glance what is happening in queries that use the <code>DaysOfWeek</code> column.</p>
  
  <p><strong>Cons</strong>: The only real "con" is that, like the bitfield approach, this breaks the relational model by storing a variable number of values in a single field.</p>
  
  <h3>Possible Solution #3: Use a Lookup Table (ugly)</h3>
  
  <p>Another possibility would be to create a new table that stores all the unique combinations of days of the week, and have the <code>Course.DaysOfWeek</code> column simply be a foreign key into this lookup table. However, this solution seems like the most inelegant one, and I only considered it because it seemed like the The Relational Way<sup>TM</sup> to do things.</p>
  
  <p><strong>Pros</strong>: It's the only solution that is "pure" from a relational database point of view.</p>
  
  <p><strong>Cons</strong>: It's inelegant and cumbersome. For example, how would you design the user interface for assigning corresponding weekdays to a given course around the lookup table? I doubt a user wants to deal with choices alongs the lines of "Sunday", "Sunday, Monday", "Sunday, Monday, Tuesday", "Sunday, Monday, Tuesday, Wednesday", and so on...</p>
  
  <h3>Other Ideas?</h3>
  
  <p>So, is there a more elegant way to handle multiple values in a single column? Or would one the proposed solutions suffice? For what it's worth, I think my second solution is probably the best of the three possible solutions that I outlined here, but I'd be curious to see if someone has a different opinion (or indeed a different approach altogether).</p>