Re: Scheduling Database - Design Help!

From: Daniel Dittmar <daniel.dittmar_at_sap.corp>
Date: Mon, 19 Sep 2005 10:32:42 +0200
Message-ID: <dglt3a$ndh$> wrote:
> I need to develop a scheduling app and am having trouble with the
> database
> design. I can easily design a table hold appointments with start and
> finish
> times, but I always have an issue when it comes time to searching for
> free
> time.
> The search examples:
> Find the first available appointment in September
> Find the first afternoon appointment
> etc...
> Should appointments be linked similar to a linked list? Should I create
> a
> row for each 5 or 10 or 15 minute slice of the day for every day and
> then
> just search for null in a 'used' field? This could grow way to fast.

You could create a table TIMESLOTS holding all possible slots (start of a x minute slice). You could then join it with the APPOINTMENTS table.

SELECT slot from TIMESLOTS, APPOINTMENTS where not (TIMESLOTS.SLOT between APPOINTMENTS.STARTTIME and APPOINTMENTS.ENDTIME) You could add conditions on the time and on the people you want to schedule a meeting for.

I'm still stuck on how to
- search for a continuous hour of free time - search for something like "where 90% are free and what are the appointments for the other 10% so that I could ask them to reschedule"

Daniel Received on Mon Sep 19 2005 - 10:32:42 CEST

Original text of this message