From: Ashley Sheridan
> On Tue, 2009-08-18 at 19:15 +0430, Behzad wrote:
>> I'm faced with an interesting and challenging problem.
>> Consider a database, designed for a hotel.
>> At any given time, each room has a different status: It's Busy or
>> or Free.
>> It's easy to retrieve number of Free rooms at the current time.
>> But how can I count the number of rooms that were busy during the
last week
>> ?
>> I would appreciate if you take a brief moment of your time and share
>> opinion.
> Keep a table that lists all the rooms along with their current status
> Keep another table that has these fields:
>       * room_id (the id from above table)
>       * status (enumerated value - 'busy','reserved')
>       * start_date
>       * end_date
> Then you perform your query using a join of these two tables, within a
> particular date range. I've left out 'free' from the second table
> because there's no point updating the table for a period if a room is
> not being used.
> You could also have start_date and end_date as datetime fields, as
> hotel i've ever been in has a set time for check-in and another for
> check-out.

It would be easier if you kept a record of the status changes to and
from busy (check-in and check-out) as a transaction log. Then you can
scan the log to see the status changes for any time period. But you
still have to deal with a room that stayed busy for the entire period.

First question, does busy include the time needed by housekeeping to
clean the room after checkout? You might want to consider a separate
status for that.

Second question, does the system keep track of when each room is
reserved? If one is reserved for three nights beginning Friday, can it
still be used Wednesday for a one or two night stay?

Bob McConnell

PHP General Mailing List (
To unsubscribe, visit:

Reply via email to