Database Answers Components must work together

Home Ask a Question Best Practice Careers Contact Us Data Models Search Site Map
Multi-Resource Scheduling 

Back to the Data Model.
The Area being Modelled is :-
A Database to support the Tracking of Printer Cartridges.

Specifications have been provided by JVM in the following terms :-

I'm looking for a database design for booking appointments that require multiple resources (people, rooms, machines). 
I've found the below description elsewhere that defines the problem, but I've been unable to find a nice solution online.

The closest I could find would be the doctor's practice diagram. Appointments would require n resources.



Problem description:

A service provider has different services and a service may need to have many resources available for a 
certain time range for it to be booked with a client. 

Some of the services can share resources and are thus not independent of other service providers that also
occupy the facility. Here is an example. 

Say you have two doctors A & B in one office and each has their own exam & treatment room. 
Also, they both share a doctors assistant M and some of the treatment equipment X. 

To keep it simple, both doctors have three services that they offer:

s1: Exam (1 hour)
s2: Treatment visit (15 minutes)
s3: Extended treatment visit (30 minutes)

The extended treatment is made up of two intervals. 

The first half (15 min) is done by the doctors assistant M with equipment X and in the second
half (another 15 min) the assistant and equipment aren't needed and become available. 

The doctor comes in to finish treating the patient.

So the doctor was available while the assistant was seeing the patient.

So when an extended treatment is scheduled the "smart scheduler" should update time availabilities for the 
various services offered by the two doctors. 

Maybe there are other ways of doing this part??

Obviously, this a simple scenario and it becomes more complex if some service types require more shared resources. 

Also, some of the shared resources can be set with upper limits as to how many times the can be multiple booked for 
the same time period. 

Say shared equipment Y can be double booked but not triple booked in a three doctor office.

Anyway, here are other issues that can be ignored for now if you prefer. 
There maybe other rules put on services like certain services (exams) being done only at certain times 
(in the mornings of certain days). 

Another maybe that available time slots are not allowed for booking any service until some percentage of other 
time slots are filled. For instance, don't allowing bookings in the afternoon for any service until 50% of
that mornings time slots are filled. 

This is one way to avoid having a patient self-schedule anywhere on an open day thus forcing in-office
scheduling to occur around that patients appointment.

Very similar scenarios happen in non-health care fields like beauty salons, day spas, scuba diving lesson 
(almost 10 shared and non-shared resources), etc.

© DataBase Answers Ltd. 2010
About Us Contact Us