Need best approach for database architecture madness

If you're given a person, doesn't that already reduce the number of schedule rows to consider down by a factor of 50000? The number of office rows will also boil down to a couple of hundred if you only consider the given office. A proper index will find you those rows in no time.

Also, do people really schedule a full year of meetings in advance, or is it more likely you will only have a fully booked database for a month or two into the future? You can always move old data into an archive if you start having performance problems with your main database.

Also, with "up to" estimates it's easy to think too big. You should rather try to figure out how many people each office will have on average and how many meetings they will have a day on average. "Up to 10 meetings a day" might easily turn into "usually two a day". Depends on what kind of business we're talking about, of course.

And don't forget to subtract weekends. They make up 2/7 of the year.

Your application seems to need one crucial query. Find the intervals defined by

 (OfficeOpenIntervals INTERSECT PeopleAtOfficeIntervals) MINUS ScheduleIntervals

and search in these intervals, near or after a certain date.

Using appropriate indices and restricting the query (searching for one person only, for the next 60 days, etc) will probably be fine. Handling time intervals operations is tricky but you can test with various indices and ways to write the queries.

Another option (if you test and find no efficient way through indexing) is to have a separate AvailableSlots table which would be at first, when there is no scheduled appointments, populated with all available days a person is in the office (that would be the OfficeOpenIntervals INTERSECT PeopleAtOfficeIntervals). Then every time a appointment is added in Schedule, the corresponding row in this AvailableSlots table would be either deleted, updated or split in two rows that would store the remaining available intervals for the person who was scheduled for meeting.

So, the query to show first 5 dates available would only have to search in this table only.

This is not a normalized solution and the integrity has to maintained by stored procedures (for all operations like a schedule added, a person leaving an office, starting, etc). The initial population could also take time - and space - but you don't have to populate the table for a hundred years. It could be for just a few months and additional populations can be done later (once per month or per year or when needed).

Would this webinar assist you?

http://vimeo.com/26742356

-Mike