Unique hash/index for time interval

Question

I am working on a simple resource booking app. The use of the resource is exclusive so it can't be booked more than once at the same time. I am wondering if this constraint can be enforced by a unique index instead of having to build validation in code.

The resource can only be booked by block of 30 minutes and the start and end time must be on the hour OR the half hour. So the booking can modeled as an array of blocks that are unique (dividing the timestamp in chunks of 30 minutes).

Can anyone think of a way to hash that so any booking with one or more 30-min. block in common would violate the unique index condition?

NB: I am using MongoDB (I don't think it really matters)

Answer 1

I am wondering if this constraint can be enforced by a unique index instead of having to build validation in code.

Use an unique compound index on the resource id, day and chunk of 30 minutes. Then insert one document for each 30 minutes of period of reservation.

For example, to reserve the resource id 123 on 9 of June 2015 from 8:00 to 9:30 (16^th, 17^th and 18^th 30 minutes period of the day), you insert 3 documents:

> db.booking.createIndex({resource: 1,
                          day: 1, period:1},{unique:true})

{
  resource: 123,
  day: ISODate("2015-09-06"),
  period: 16
},
{
  resource: 123,
  day: ISODate("2015-09-06"),
  period: 17
},
{
  resource: 123,
  day: ISODate("2015-09-06"),
  period: 18
},

---

Depending the number en entries, you might consider using embedded documents instead:

> db.resource.createIndex({_id: 1,
                           "booking.day": 1,
                           "booking:period":1},{unique:true})

And describe your resources like this:

{
  _id: 123,
  someOtherResourceAttributes: "...",
  booking: [
    {
      day: ISODate("2015-09-06"),
      period: 16
    },
    {
      day: ISODate("2015-09-06"),
      period: 17
    },
    {
      day: ISODate("2015-09-06"),
      period: 18
    },
  ]
},

This has the great advantage that insert/update would be atomic for the whole reservation. But beware that document size is limited to 16M.