Good questions, I need to talk to the client to determine the exact
specifications. Thank you every one for helping me to figure out the
and for opening my view with your answers :)
On Wed, Aug 19, 2009 at 10:39 PM, Bob McConnell <r...@cbord.com> wrote:
> 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