Hotel booking

lukin

I am working on a hotel bookings application and I have an idea of how to prevent overbooking. Basically I am going to have a table with 365 fields in it and each of these fields will contain the amount of rooms in the hotel (ie 100).
Each time a user attempts to book a room the code will check the table to see if each value in the fields is equal to or greater than the number of rooms the user wants to book (between the dates selected by the user).
If the value is greater to or equal to the number of rooms required (for every field between these dates), I will subtract that number from it.
For example, to check for rooms free between the 1 Jan. and 8 Jan. I will check from field 1 to 8 and see if there are the number of free rooms required in each field between these dates.
If there is, the booking can go ahead and but if not then I will set a variable to say 'false' (the booking cannot be made).
It seems a bit excessive to have a table with 365 fields in it but the only other examples I've seen have grids with the dates along the top and room numbers along the side so the user has to check his/herself.
I just want the room availability to be determined by the execution of code and a simple "yes" or "no" at the end.
The way I have described above is the best I can think of, I am just wondering is there an easier, less obvious way I haven't thought of?
Btw, I don't want anyone to write any code or do any work for me, just opinions. Thanks.

Civilian_Target

I work in the travel industry, and generally it doesn't work like that.

Users book rooms, not beds, so the number of rooms is more important than their capacity.
Actually, we represent each room as an object, give it an arbitrary:
- Normal capacity
- No. of extra beds (ones you can add for young kids, for example)
(We also store the total number of extra beds each hotel can supply)

We don't check the travellers against the capacity, and that's delibirate, what if it's a couple in a double room with 3-month old twins? We do generally print a warning if the number of adults assigned to the room exceed the normal capacity.

lukin

Civilian_Target wrote: »

I work in the travel industry, and generally it doesn't work like that.

Users book rooms, not beds, so the number of rooms is more important than their capacity.
Actually, we represent each room as an object, give it an arbitrary:
- Normal capacity
- No. of extra beds (ones you can add for young kids, for example)
(We also store the total number of extra beds each hotel can supply)

We don't check the travellers against the capacity, and that's delibirate, what if it's a couple in a double room with 3-month old twins? We do generally print a warning if the number of adults assigned to the room exceed the normal capacity.

I am taking into account rooms, not beds. I am going to have two options, single and double rooms so the room booked is either a single or double.

Civilian_Target

ah, cool.

I still wouldn't bother doing a table like that though.
Store a list of occupied dates for each room, it's much easier.

bonkey

Civilian_Target wrote: »

Store a list of occupied dates for each room, it's much easier.

ASsuming its in a DB, this approach makes a SUM easy, which - when done with GROUP BY date, allows you to see hte number of bookings per day for rooms.


lukin - if you do go with the table idea....you'll have a problem come 2012 when you discover that there's a whole day's worth of bookings missing.

lukin

bonkey wrote: »

lukin - if you do go with the table idea....you'll have a problem come 2012 when you discover that there's a whole day's worth of bookings missing.

Yeah I know, leap year, that kind of puts the kibosh on the year table idea.
It looks like a choice between a table for the year or a table for the number of rooms.
I was thinking that a rooms table and a reservations table is the way to go:

CREATE TABLE Rooms (roomnumber INT, type VARCHAR(10))
CREATE TABLE Reservations (roomnumber INT, reserved DATE)

Rooms contains all the room numbers (roomnumber) and a room type for each room (type).
Reservations contains the dates any room is reserved (reserved) and room number of that room (roomnumber).
That way I can find the number of rooms of a certain type available within a certain date range (I think).

Civilian_Target

I don't get it.
Why are you tying your application so tightly to the database?

Personally, I would store each customer and hotel room as objects and link them.

In the eighties we had solutions that involved big tables of rooms and days but technology has given us better ways since then.

lukin

Civilian_Target wrote: »

I don't get it.
Why are you tying your application so tightly to the database?

Personally, I would store each customer and hotel room as objects and link them.

In the eighties we had solutions that involved big tables of rooms and days but technology has given us better ways since then.

In my app, the user enters a date range and a room type and searches for a reservation. It returns a yes or no based on what it finds. It's a .NET app, just a little project to give myself more experience.
Storing each customer and hotel room as an object sounds like Hibernate to me, but that's Java. I have limited experience in this type of thing, I don't expect anyone to write the code for me, but I am welcome to ideas, so thanks.
I can't see any way I can do it without having a reservations table and a rooms table though.

marco_polo

lukin wrote: »

In my app, the user enters a date range and a room type and searches for a reservation. It returns a yes or no based on what it finds. It's a .NET app, just a little project to give myself more experience.
Storing each customer and hotel room as an object sounds like Hibernate to me, but that's Java. I have limited experience in this type of thing, I don't expect anyone to write the code for me, but I am welcome to ideas, so thanks.
I can't see any way I can do it without having a reservations table and a rooms table though.

Once perhaps it was just java, but not anymore. Meet NHibernate. It is usually a few versions behind the java version but I have used it before and it is quite solid. Also .NET 3.5 come with ADO.NET Entity Framework but I have not used this myself.

https://www.hibernate.org/343.html

Civilian_Target

lukin wrote: »

In my app, the user enters a date range and a room type and searches for a reservation. It returns a yes or no based on what it finds. It's a .NET app, just a little project to give myself more experience.
Storing each customer and hotel room as an object sounds like Hibernate to me, but that's Java. I have limited experience in this type of thing, I don't expect anyone to write the code for me, but I am welcome to ideas, so thanks.
I can't see any way I can do it without having a reservations table and a rooms table though.

Understood.
But think about it, it makes sense to do the job right.

The table is not without it faults, as rightly pointed out, what about leap years? What about extra guests?
Whatever system you implement, if it doesn't meet the needs of you customer, it is wrong, and I suspect the hotel is open Feb 29th 2012! And what if they want to make a booking for next year on Dec 24th?

Keep it really simple, it reduces the chances of going wrong. Just store rooms + booking dates, and build a list, or if you can think of a more simple system, tell me so that I can use it! It is more intensive, but it is much less likely to cause problems

lukin

Having thought a bit more about it, it seems I have a choice between two options:

At table of rooms with 1. a room number and 2. a room type
A table of room reservations with 1. start datetime field and 2. end datetime field and 3. the room number

or else, what I have said earlier:

A table of rooms with 1. a room number and 2. a room type
A table of room reservations with 1. a roomnumber and 2. reserveddate

The reservations table in the second option would be very big as it would have to enter every date each room is booked for so maybe the first one is better.

John_Mc

I implemented this myself in a working system with over 1200 reservations on it so far. Will be redoing it again sometime soon as my coding was a bit poor back then, but the underlying way it works will remain the same.

I have a table containing room information, including a base rate and the number of rooms of this type that are available for online booking each night. This allows many types of room with different capacities, prices etc.

I have an amended availability feature which allows hotel staff to override the standard booking for a particular room on a given date, or range of dates.

Reservations are stored in another table, and when a query for availability is made for a room on a given date, the system checks the amended availability table first, and if nothing found, then the room table for the standard availability. It sums the existing reservations for that date and room, and compares it to the current availability and gives its answer.

Obviously there's a bit more to handling rates but it's more or less handled in the same way.

Would this work for you?

jimmycrackcorm

With over 20 years experience in software development, i say that i like the op's approach. The storage is no issue and the key benefit is that it is a very simple and maintainable solution. The best solutions are always the simplest.