Scheduling Database - Design Help!

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.

If you need a more specific example to understand I can provide that,
but I
wanted to keep this as short as possible.

If anyone has experience designing a scheduling DB then please post
your
expoeriences.

Thanks

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

CREATE TABLE available_times (appointment_date date, appointment_start
time, appointment_end time);

Put into this table one row per date per 5/10/15 minute slice.  (This
isn't going to be that big- at 5 minute slices you've got 105,120 rows
per year).

CREATE TABLE appointments (appointment date, appointment_start time,
appointment_end time, <lots of keys to other tables depending on
structure> );

Then every appointment goes into appointments.  To find free time, you
can do

CREATE VIEW free_times AS
SELECT appointment_date, appointment_start, appointment_end
FROM available_times
EXCEPT
SELECT a.appointment_date, a.appointment_start, a.appointment_end
FROM available_times a, appointments b
WHERE a.appointment_date = b.appointment_date
AND a.appointment_start BETWEEN b.appointment_start AND
b.appointment_end
AND a.appointment_end BETWEEN b.appointment_start AND
b.appointment_end;

and then add conditions to free_times to get first appointment in
September, etc.

HTH

Sorry, this was not tested. Only my idea.

CREATE TABLE Appointment
(id      SMALLINT    NOT NULL
,astart  TIMESTAMP   NOT NULL
,afinish TIMESTAMP   NOT NULL
,person  VARCHAR(20) NOT NULL
,telno   VARCHAR(12) NOT NULL
)


SELECT b.afinish AS available_start
, TIME('00:00:00') + DEC(n.astart-b.afinish, 6,0) AS
available_duration
FROM Appointment b
, TABLE
(SELECT MIN(n.astart)
FROM Appointment n
WHERE n.astart > b.afinish
) n(astart)
WHERE  TIME(:requested_dura
tion) - TIME('00:00:00') >= (n.astart -
b.afinish)
-- * Find the first available appointment in September
--   AND MONTH(b.afinish) = 9
--   AND MONTH(n.astart) = 9
--
-- * Find the first afternoon appointment
--   AND b.afinish >= TIMESTAMP(CURRENT DATE, '12:00:00')
--   AND n.astart  <= TIMESTAMP(CURRENT DATE, '23:59:00')

Create events for "open", "available for appointment",  "not available
for anyone", etc. so that you have something for every time slot in the
work day.  The events will be either scheduled or "schedule-able"; you
then have a routine carve out time slots within the "schedule-able".

1) Will be used more for storing information or for reporting on it?
2) Do appointments only start once every x minutes, or can they start
at any time?
3) Can more than one person schedule an appointment for the same time?

B.

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.

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