Re: Scheduling Database - Design Help!

pb648174 wrote:
> I think you should stick with tracking the start and end dates only but
> you will also need to track the available hours for appointments (M-F
> 9-5) so that you can do the subtraction and come up with the free time.

I think I would use a start time and duration instead of start time and end time. Then something like

table schedule
starttime timestamp
duration number (units of minutes?)
usedflag a code type (int or char)
location
description
...

free time are those rows with usedflag code indicating it's free. Finding the first available free slot is something like

select min(starttime)
 from schedule
where usedflag='free'
and duration >= :desired_duration

or all slots on a given day

select starttime
 from schedule
where usedflag='free'
 and starttime between :given_day and :given_day+1day

I'm really surprised this wasn't suggested earlier.   Ed Received on Wed Sep 21 2005 - 22:47:46 CEST