MongoDb unique constraints on a Date range

Question

Im using MongoDb with Mongoskin. In a collection I'm saving events. Among other fields, these events have a start and an end, saved as Dates in Mongodb.

events { 
  start: "Date1",
  end: "Date2",
  ...
}

When inserting new documents in this collection I need a constrain that forbids insertion of document which start-end dates overlapping an event alreay created. In short, I dont want any events share the same time span.

Question: Is there a way to handle this constraint trough MongoDb with some kind of unique index? I think not, but please correct me if I'm wrong!

If not:

Question Do I have to check possible overlaps trough code before inserting new events? Do I need to set up some kind of write lock, so that another user can't squeeze in an event between the time I check for overlaps and inserting my own event? How is this done in MongoDb?

EDIT

This is the best way i have come up with so far, it actually seems to work pretty good.

var input = getPostInput();

var query = {$and: [
  {start: {$lte: input.end}},
  {end: {$gte: input.start}}
]};
db.events.findAndModify(query, {}, {$setOnInsert: input}, {new: true, upsert: true}, callback)

It uses the findAndModify as a type of "findOrCreate" operator. $setOnInsert add the POST input properties only if the findAndModify don't find a document, and upsert: true says it should create an document if none is found. These two options in combination seems to create a findOrCreate operator.

EDIT

Problems arise when updating (PUT) an event. I can't reuse the code above because it's relies on upsert and $setOnInsert.

EDIT

@wdberkeley:

I'm still struggling with this main problem: ensure uniqueness on a range. The more I think about it, it seems that "the array of time slices" might be the most non problematic solution. For example, lets say that 5 minutes is chosen as the smallest time period, and the average booking is 45 minutes. This would require me to save 9 numbers (probably dates): timespan = [0,5,10,15,20,25,30,35,40], instead of two: start=0, end=45. This is more than four times more saved data for the average booking. I dont mean to be harsh, but don't you see this as a problem? Or does it become a problem first when the saved data is 10 times larger or 100 times larger? I do realise that this is also relative to the totalt amount of bookings actually made...

Answer 1

There isn't a dead simple way to do this in MongoDB. I cooked up one alternative option that could work for you. If your dates come in discrete steps, like if this is for a booking application where users reserve objects by the day or the hour, then you can use a combination of unique indexes and multikey indexes. For example, suppose reservations are by day. John Q reserves October 11 to October 14, inclusive. That is something like the 281st to 284th days of the year - let's assume that's exactly which days it is. Save the reservation field as an array of the days that are reserved

> db.reservations.insert({ "span" : [ 281, 282, 283, 284 ] })

Put a unique index on the span field.

> db.reservations.ensureIndex({ "span" : 1}, { "unique" : 1 })

Now you can't insert a document that has any of those days in its span:

> db.reservations.insert({ "span" : [ 279, 280, 281, 282 ] })
// unique key error

This might work for you with some additional tweaking to take into account the year, or it might be part of a compound unique index to make the timespans unique by e.g. room_id for hotel booking.

Another way is just to coordinate the checks on the client side. If you have multiple clients that don't talk to each other at all I guess the best way to do this would be to share a "lock" in the database: findAndModify a document in a lock collection to check and acquire a lock. Once a client has the lock by changing a field on that document, it can do the check for overlaps with a query and then the insert if all is well, then release the lock by changing the flag on the lock document again.