Good questions, I need to talk to the client to determine the exact requirements and specifications. Thank you every one for helping me to figure out the potential db-scheme and for opening my view with your answers :) On Wed, Aug 19, 2009 at 10:39 PM, Bob McConnell <rvm@xxxxxxxxx> 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 > Reserved, > >> 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 > your > >> 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 > every > > 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 > -- Kind regards, -behzad
