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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
