Approach to managing availability data

[keymaster]

Hi,

Firstly, thank you for taking the time to make your hard work publicly available. It is well appreciated.

I downloaded the source code and am trying to understand how you manage availability of rooms.

I have a few questions, hopefully you will not mind answering them:

1. which file contains the sql for creating the database?

2. Which tables handle the availability of hotel rooms (ie. from_date, to_date, num_rooms_available)?

3. What structure do you use for modelling the availability lists of rooms over time?

ie. how do you:

- determine if a room is free for a specified period?

- allocate a room and update the availability list?

- free a room and update the availability list

I am trying to figure out an efficient way in MySQL to model the management of availability lists, so performance is reasonable with large numbers of rooms and hotels.

Thanks very much.

[marco]

1. which file contains the sql for creating the database?

Tables that change every year are created in includes/funzioni_anno.php , other tables in creadb.php .

2. Which tables handle the availability of hotel rooms (ie. from_date, to_date, num_rooms_available)?

All information is contained in prenotaXXXX where XXXX is the year. There is not a field with the number of rooms available, it gets calculated by counting the reservations present in each period.

3. What structure do you use for modelling the availability lists of rooms over time?

This is mainly done by the functions in includes/liberasettimane.php . The function tab_a_var converts the database informations of prenotaXXXX in arrays that are then passed to the function liberasettimane, that will determinate if one (or more) of the requested apartments are free in the requested period. Liberasettimane works recursively trying to free the requested apartments, and if needed moves other reservations in their assigned apartments (this is done in the arrays, only at the end, if successful, the database is updated).

ie. how do you:

- determine if a room is free for a specified period?

Ask to liberasettimane function.

- allocate a room and update the availability list?

With liberasettimane, and then when the reservation record is inserted in prenotaXXXX the availability will be automatically updated everywhere. Reservations are inserted in prenota.php and clienti.php. Lliberasettimane is issued in clienti.php and the resulting data inserted in a transacion (in database table transazioni) that is passed to prenota.php where the reservation record is actually inserted. A transaction is also created when modifying a reservation in modifica_prenota.php, with the difference that this transaction contains also the movements that must be done to other reservations (when inserting a reservation the other reservations movements are updated in the database directly from clienti.php, regardless of the transaction end). To "move" a reservation you just have to change the apartment in prenotaXXXX.
The only availability list present in php-residence is the "indicative availability overview" (generated in includes/funzioni_quadro_disp.php with the arrays from tab_a_var). As the name says it is indicative because if a room type is available in day 1 and day 2 it doesn't mean you'll be able to insert a single reservation in days 1 and 2. Or if a room is currently occupied it doesn't mean that it can't be made free.

- free a room and update the availability list

Just delete the reservation record from prenotaXXXX, this is done in modifica_prenota.php .

I am trying to figure out an efficient way in MySQL to model the management of availability lists, so performance is reasonable with large numbers of rooms and hotels.

The time needed by liberasettimane to free a room grows exponentially with the number of rooms (naturally only when the hotel is almost full and there isn't already a free room). So over a certain number of rooms it would take too much time, so there is a time limit that you can set in "configure and customize". After that time the research for a free apartment will be given up, but consider that other hotel management systems won't even try to free the apartment, and cases when an apartment is freed after many seconds are very rare. The time limit doesn't actually completely stop liberasettimane, it only stops the start of new recursions. So for example if you have an hotel with 100 rooms (all of the same type, with separated different types it's faster) you can set the time limit to 4 seconds and the search should not take more than 20 seconds in the worst case (also depends on processor speed).

Marco

[keymaster]

Thanks for the detailed reply. Will have a look.