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.
ThanksOn 18 Sep 2005 22:20:03 -0700, 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.
>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
Hi Trevor,
Using pre-allocated slots would bloat your database (though Daniel's
idea would limit this somewhat), and at the same time, it would limit
the freedom of the user to make appointments the way he wants to, to the
granularity of your pre-allocated slots.
Here's an alternative:
CREATE TABLE Schedule
(PersonID int NOT NULL REFERENCES (Persons),
StartTime smalldatetime NOT NULL,
EndTime smalldatetime NOT NULL, -- but see below!
-- other columns,
PRIMARY KEY (PersonID, StartTime),
UNIQUE (PersonID, EndTime),
CHECK (EndTime > StartTime),
)
You'd also need to ensure that there are no overlapping intervals, but
that can't be done in a CHECK constraint - you'll ened a trigger to
verify that business rule.
With this design, you can go two ways:
a) Store only the appointments. If there are no intervals that start
before time Y and end after time X, then the time interval from X to Y
is available for appointments.
This approach makes the processes for adding, changing and removing
appointments easy, but makes searching for available time somewhat
harder, as you have to search for absence of rows.
b) Change the nullability of EndTime to allow NULLs. NULL will represent
"eternity". Define a code to represent available time. Give each person
one special starting row: StartTime is the earliest datetime your
application will allow; EndTime is NULL; row marked as "available time".
When making the first appointment, the end time in this first row is
changed to the start time of the appointment, a row is added for the
appointment and an extra row is inserted to makr the time from the end
of the appointment to eternity (NULL) as available.
This approach makes the processes for adding, changing and removing
appointments harder (think about the combinations: an appointment in the
middle of available time has to be treated differently from an
appointment that immediately follow the previous appointment, that's
followed by another appointment, or even both. When appointments get
removed, changed, or shortened, the time that is now available again has
to be collapsed with adjacent avaialble time. Etc etc), but makes
searching for available time easier, as each time will always be part of
exactly one time slot in the Schedule table.
Note: To be complete and fail-safe, you'll also have to implement checks
(in trigger code) to ensure that there are no overlaps and no gaps
between a person's rows in the schedule).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks so much to everyone for the input. I'll try out a couple of the
ideas and post back my results in case anyone wants to know how it
played out.
Thanks again,
Trevor
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.
> 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
ly transferences