Design pointers for "smart" appointment scheduler program

I'm interested in suggestions or pointers to design a "smart scheduling attendant" for appointments. The appointments can be self-scheduled by clients so the "smart scheduler" replaces some of the scheduling task done by front-desk staff. Here are some of the things that it needs to do automatically.

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:

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.

I'm looking for a fairly general solution to this type of problem. In fact, if there is a technical name for this I can research then I would very much appreciate it.

Any help in database and algorithm design would be very welcome. Thank you.

Wednesday, September 24, 2008

Deleting …Approving …

Go to "Database answers dot org" and look up "reservation systems". There should be something there that is close enough to what you are doing to give you some ideas.

Walter Mitty
Wednesday, September 24, 2008

Deleting …Approving …

Hi Walter and thanks for the tip. I'm a newbie to all this but do appreciate your time in helping out.

The reference you cited looks good and I will look at it more. However, I'm not seeing the issue that's troubling me.

The more specific issue is how the software and data should be set up to update available times for all services provided by all service providers that share resources in the facility.

Initially, on opening a facility there are no appointments booked so all services show all possible time slots as being free but booking the first service may require some additional updating work behind the scenes. If the booked service uses shared resources any other services that could use those resources in the relevant time period need to have their available times update to account for this booking.

It's all this updating work going on "behind the scenes" that I'm most concerned with. Correct me if I'm wrong but in my naive understanding, it seems to me that this resembles resource management software (but not quiet) more than a simple appointment scheduler and it wasn't obvious to me that the reference you cited had something corresponding to this situation.

BTW, so you know were I'm coming from.

I want to quickly test an customer "input device" for this kind of system with mostly "off the shelf" parts and as little programming on my part as possible. I'm not a professional software developer, more a cobbler with some programming skills.

So I'll need to make an inexpensive test set up using maybe MS Access or the Open Office version of Access for Linux since the open source software for the "input device" is only available for Linux. If things work out I'll get a professional to redo the system.

Thanks again

Mark Szlazak
Wednesday, September 24, 2008

Deleting …Approving …

>I'm a newbie to all this

Then may %Deity% help the doctor you're building this for . . .

I'm Feeling Cranky Today
Sunday, September 28, 2008

Deleting …Approving …

No deity is required since there is no doctor. That was just an example. However, you are suggesting that this problem is to difficult for a newbie to tackle and that was helpful in itself. More specific reasons why this is the case would be even more helpful. I do have enough time to follow-up on good suggestions and I later would seek those better suited to the task. But first I would like to get a good "feel" for the issues involved so I can communicate what I need in a more exact way.

Mark Szlazak
Monday, September 29, 2008

Deleting …Approving …

Your right that the problem your after is walking into the solution space of resource planning software which in some ways are schedulers with lots of addons. In regards to solving the problem I would suggest keeping the following in mind:

i) UsabilityScheduling can get really complicated really quickly and needs to be understood easily by those who use the software. If you have some automation going on in the background the user needs to be able to understand what happened and not left high and dry guessing

ii) Semi-automationIn our experience its important to have scheduling automation with manual override because there are always outlier conditions that your software just won't be able to handle. The automation should do the 'heavy lefting' in regards to scheduling.

Scheduling software programmer
Monday, September 29, 2008

Deleting …Approving …

Yes and thank you "scheduling software programmer"!

I'm aware of those issues and have been thinking more about what I need done with regards to matching the input device with the task at hand. I've come down to three possibilities on the issue of openings for services for each provider. The first requires the most memory because it stores openings of all services in separate tables for each provider over a range of dates. The number of services can be quite large for even a small businesses like a beauty salon & day spa.

This has the advantage of speed when interacting with my "input device" which is a speech recognition engine.

The second approach uses less storage by dynamically calculating service openings based on _some_ openings data, business rules and shared resources information.

A third approach assumes that there aren't that many services with shared resources so services with no shared resources are taken account in the services providers openings table. However, each service with shares resources (for each provider) gets an additional openings table.

I'm still deciding which approach will work best.

Lastly, I'm not quite clear as to why you mention that the receptionist/front desk needs to understand what is going when services get booked. That should be pretty clear. I plan to have the needed views of availabilities displayed in different ways similar to what desktop appointment calendar displays things (e.g. www.scheduleview.com). Could you please elaborate more on this?

Thanks.

Mark Szlazak
Tuesday, September 30, 2008

Deleting …Approving …

Scheduling programmer, I forgot about your "outlier" remark. Could you give an example?

Mark Szlazak
Tuesday, September 30, 2008

Deleting …Approving …

Sorry this is quite the delayed response.

You seem to have alot of concerns in regards to data volumne and how data will be stored. I think your getting way to ahead of yourself in regards of the software design. We write scheduling software that is used in industrial scenarios where 50,000 items are being 'planned'. We do all of this will flat files. The main reason is that would need data flexibility and don't want the overhead code of database integration.

In regards to the secretary comment, scheduling software needs to be easily understood. Someone like a secretary needs to be able to look at the software and comprehend what the software schedules says and what is physically going on. If the scheduling software looks like a mystery that it will become history because of lack of use like many scheduling applications.

On a related note, this connects with my outlier remark. There are always exceptions outside of a planned schedule, especially if there is a level of automated planning. The software user needs to be able to understand what the program is doing and how to incorporate the random stuff that happens day to day. For example taking resources offline because they are broken.