1

Let's say I have the following appointments:

Appointment 1 = 1:00
Appointment 2 = 2:00
Appointment 3 = 3:00
Appointment 4 = 4:00

I need to reschedule all of these one hour later:

Appointment 1 = 2:00
Appointment 2 = 3:00
Appointment 3 = 4:00
Appointment 4 = 5:00

The problem is that I want to do this in one atomic operation, but appointment times must be unique. If I handle these in order, then I will get an error. I can't schedule Appointment 1 at 2:00, because 2:00 is not an open time. It's occupied by Appointment 2.

Times are stored in a table very simply: 

AppointmentId: number,
AppointmentTime: datetime

There is a unique constraint on AppointmentTime.

I would rather not have to unschedule all the appointments first. That's probably what I'll end up doing, but I'm trying to avoid that.

The simple solution is to do these in reverse order. But this is a simple case that is neatly ordered. Chances are, the data would not be sequential. Is there a pattern or algorithm that deals with this kind of thing? Finding the optimal sequence for performing an operation on a list of objects?

There is also the case where I want to schedule my appointments to existing times:

Appointment 1 = 4:00
Appointment 2 = 2:00
Appointment 3 = 3:00
Appointment 4 = 1:00

There is no way to calculate an order that will work here. It's circular. That's why I'll probably end up unscheduling everything. But I'm still interested in trying to determine the optimal path.

I'm doing this on Node using MongoDB.

Anyone have any suggestions?

Mohair
  • 383
  • Will the rescheduled times always be time+n (where "n" could be negative) for all appointments to be rescheduled? What's the data-processing context? – outis Apr 27 '16 at 19:46
  • 3
    Does there even need to be an optimal path? If this information is stored in a typical relational database, you can do all the updates as a single atomic transaction without having to worry about constraints getting evaluated on "in-between states". – Ixrec Apr 27 '16 at 19:50
  • @lxrec I'm using Node and Mongo, so I don't have that luxury. – Mohair Apr 27 '16 at 19:52
  • @outis Yes. I will remove anything that is not a change. – Mohair Apr 27 '16 at 19:53
  • We need more info. Is each appointment a different record in Mongo or is the storage based on a day/week/? Where is the validation of no overlaps occurring? In the DB or as validation in some app? If it is in some app layer, you could make the changes directly to the db and then re-validate when all the moves are done. – cdkMoose Apr 27 '16 at 19:59
  • 1
    Couldn't you just go through the list recursively until it is empty or all the remaining reschedule attempts cause a conflict? – JeffO Apr 27 '16 at 20:19

1 Answers1

1

No, there is no algorithm that won't fail miserably in some case, because some legal schedule changes cannot be sequentialized without first removing an entry. Here is an example:

A: 13:00, one hour
B: 14:00, one hour

reschedule to

B: 12:30, one hour
A: 13:30, one hour

As you see, you can't reschedule A first, because that would conflict with the end of B, and you can't reschedule B first, because that would conflict with the start of A.

As such, there are only two options left to you:

  1. unschedule first, then reschedule, and

  2. implement a general rescheduling of multiple appointments as one big atomic action, computing the resulting schedule first, checking if it's ok, and then committing it.

cmaster - reinstate monica
  • 8,001