Checking for overlapping car reservations

Question

I'm writing a simple booking program for a car rental (a school assignment). Me and my buddy are trying to make the system a little more advanced than the assignment dictates, but we're having some problems we hoped you could help us with.

The idea is that you can reserve a certain car type, and when you get the car it will be one of that type (you don't reserve a specific car, as our assignment dictates, but only a type). Only one customer can have the car on a specific date. As the reservations tick in we have to make sure, that we don't hire out more cars of each type than we've got. The reservations are basically stored with a start date, an end date, and a car type.

If we ignore the car type for now (lets say we only have one type) then the reservations could graphically look something like this:

1/12  2/12  3/12  4/12  5/12  6/12  7/12
|-------------------|
                    |-----------------|
                                |-----|
|-------|
                          |-----------|
|-------------|

If the rental only has three cars it would be possible to rent a car from 3/12 to 5/12 since all days only have 2 car reservations. But how do we know this? Do we have to check each date and count() the number of reservations that spans over that date?

And what if somebody had reserved a car on 4/12, then 3/12 and 5/12 would still only have 2 reservations, but 4/12 would have 3.

Would it be possible to do with a query some how, or do we have to step through each date in the program to check the number of reservations didn't exceed the number of cars? (This is easy enough with only full dates, but consider the scenario where you could rent the cars on an hourly basis (not only on a daily as here). Then it could be a though one to step through each our if we have a lot of reservations and cars and the timespan is long...)

Hope you have some nice ideas that will help us along. Thanks for taking the time to read the question :)

• Mikkel, Denmark

Answer 1

Assume, You have such reservation situation in real life:

       1/12  2/12  3/12  4/12  5/12  6/12  7/12
Car1:  |-------------------|
Car2:                      |-----------------|
Car3:  |-------|     |-----------|     |-----|                              
Car4:  |-------------|

Table car

| id | type | registration |
| 1  | 1    | HH1111       |
| 2  | 1    | HH3333       |
| 3  | 2    | HH77         |
| 4  | 3    | DD999        |

Table reservation

| car_id | date_from  | date_to    |
| 1      | 2013-12-01 | 2013-12-04 |
| 2      | 2013-12-04 | 2013-12-07 |
| 3      | 2013-12-01 | 2013-12-02 |
| 3      | 2013-12-03 | 2013-12-05 |
| 3      | 2013-12-06 | 2013-12-07 |
| 4      | 2013-12-01 | 2013-12-03 |

Now, You must by really simple logic, select all available cars for period

from 2013-12-05 to 2013-12-06

"Select ALL cars, which does not have any reservation with dates, which blocks it for usage" with brillian mysql select:

select * from car  where not exists ( select * from reservation 
where car.id = reservation.car_id AND
date_from < '2013-12-06' AND
date_to > '2013-12-05' )  

Answer 2

"Would it be possible to do with a query some how, or do we have to step through each date in the program to check the number of reservations didn't exceed the number of cars? (This is easy enough with only full dates,"

The nature of your problem is that a violation of the constraint could appear on any individual date. So logically speaking, it is indeed necessary to do the check for each individual date comprised in a new reservations. The only optimisation possible would be to do the check at the level of "smallest intervals". To do that, you must first compute all the intervals that already appear in the database, and which overlap with your new reservation.

For example, a new reservation for 4/12-6/12 would have to be split into 4/12-5/12 (second line) and 5/12-6/12 (third line). Those individual intervals might be longer than one single day, and you can do the checks on the level of those individual intervals. (They are the same as individual days in this particular example, but a reservation 7/12-19/12 would not have to be split at all.

However, computing this might prove difficult, and there's another caveat: when you're looking al multi-row inserts, you should also be splitting over the other rows to be inserted (and that requires you to record all the inserted rows in a temporary table, otherwise you won't be able to access them).