Re: Scheduling Database - Design Help!

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 Received on Mon Sep 19 2005 - 12:56:05 CEST