|
Posted by Daniel Dittmar on 09/19/05 11:32
Trevor.D.Matthews@gmail.com 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
Navigation:
[Reply to this message]
|