Since I was working quickly, I used the simplest relational database design I could. The main database structure was five tables, Event, EventStaff, Location, Professor and Staff. The Event table held all of the information about a class: class or event name, professor, location, date, time, what equipment or assistance was needed and information about how and when the request was made. The setups were assigned in the EventStaff table, which linked the Event table to the Staff table. In general, two links were made between the tables for each event: one for the setup and one for the pickup.

Besides the primary keys for the two tables, the EventStaff table fields included the time of the setup or pickup, the staff member assigned and a field noting whether it was a setup or a pickup. Code in the event entry form added two records to this table when a user entered an event. The code would create the first entry, label it “Setup” and set the time at ten minutes before the user-entered event start time. Then it would create a second, labeled “Pickup” that would have a copy of the end time. Both would then appear on the entry form so that the user could assign staffing.

I used this structure because I was working quickly and it made sense to me at the time to create each class as a separate record in an events table. The nice thing about the database is that it was simple, unfortunately, it was not powerful enough. While the new system increased the reliability of our daily schedule and allowed for advanced bookings, entering classes was a chore and making changes was difficult. For every semester-long class that needed to be entered into the system, there were fourteen to forty-two individual classes to be entered. If a change needed to be made mid-semester, these classes would have to be found and changed individually. After a couple of semesters, it was clear that changes would need to be made.

In the summer of 2008, I began work on the second version of the scheduling database. What I wanted for the new version was for classes that belonged to a single course to be related to that course for ease of entering, searching and editing. The two big changes I wanted the user to see were a form for entering a full semester course and a search tool that could bring up all classes for that course. To do this, I had to add another table to my design. I also had to solve the problem of recurrence, one that better minds than mine have had difficulty doing.

The new Course table would hold the information common to every iteration of a specific class: the course name, the person responsible (Contact, a link to the renamed Professor table), whether the course was a recurring class or a one-off, semester, location, date and method of request, start and end times and the standing instructions for that course. After the Course table was designed, the only field left from the old Event table was the class date. However, sometimes locations, times or instructions can change for a specific instance of a class, so each class also held its own copy of the location, start and end times and instructions so that single classes could be modified without affecting the others. This was my solution for recurrence. It wastes some space copying data to two tables, especially for one-off events where the data is only needed once, but on the other hand it saves a bit over the original design for full-semester courses as the course name and instructor are only stored once. Structurally, the only difference between one-off events and semester courses were that one-off events had only one related record in the Class table and a recurring field value of false and recurring classes had multiple related records and a recurring field value of true. The true-false value would be used later when I built the search form.

I soon ran into another problem: complexity. Each class now had a three-level structure: course information in the Course table linked to single instances of courses in the Class table linked to the setup and pickup staff and times in the old EventStaff table. While this didn’t pose any problems when querying the database or when I created an entry form for one-off event entry, it did when I started building the full-semester course entry form.

The new one-off entry form was very similar to the original entry form, except that the data in the bulk of the form was based on a query pulling data from two forms rather than on a single table. The staff assignment data was in a subform based on the EventStaff table, as before. When I began work on the new semester course entry form, I hit a Microsoft Access limitation: I couldn’t have nested continuous subforms. So, staff assignments couldn’t be viewed on the same list as the classes for a particular course.

I decided that since almost every class had two, and only two related records in the EventStaff table, that I didn’t really need the effective many-to-many relationship between the Staff and Class tables that the EventStaff table provided. I moved all of the data into the Class table by way of four fields: setup time, pickup time, setup staff and pickup staff. Setups and pickups were now differentiated not by a field in the record but by the field they were in.

This simplified my form and made it fairly easy to design. The form was in two parts: the main form and subform. The main form held the fields from the Course table and the subform was a listing of all the fields from each related record in the Class table. The Course information was entered by the user in the form and then each class was entered by date in the subform. Code in the subform copied the relevant information into each record. As soon as the user created a new record in the class table by entering the class date in the subform, the code copied the instructions and start and end times into the new record and calculated the setup and pickup times from the start and end times. All that was left for the user was to assign staff. So, although each class date had to be entered by hand, it was still far faster and easier to enter a full course than the previous system.

But I wasn’t finished yet.

Part One to this series can be found here: Schedule Hacking Part One: Building an Audio-Visual Schedule