DB design issues...

I hate working with date ranges...

[Gibble]

Ok the basic problem is between two tables.

Table 1 contains blocks of hotel rooms. Meaning it has a HotelId, NumberOfRooms in the block, and a date range (date1, date2).

Table 2 is of reservations. They relate to a HotelId, and are for a certain NumberofRooms and a date range...BUT the problem is that they aren't neccessarily for the same range of days as the block of rooms in Table 1.

The problem is I need reliable stable way to keep track of what reservations have been made for what days, for how many rooms so I know what's left in the blocks of rooms that are available.

Should I leave the tables as is, and rely more on code to determine what's left for each day. Or should I introduce some tables to keep track that could easily be unreliable and corrupted...or at least that's how I percieve it...

suggestions and ideas would be greatly appreciated.

[txskydiver]

Do the date ranges between the two tables overlap? In other words:

Table 1 - 4/1/2004 to 5/1/2004
Table 2 - 4/15/2004 to 5/15/2004

or are the values of the reservation table limited to all or part of the range within table 1?

[Gibble]

The ranges in Table 2 will always be limited to be all or part of the range in Table 1

See what happens is they book X rooms in Y hotel for nights A through B. (Table 1)

Then they make reservations to clients (Table 2) for the rooms in (Table 1).

But the reservation may or may not be the same length as the range in Table 1, but the dates must be obviously (so the room is booked) fall inside the range of Table 1.

Make sense?

The question is, should I make the database or program more complicated...

[txskydiver]

I would have to lean toward controlling it with code on the page, unless you needed it stored somewhere for some reason.

Is this for an online reservations site? Can the user "reserve" any date they choose from the available blocks of dates?

If so, I'd be interested to see the form you are using for the reservations, and how you are limiting them to the "open" dates.

[Gibble]

It'll be for internal use by the agents.

But basically it's to prevent them from overbooking since their paper process currently is far from foolproof!

[Netizen]

Personally I would split the data up so that you have two tables which have hotelID, numberOfRooms and date in them. This will add to the number of records but make the queries and accounting easier. The first table says that a particular hotel has a block of rooms available on a particular date. The second table would have individual bookings for that hotel and date.

You could then select and sum the number of reservations to make sure it is less then the allocated block.

Just my $0.02

[Gibble]

Netizen...that's the way I was thinking, the problem is that the date ranges aren't the same...so eg.

Block1 Jan 4-10 10 Rooms

Booking1 Jan 4-10 2 Rooms
Booking2 Jan 6-10 1 Rooms
Booking3 Jan 4-8 4 Rooms

...How do I quickly know how many rooms I have left on the 9th?

[Netizen]

Ah, maybe I wasn't clear enough...

Block1 Jan 4-10 10 Rooms

Booking1 Jan 4-10 2 Rooms
Booking2 Jan 6-10 1 Rooms
Booking3 Jan 4-8 4 Rooms

I think you need to end up with something link

Block 1 Jan 1 10 rooms
Block 1 Jan 2 10 rooms

etc

and

Booking1 Jan 1 2 Rooms
Booking1 Jan 2 2 Rooms
Booking1 Jan 3 2 Rooms
...
Booking2 Jan 6 1 Rooms
Booking2 Jan 7 1 Rooms
Booking2 Jan 8 1 Rooms
...
Booking3 Jan 4 4 Rooms
Booking3 Jan 5 4 Rooms
Booking3 Jan 6 4 Rooms
...

then you can add up the rooms allocated to bookings by date.