DB Question | A hotel reservation scenario

--00032555bc9af4b5d804716b916e
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

Dear list,
e-Greetings!

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.

Thank you in advance,
-b

--00032555bc9af4b5d804716b916e--

Re: DB Question | A hotel reservation scenario

On Tue, 2009-08-18 at 19:15 +0430, Behzad wrote:
> Dear list,
> e-Greetings!
>
> 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.
>
> Thank you in advance,
> -b

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.

Thanks,
Ash
http://www.ashleysheridan.co.uk




--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: DB Question | A hotel reservation scenario

On Tue, Aug 18, 2009 at 10:45 AM, Behzad wrote:
> Dear list,
> e-Greetings!
>
> 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.
>
> Thank you in advance,
> -b
>

query using the BUSY status as a parameter? But really you haven't
given enough to ascertain whether the structure is set to allow this
to happen


--

Bastien

Cat, the other other white meat

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: DB Question | A hotel reservation scenario

-----Original Message-----
From: Behzad [mailto:behzad.eslami@gmail.com]
Sent: 18 August 2009 04:46 PM
To: PHP General Mailing List
Subject: [PHP] DB Question | A hotel reservation scenario

Dear list,
e-Greetings!

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.

Thank you in advance,
-b
---------

You get that information from the history table. I presume there is some
sort of a history table?

Cheers
Arno


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: DB Question | A hotel reservation scenario

to answer this is in fact not possible on the base of information u give.

I dont think there is a general db outlay for hotels. it depends how the
"booking" tables are designed.

does the application excist or u are doing a new one?

if it excist, have a look how the availability of a room is calculated and
then go from there. it would be the same calculation, just with backwards
dates.

hope that helps

ralph_deffke@yahoo.de



"Behzad" wrote in message
news:470fa6660908180745i6bb6a442xd53d2c02fac7bb8d@mail.gmail .com...
> Dear list,
> e-Greetings!
>
> 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.
>
> Thank you in advance,
> -b
>



--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Re: DB Question | A hotel reservation scenario

--00032555b042eaea8c04716d10c0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

Based on Ashely's reply, I created the following scheme:

The table "Rooms" has the following fields.

- id (PK)
- room_no (varchar)
- status (ENUM: Busy, Reserved)

the other table, "RoomLogs", has the following fields:
- room_id (the id from above table)
- status_modified_on (date)
- status (enumerated value - 'busy','reserved')

The problem is to generate a graph to display status of rooms in different
periods of
time (daily, weekly, monthly).
X Axis = Status (Busy, Reserved)
Y Axis = Number of rooms with a certain status (the last status for each
room makes sense)


On Tue, Aug 18, 2009 at 8:13 PM, Ralph Deffke wrote:

> to answer this is in fact not possible on the base of information u give.
>
> I dont think there is a general db outlay for hotels. it depends how the
> "booking" tables are designed.
>
> does the application excist or u are doing a new one?
>
> if it excist, have a look how the availability of a room is calculated and
> then go from there. it would be the same calculation, just with backwards
> dates.
>
> hope that helps
>
> ralph_deffke@yahoo.de
>
>
>
> "Behzad" wrote in message
> news:470fa6660908180745i6bb6a442xd53d2c02fac7bb8d@mail.gmail .com...
> > Dear list,
> > e-Greetings!
> >
> > 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.
> >
> > Thank you in advance,
> > -b
> >
>
>
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>


--
Kind regards,
-behzad

--00032555b042eaea8c04716d10c0--

Re: Re: DB Question | A hotel reservation scenario

On Tue, 2009-08-18 at 21:02 +0430, Behzad wrote:
> Based on Ashely's reply, I created the following scheme:
>
> The table "Rooms" has the following fields.
>
> - id (PK)
> - room_no (varchar)
> - status (ENUM: Busy, Reserved)
>
> the other table, "RoomLogs", has the following fields:
> - room_id (the id from above table)
> - status_modified_on (date)
> - status (enumerated value - 'busy','reserved')
>
> The problem is to generate a graph to display status of rooms in different
> periods of
> time (daily, weekly, monthly).
> X Axis = Status (Busy, Reserved)
> Y Axis = Number of rooms with a certain status (the last status for each
> room makes sense)
>
>
> On Tue, Aug 18, 2009 at 8:13 PM, Ralph Deffke wrote:
>
> > to answer this is in fact not possible on the base of information u give.
> >
> > I dont think there is a general db outlay for hotels. it depends how the
> > "booking" tables are designed.
> >
> > does the application excist or u are doing a new one?
> >
> > if it excist, have a look how the availability of a room is calculated and
> > then go from there. it would be the same calculation, just with backwards
> > dates.
> >
> > hope that helps
> >
> > ralph_deffke@yahoo.de
> >
> >
> >
> > "Behzad" wrote in message
> > news:470fa6660908180745i6bb6a442xd53d2c02fac7bb8d@mail.gmail .com...
> > > Dear list,
> > > e-Greetings!
> > >
> > > 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.
> > >
> > > Thank you in advance,
> > > -b
> > >
> >
> >
> >
> > --
> > PHP General Mailing List (http://www.php.net/)
> > To unsubscribe, visit: http://www.php.net/unsub.php
> >
> >
>
>
You should add a 'free' type to the first table (just the first one) to
indicate whether a room is free or not also.

As for counting the number of rooms that were busy last week, think
about what that actually means. Does that mean all rooms that were busy
the entire time, all rooms that were busy at least part of the time
during that week, etc?

Thanks,
Ash
http://www.ashleysheridan.co.uk




--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: DB Question | A hotel reservation scenario

I would create a room history table that contained three fields: room
number, status, date stamp. Each time the status of a room changes
insert a new record into the table with the current status and date/
time.

Take care,
Floyd

On Aug 18, 2009, at 10:45 AM, Behzad wrote:

> Dear list,
> e-Greetings!
>
> 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.
>
> Thank you in advance,
> -b


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: DB Question | A hotel reservation scenario

At 2:46 PM -0400 8/18/09, Floyd Resler wrote:
>I would create a room history table that contained three fields:
>room number, status, date stamp. Each time the status of a room
>changes insert a new record into the table with the current status
>and date/time.
>
>Take care,
>Floyd

That's the way I would handle it -- clean and simple.

That way not only would have have the current status of each room,
but you could use that data to perform all sorts of analysis, such as
which rooms are most popular, what dates provide the most traffic,
which rooms may need more maintenance, and so on.

Cheers,

tedd
--
-------
http://sperling.com http://ancientstones.com http://earthstones.com

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: DB Question | A hotel reservation scenario

From: Ashley Sheridan
> On Tue, 2009-08-18 at 19:15 +0430, Behzad wrote:
>>=20
>> I'm faced with an interesting and challenging problem.
>>=20
>> Consider a database, designed for a hotel.
>> At any given time, each room has a different status: It's Busy or
Reserved,
>> or Free.
>>=20
>> 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
>> ?
>>=20
>> I would appreciate if you take a brief moment of your time and share
your
>> opinion.
>=20
> Keep a table that lists all the rooms along with their current status
>=20
> Keep another table that has these fields:
> * room_id (the id from above table)
> * status (enumerated value - 'busy','reserved')
> * start_date
> * end_date
>=20
> 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.
>=20
> 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

Re: DB Question | A hotel reservation scenario

--00151750d9d45e126404718e1907
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

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 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

--00151750d9d45e126404718e1907--