Ideas on hotel reservation systems that interact with sql

[rbmbhs]

I am trying to design a database in Access 2003 that is capable of
intereacting with sql to check for availabilities of hotel rooms and either
confirm or deny a reservation request based on the query. Any ideas on how to
code something along those lines? Any help is greatly appreciated.

 

[Danny J. Lesandrini]

Reservation management is such a large topic, I would suggest you buy
a solution. There's a great product called EMS that is sold by Dean Evans
and Associates ... http://www.dea.com/

I worked on their product a while back and was amazed at the logic required
to prevent double-booking. That will be your greatest challenge.

 

[Albert D.Kallal]

The trick in a booking system is to only store the start and end date of the
booking.

And, to prevent collisions, the logic here is quite simple:


A collision occurs when:

RequestStartDate <= EndDate
and
RequestEndDate >= StartDate

The above is thus a rather simply query, but if any collision occurs, the
above will return records..and you simply don't allow the booking. In other
words, since we NEVER allow booking with a collision, then the above simply
statement will work for us.


dim strWhere as string
dim dtRequeestStartDate as date
dim dtRequestEndDate as date


dtRequestStartDate = inputbox("Enter start Date")
dtRequestEndDate = inputbox("Enter end date")


strWhere="#" & format(dtRequestStartDate,"mm/­dd/yyyy") & "# <= EndDate" & _
" and #" & format(dtRequestEndDate,"mm/dd­/yyyy") & "# >= StartDate"


if dcount("*","tableBooking",strW­here) > 0 then
msgbox "sorry, you can't book
....bla bla bla....

The above is just an example, and I am sure you would build a nice form that
prompts the user for the booking dates. Howver, what is nice here is that
the simple condistion above does return ANY collsion....

 

[Danny J. Lesandrini]

The trick is doing this with multiple users. Adds a magnitude of complexity.

 

[Albert D.Kallal]

The trick is doing this with multiple users. Adds a magnitude of
complexity.

Actually, building the system multi-user does not make the booking problem
much harder at all. In fact, all you need to add is some code to "lock" the
resource you are about to book. The rest of the code can remain much the
same as it would in a single user application.

Writing good software is hard, and that includes reservation software. I
been writing reservation systems for almost as long as I been writing code
(I nearly lost count of my reservation systems - Fishing lodge, Ground Tour
systems, Event management..this list goes on forever).

Those systems were difficult to write because they were complex. However,
the #1 tip I can give is that you do NOT want to store the quantity on
hand. My original systems *DID* store quantities, and they were very
difficult
to modify and maintain.

In my next generation systems that wrote, I NEVER stored the quantities on
had. This simplified these systems by huge amounts.

It matters not if you are checking out equipment, or TV projectors, or Video
DVD rentals,or even inventory. Really, all of these problems are VERY
similar. In all of these cases, the fatal mistake is when developers "store"
the amounts on hand.

For example, to book hotel rooms, you only need

StartDate, Enddate, RoomNumber

The above is all you need. The available, occupancy;etc. can all be then
calculated on the fly. The beauty of this approach is that then you can use
standard interface code in ms-access to delete a booking, or even change the
quantity of people booked. Since one does not store the number of people
booked, then you can freely delete records in the system, and NOT have to
have a bunch of complex code to update some quantity on hand. If I simply
delete the above record, then the booking is gone. Further, I can simply
change the start date, or end date (in fact the user would change that!!!,
and the booking is done. updated.

Contrast the above approach to that of system that actually "stores" the
quantities used. A simple date range change for a booking becomes a very
complex mess of code in which the DVD, or Room or whatever has to be
updated.

When you never store the quantity on hand, then you don't have to write code
to update the amounts! (and, that type of code tends to be difficult
anyway --- especially when you change the seats used from 4 to 5.

The funny part is that those systems that actually store the quantity on
hand
often have a set of routines to go and 'check' the actual data, and FIX when
the number of bookings is incorrect. One bad piece of code, or a improper
deleting of a record, and your quantity on hand is toast if you store the
value. With the above approach, no code is EVER used to update the amounts.

I suppose "easy" to make reservation systems is a relative term, but I
written so many, and learned so many lessons, that I do know how to write
these things with ease. If you take my above room booking example, and the
other sample code in my other post, you have the basic code setup for a
reservation system. A few forms, and bit code, you have your collision code
done. It really is a amazing trick, and almost makes writing these systems
too easy...

So, don't store the quantity on hand. By the way, there is also some
disadvantages to using this approach also. (reports tend to be harder to
write
for example).

I outline some lessons learned in a reservation system I wrote here:
http://www.members.shaw.ca/AlbertKallal/Articles/fog0000000003.html