Time availability comparison, using Ruby on Rails

I want to have an availability table for multiple objects, and compare them. For example, a renter would search for a rental unit which is available from 1-3PM on Monday.

To do this, I thought I would divide the week into 30-minute time slots and give each time slot an ID. Then the date range would search for correspon开发者_StackOverflowding timeslot IDs.

However, then I'd need 5040 records to keep track of each timeslot, and I'm not too thrilled about maintaining this manually (and in Ruby I don't know how I would go about it). Is there a more elegant method, by using datetimes, or some Rails plugin that will do something similar to what I want but much more easily?

The critical requirement is that it must compare multiple, separated blocks of time.

EDIT: It would be a bonus if there could be a way to compare multiple blocks of time to see the best match. For instance, if I want to rent a video camera on Monday, Wednesday, and Friday, and one camera is available all three days but another is available only two of those days, I want to be able to compare and rank the cameras on the basis of best match.

You don't need to have such a complex model. All you need is a table that shows the blocks of time for which the rental units are not available, in other words, a table with booked times for each rental unit. For example:

create table BOOKING 
( rental_unit_id  int 
, start_datetime  date
, end_datetime    date
)

Now if you want to get a list of rental units that are available for the entirety of a given time block, say @Arrive to @Depart then all you have to do is run a query like this:

select R.rental_unit_id -- (and anything else you want)
from RENTAL_UNIT R
where not exists ( select B.rental_unit_id
                   from BOOKING B
                   where B.rental_unit_id = R.rental_unit_id
                     and end_datetime > @Arrive
                     and start_datetime < @Depart )

This query says get me a list of rental units where there is no booking which overlaps the search period of interest (from @Arrive to @Depart). Note that you can play with <= @Depart depending on whether you want to have an inclusive or exclusive endpoint for your booking periods.

EDIT: Handling multiple availability blocks

@OP has added a requirement for multiple availability blocks. If you are renting equipment over multiple days then @Arrive and @Depart just happen on different dates. If, as in @OP's example, the multiple days have gaps in the middle - presumably where the equipment is returned and can be rented to someone else - then you just have to add extra where not exists clauses - one for each independent block of desired availability. Just "and" them together and you will find the rental units that are available in all of the desired time blocks. The notion of better or worse matches doesn't really apply. A rental unit is either available or it isn't.

I would say your time slot should be the following model:

TimeSlot
   rental_id : int -> foreign key to your rental table (housing, whatever)
   start_time  : time
   end_time    : time
   day_of_week : int 1-7

Then searching from availability between two times would be: (let us call these AVAILABLE_SLOTS)

(
  (wanted_start_time >= TimeSlotTable.start_time && wanted_start_time <= TimeSlotTable.end_time)  OR
  (wanted_end_time >= TimeSlotTable.start_time && wanted_end_time <= TimeSlotTable.end_time) 
) 
AND Optionally:
( wanted_day_of_week  = TimeSlotTable.day_of_week)

where TimeSlotTable is the name of your table.

Then, if you really want get more granularity out of the query you could have an exception table. That is, a table where the rental owner indicates if they are not available on a particular day, which will require that the UI asks for a date in addition to time. (let us call this EXCEPTIONS)

ExceptionTimeSlot
   rental_id : int -> foreign key to your rental table (housing, whatever)
   date : date

and query:

( wanted_date  = ExceptionTimeSlotTable.date)

Finally, you want rental_ids that are in the AVAILABLE_TIMESLOTS but not in EXCEPTIONS.

The whole thing feels like an event calendar, so you might be able to pick up an even calendar and customize it for your purposes, and call events timeslots.