Best practice on hotel room allocations

[economyman]

Hi

I'm building a DB to manage a seasonal booking system for a tour operator that runs some special events during the year at several hotels. The operator receives allocations of rooms for a given period (from date to date) including sometimes several different room types per hotel per event.

I want the system I am designing to be able to check each time a new booking is entered whether there is room still available. It's rather complex in that sometimes a booking may begin before or extend after the specific event dates and thus be outside the allocation period. But I'll find a way (I hope) to deal with that. Another point is that sometimes, a room is used for two different booking during the same event if say for example some people come for the first half and the other for the second half.

Now, here's my questions: should I build some kind of matrix for each room allocated for each night allocated and have an occupied yes/no field for each point in the matrix so that it is easy to look up or would this go against the rule of recording calculated fields? After all I have a table with the allocation details of room type and from / to date and I have the booking. It's possible (although I don't yet know how) to build a function to calculate availability using these two pieces of information rather than doing it with a quick look up in the matrix.

I'm pretty sure, if I was working in good old DOS basic that's how I would have done it (with a DIM).

Of course I would prefer to have something as general as possible and don't want to have to build new tables manually each time a new event / allocation is added.

I would really appreciate any input available.

 

[economyman]

Maybe I should reiterate my quesiton - should I create a multi-dimensional array of some sort and record for each night and each room a booking ID so it is clearly visible how many nights /rooms are taken / free or should this be calculated each time from the underlying allocation and booking tables.

Thanks

 

[economyman]

I am attaching the current db architecture. It's just the beginning but really my question relates mainly to the allocation table.

At the moment as can be seen in the attached picture, it includes an allocation per room type per date range.

Would I be better off having an allocation per room type per date (rather than date range) and then have another field with the number of bookings for that room type for that date.

Obviously it's possible to calculate the number of rooms booked per night per room type from the BookedRooms table but that seems like a rather complex calculation required each time a booking is made and availability needs to be checked.

I now realise this thread should possibly have been opened in database design section. Sorry about that - is there a way to move it?

 

[alktrigger]

Here is something that I hope can help you. Its a very basic development, but it should point you in the right direction.

I converted from 2007 to 2000 so if there are compatibility problems, let me know.

 

[economyman]

Here is something that I hope can help you. Its a very basic development, but it should point you in the right direction.

I converted from 2007 to 2000 so if there are compatibility problems, let me know.

Thanks alktrigger. There are compatibility issues. Could you send the 2007 version - I'm using Access 2007.

Thanks

economyman

 

[alktrigger]

Sure, here you go

 

[economyman]

Thank you. That's given me some idea of an approach, although this would require me to set up a record for each room and in my case specific dates it is available because we have an allocation and it is limited in time (it's not all year). I was hoping to avoid such database chores such as building a table that has rooming lists but instead to automate it all - maybe it cannot be avoided.

 

[alktrigger]

Using this method will allow you to keep records of who used what rooms over a long period of time. This can let you analyze specific customers and potentially use this database for marketing/promotions/customer loyalty. But as for "setting up a record for each room" would be nearly automatic once you have your user interface built. I've found that its better to make make the leap and put in all the features, even if you don't use them from the start, rather than regretting it and trying to add them in later. The rooming table might grow very large, but since it has a small quantity of fields your database should stay relatively small.

My question for you (in the case where you are avoiding a rooming table) how were you planning on setting up multiple reservations for the same room? Using a "available" check box would work if you were running a motel with no reservations, but keeping track of open dates would be significantly more difficult.

My suggestion is to take the route that would have less chance for error in the long run (user or programming).

 

[The_Doc_Man]

Search this forum for "Bookings" and "Reservations" and "Scheduling" - all of which are topics that include threads similar to your request. Note that for "Scheduling" you may also see threads on scheduling Access itself to do something on a timed basis, which is another meaning of the word "Scheduling" in this context.

 

[economyman]

Using this method will allow you to keep records of who used what rooms over a long period of time. This can let you analyze specific customers and potentially use this database for marketing/promotions/customer loyalty. But as for "setting up a record for each room" would be nearly automatic once you have your user interface built. I've found that its better to make make the leap and put in all the features, even if you don't use them from the start, rather than regretting it and trying to add them in later. The rooming table might grow very large, but since it has a small quantity of fields your database should stay relatively small.

My question for you (in the case where you are avoiding a rooming table) how were you planning on setting up multiple reservations for the same room? Using a "available" check box would work if you were running a motel with no reservations, but keeping track of open dates would be significantly more difficult.

My suggestion is to take the route that would have less chance for error in the long run (user or programming).

alktrigger - I am trying to take your approach. I have a booked rooms table (not equivalent to bookings because bookings can have several rooms) but I have an additional issue which is the allocation. The company gets a room allocation, say 140 rooms of a particular type and 50 of another for say 10 days. Bookings will typically be for the period of the allocation but could be shorter or longer. If longer a request is made to the hotel. I want to be able to do several things: (1) check and display the number of rooms of a given room type for a given date or date range left in the allocation which needs to take into account the number of rooms already booked and the specific dates they have been booked for; (2) check if a booking request for a certain date range is possible based on the number of rooms left for each day in the date range and if not to make a request to the hotel for the dates outside the allocation or beyond the allocation. If the booking can be confirmed then it is done on the spot.

I have attached the database structure in a picture. Maybe someone could have a quick look and tell me if I am in the right direction. I believe I simply need to delve into some serious VB programming to get this done and since I haven't programmed much for 20 years I'll just need to brush off the rust a bit.