Re: [GENERAL] scheduling table design

Hello Mr. Barnes,

I don't know of a nice solution to the problem of scheduling events that may
occur indeterminately far into the future. The way I have solved this
problem before is to have a table of available items. In this case the
available items would be something like:
1 9:00 Dr. Jones
2 9:30 Dr. Jones
3 10:00 Dr. Jones
.
.
.
17 9:00 Dr. Smith
18 9:30 Dr. Smith
19 10:00 Dr. Smith
etc.
This serves as the control table.
You will want to have an active/inactive flag on these items so that, for
example, when Dr. Smith leaves the practice, he will not be shown as
available, but the record of his past appointments will not be obliterated.

Your data table would then include a date and a reference to an available
item, plus whatever sort of incidental data is associated with the
appointment. You will probably not want to make the date/item combination
unique. This because, no matter what your client tells you, they will want
to overload appointments.

You can then search for open appointments by finding the available items
that are NOT in the data table for a given date.

One problem with this solution is that your client will have to settle on a
minimum granularity for appointment times. That is, does he have
appointments every half hour, or every fifteen minutes?

Hope this helps,
David Boerwinkle

-----Original Message-----
From: Barnes <aardvark(at)ibm(dot)net>
To: pgsql-general(at)postgreSQL(dot)org <pgsql-general(at)postgreSQL(dot)org>
Date: Wednesday, February 23, 2000 11:03 AM
Subject: [GENERAL] scheduling table design

>
>I'm trying to build what amounts to an appointment book for a doctor's
>office. The office has multiple doctors, and my application is going to
>handle patient appointment scheduling for all the doctors(among many other
>things). Anyway, I am looking for advice on database table design.
>
>I'll be tracking the date and time of the appointment, the doctor, reason
>for the appointment, insurance company authorization and the patient's
name.
>I'll also need to be able to view open appointment blocks, and I would like
>as much flexibility as possible in setting the times of appointments.
>
>If anyone has any suggestions or advice regarding table design, I would
>really appreciate it. I'm not sure at all where to start. With things
like
>open appointments, this seems much more complex than the typical name,
>address, phone number databases I've done in the past.
>
>Thank you for any help.
>
>David Barnes
>
>
>************
>