title: Storing timetable availability in SQL table
body: <p>I'm playing around with a database idea at the moment. It's likely not going to be deployed in any sort of fashion and is more of a learning experience. </p>
  
  <p>It's meant to simplify the collection and handling of tutor information for a bunch of classes at the university I went to. I worked part time in an office that organised tutors for a handful of classes each semester. </p>
  
  <p>I've got a number of questions, but the one that's causing me a problem at the moment is how I can store the availability of each tutor. I'm considering 3 options at the moment, and I'm looking for feedback on the pros and cons of each from a technical perspective.</p>
  
  <p><strong>Background:</strong> 
  Tutor information is stored in a "tutor" table (tutorID references this) and the previous availability must be able to be recalled. Tutor availability is discrete (hourly), and constant throughout a semester.</p>
  
  <p><strong>Option 1:</strong></p>
  
  <pre><code>Table: Availability
  +-----------+---------+-------+-------+---+---+---+----+---+
  | avID (PK) | tutorID | year  |  sem  | M | T | W | Th | F |
  |           |         | (int) | (int) |    (all strings)   |
  +-----------+---------+-------+-------+---+---+---+----+---+
  </code></pre>
  
  <p>In this table, availaiblity is stored in a string (08,09,10,13,14 represents 8am, 9am, 10am, 1pm and 2pm). </p>
  
  <p>Data could be reclaimed with</p>
  
  <pre><code>SELECT * FROM Availability WHERE tutorID=0001 AND year=2013 AND sem=1
  </code></pre>
  
  <p>And to see who's available</p>
  
  <pre><code>SELECT * FROM Availability WHERE AND year=2013 AND sem=1 AND M LIKE '%08%'
  </code></pre>
  
  <p><strong>Option 2:</strong></p>
  
  <pre><code>Table: Availability
  +-----------+---------+-------+-------+--------------+
  | avID (PK) | tutorID | year  |  sem  | availability |
  |           |         | (int) | (int) |     (set)    |
  +-----------+---------+-------+-------+--------------+
  </code></pre>
  
  <p>In this layout, the availability column is stored as the SET datatype in mysql, with the options being every combination of Mon through Friday and every time from 8 till 4 (M08, M09... Th14, F16 etc etc). This works out to 45 acceptable values. This is the one that I'm currently leaning towards, but I don't know much about the SET datatype.</p>
  
  <p>Data could be reclaimed with</p>
  
  <pre><code>SELECT * FROM Availability WHERE tutorID=0001 AND year=2013 AND sem=1
  </code></pre>
  
  <p>And to see who's available</p>
  
  <pre><code>SELECT * FROM Availability WHERE AND year=2013 AND sem=1 
                  AND FIND_IN_SET('M09',availability) &gt; 0
  </code></pre>
  
  <p><strong>Option 3:</strong></p>
  
  <pre><code>Table: Availability
  +-----------+---------+-------+-------+-------+-------+
  | avID (PK) | tutorID | year  |  sem  |  day  | time  |
  |           |         | (int) | (int) | (int) | (int) |
  +-----------+---------+-------+-------+-------+-------+
  </code></pre>
  
  <p>In this option, there is a single row for each tutor each year and each timeslot. </p>
  
  <p>Data could be reclaimed with</p>
  
  <pre><code>SELECT * FROM Availability WHERE year=2013 AND sem=2 AND tutorID=0001
  </code></pre>
  
  <p>Availability with</p>
  
  <pre><code>SELECT * FROM Availability WHERE year=2013 AND sem=2 AND day=3 AND time=14
  </code></pre>
  
  <p>Anyway... Thanks for reading through all of that. Hopefully someone will be able to shed some light on this. I think that it basically will boil down to a best-practice type of question. Unless there's something that I've missed entirely!!</p>