Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Scheduling Database - Design Help!

Re: Scheduling Database - Design Help!

From: James Conrad St.John Foreman <james.foreman_at_abcmail.co.uk>
Date: 19 Sep 2005 03:56:05 -0700
Message-ID: <1127127365.529405.301890@g43g2000cwa.googlegroups.com>


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 - 05:56:05 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US