Re: Scheduling Database - Design Help!
Date: 19 Sep 2005 03:56:05 -0700
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
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