Page 1 of 1
compare date range
Posted: Sun Aug 31, 2008 4:25 am
by chelflores
Hi,
I'm having a small problem with a hotel booking script i'm writing, here gos
The hotels have a number of different rooms and can be reserved, and i need to calculate the availability date range of a room, eg. 31/08/08 - 10/09/08 The date where the guest want to reserve and the room is reserved in 05/09/08-15/09/08.
The only way i can see doing this is by splitting the range up into individual dates, then checking against the database for that individual date.
Any help/pointers would be appreciated
Many Thanks
Chel
Re: compare date range
Posted: Sun Aug 31, 2008 11:37 am
by califdon
chelflores wrote:Hi,
I'm having a small problem with a hotel booking script i'm writing, here gos
The hotels have a number of different rooms and can be reserved, and i need to calculate the availability date range of a room, eg. 31/08/08 - 10/09/08 The date where the guest want to reserve and the room is reserved in 05/09/08-15/09/08.
The only way i can see doing this is by splitting the range up into individual dates, then checking against the database for that individual date.
Any help/pointers would be appreciated
Many Thanks
Chel
The logic of reservations can become very tricky. I always go back to the concept of "entities"--how you define the thing that will be represented in a table. In this case, I think you are right, the entity is "a particular room that is reserved on a particular date", so a reservation will generally consists of a
series of such records. That leads to a schema something like this:
Code: Select all
[b]tblReservations[/b]:
RID Integer, auto-increment, primary key
CustID Integer, foreign key to customer table
... (other attributes of a reservation)
[b]tblRooms[/b]:
RmID Integer, auto-increment, primary key
Room Text (Room No.)
Hotel Text (Hotel Name)
Beds Integer
... (other attributes of a room)
[b]tblReservedRooms[/b]:
RRID Integer, auto-increment, primary key
RID Integer, foreign key to tblReservations
RmID Integer, foreign key to tblRooms
ResDate Date
It may seem like that's a lot of records in tblReservedRooms, but they are small records and I don't know of a better way to define the entities. When checking whether a room is available, all you have to do is loop through the
dates requested and check all rooms to see if each date is among the records in tblReservedRooms. Much simpler than checking whether a date is between two dates. At least I think so.