1

I am working on a school project which involves managing driving instructor calendars.

The application stores the time periods over which an instructor is available.

public partial class AvailabilityPeriod // kind of pseudo code
{
  Instructor instructor;
  DateTime start;
  DateTime end;
}

It also stores the individual appointments for this instructor, in a similar fashion.

When a customer says, for example, "I want a 2-hour lesson", I have to fetch availability periods and appointements of all instructors in order to compute their "actual availability" and then find someone having more than 2 hours free in his/her schedule.

Is there a better way ?

My question is almost an exact duplicate of Time Calendar Data Structure and I know it. But, well... We're in 2011 and I'm interested in Entity Framework-specific info, or at least something about doing this whith object relational mapping :-)

Community
  • 1
  • 1
user510101
  • 23
  • 3

1 Answers1

1

I wrote a similar scheduling application, and I ended up taking a little different approach (though your approach will certainly work).

In that system, the schedule was broken down into 15 minute increments, called blocks, and stored in a table. Everyone was always available unless they specifically requested the time off. These time off requests were stored in a separate table, again in 15 minute increments.

We had a ScheduleBlock table that basically stored all of the potential blocks which was populated based off of business rules, and a ScheduleException table that stored the blocks that users could not work.

With this table structure, it was easy to create a UI that showed all the ScheduleBlock entities (this is all of the potential available time, i.e. the business hours), and then additionally bind all of the ScheduleException entities (appointments, PTO, sick days, etc) to show the time users could not work (color coded per user). From there, the user of the system could select the blocks of time they wanted to schedule their particular event, similar to the way MS Outlook works.

When we did automatic scheduling, like in your case, we queried for users having X number of block available for a specified date range, sorted by the number of appointments that they had, and looped through them looking for a match. We sorted by the number of appointments so that the person with the most open schedule would be scheduled first.

Hope this helps!

BrandonZeider
  • 8,014
  • 2
  • 23
  • 20
  • If I understand correctly, there is only one ScheduleBlock in the whole database for a given 15 minute interval. Where are the actual appointments stored then ? – user510101 Apr 11 '11 at 15:28
  • ScheduleBlock stored the "business hours" (all time blocks in the system), ScheduleException stored the time users could not "work" (foreign key pointing to ScheduleBlock), and we had another table that stored our actual appointments (ScheduleAppointment, again a foreign key to the ScheduleBlock). You could combine the ScheduleException and ScheduleAppointment tables and just use an "appointment type" or something if you wanted. You could also not store the ScheduleBlock entries if your business rules do not change. It just makes it easier. – BrandonZeider Apr 11 '11 at 15:32
  • My business rules do no change :) I undersand you're storing unavailability periods rather than availability. Unfortunately I must store their availability since it can greatly vary, eg. Alice works Monday and Tuesday, Bob works Tuesday and Wednesday... – user510101 Apr 11 '11 at 20:28
  • That's cool. Then in your situation if you store all availability in start date/end date records, and you were looking for anyone with an open 2 hour window, you could specify "where DateDiff(hh, StartDate, EndDate) >= 2" – BrandonZeider Apr 11 '11 at 20:40