Reservation algorithm

[jimmy]

I'm in the process of developing a reservation management system for
small restaurants. I am stuck on creating an algorigthm for managing
the reservations. For example i need to check that a table is avaialbe
and when etc. I understand that i'll need a database table containing
information about the tables in the restaurant but further than this im
pretty clueless so any help would be much appreciated.

[vbnetdev]

Jimmy

Contact me off list. You need a consultant to help you. You can't really
plan something like this here.

Kelly

"jimmy" <ja**************@tiscali.co.ukwrote in message
news:11********************@48g2000cwx.googlegroup s.com...

I'm in the process of developing a reservation management system for
small restaurants. I am stuck on creating an algorigthm for managing
the reservations. For example i need to check that a table is avaialbe
and when etc. I understand that i'll need a database table containing
information about the tables in the restaurant but further than this im
pretty clueless so any help would be much appreciated.

[jimmy]

Yes this is, i have completed the other parts of the project (stock
management and actually storing the reservations in a database and
searching etc) but i cant get my head around how to do this.

Thanks James

[Chris Dunaway]

jimmy wrote:

Yes this is, i have completed the other parts of the project (stock
management and actually storing the reservations in a database and
searching etc) but i cant get my head around how to do this.

So you can store the reservations in the database? I assume that you
have some way to information about the tables such as number of
parties, whether it's a booth or a table, whether it's near a window or
not, whether it's close to the stage (if there is a stage), etc. So a
reservation would be a table for a specific time?

So, how do you envision the program working? Will someone call to
request a reservation? If they ask for a table for 4, near a window,
for 8:00pm, you will have to query your associated database for a table
that matches that description. Then based on the data stored in the
reservations table, you can check to see if a reservation already
exists for that table at that time. A possible db setup might include
the following:

DinnerTable (holds information about the available tables)

Id int
Capacity int
Type int -- 0 = table, 1 = booth
NearWindow bit
Reservations

Id int
DinnerTableId int --FK to Tables table
Time datetime
Duration int --Length of the reservation in
minutes

These are only simple examples to help you start thinking.

Your queries would have to be able to figure out which tables are
reserved (or still in use), which tables are likely to be vacated, and
which tables are free.

If a party is using a table at 8:00pm and someone calls to reserve that
table for 9:00pm, can you put that down as a reservation even though
the first party might linger at the table past 9pm? Or would you tell
the caller that that table is reserved for 8pm and it might not be free
exactly at 9pm and that they may have to wait until 9:15? These are
things to consider.

And you'll probably need a way to cancel a reservation.

Break the program into small tasks and begin solving each of those
tasks, keeping in mind all of the requirements for the project.

Hope there is at least some useful nuggets of information in here for
you.

Good Luck.

[David Browne]

"Chris Dunaway" <du******@gmail.comwrote in message
news:11**********************@f1g2000cwa.googlegro ups.com...

jimmy wrote:

>Yes this is, i have completed the other parts of the project (stock
management and actually storing the reservations in a database and
searching etc) but i cant get my head around how to do this.

So you can store the reservations in the database? I assume that you
have some way to information about the tables such as number of
parties, whether it's a booth or a table, whether it's near a window or
not, whether it's close to the stage (if there is a stage), etc. So a
reservation would be a table for a specific time?

So, how do you envision the program working? Will someone call to
request a reservation? If they ask for a table for 4, near a window,
for 8:00pm, you will have to query your associated database for a table
that matches that description. Then based on the data stored in the
reservations table, you can check to see if a reservation already
exists for that table at that time. A possible db setup might include
the following:

DinnerTable (holds information about the available tables)

Id int
Capacity int
Type int -- 0 = table, 1 = booth
NearWindow bit
Reservations

Id int
DinnerTableId int --FK to Tables table
Time datetime
Duration int --Length of the reservation in
minutes

These are only simple examples to help you start thinking.

Your queries would have to be able to figure out which tables are
reserved (or still in use), which tables are likely to be vacated, and
which tables are free.

If a party is using a table at 8:00pm and someone calls to reserve that
table for 9:00pm, can you put that down as a reservation even though
the first party might linger at the table past 9pm? Or would you tell
the caller that that table is reserved for 8pm and it might not be free
exactly at 9pm and that they may have to wait until 9:15? These are
things to consider.

And you'll probably need a way to cancel a reservation.

Break the program into small tasks and begin solving each of those
tasks, keeping in mind all of the requirements for the project.

Hope there is at least some useful nuggets of information in here for
you.

And go to the restaurant and pester the host to learn how the process is
done. Once you start thinking about the data structures and know how to do
the job, things will start to fall into place.

David

[jimmy]

Yes thanks alot this has helped me get a little further however i had
hoped to expand on this to offer a web interface for users to book
themselves in however i guess there is no easy way to do so as it will
be near impossible to estimate what time the table will be free.

I have had an idea whereby a query finds all suitable tables as
suggested, and then all tables are searched to see if there are any
reservations made for them. If there is a table that has no
reservations then this table can be allocated or else the reservation
times are searched to find the table with a reservation that is
furthest away from the requested reservation. If there are no tables
free within an hour of requested time then the reservation will not be
made.

Does this sound like a suitable solution for an 'unattended'
reservation?

[Keith]

I think that you would need to use historical data (as your database grows)
to look at the time it takes for a table to "free up" at each site, which
might include average and standard deviation (based on a variety of factors,
including party size, time of day, day of the week, month of the year
(seasonal), events [is a band playing, etc.]). Most restaurants know this
from experience, but an unattended system will have to use logic to figure
it out and predict how long a party of 4 at 7pm on a Thursday is likely to
be "using" a table.

You probably also need a threshold value for each restaurant indicating how
long they are willing to have a person wait before being seated. Come to
think of it, you may need that for both reservations (some places you make a
reservation just to get in, and you might still wait 20 minutes to be
seated, in others you expect to be seated immediately) and walk-in clients
(who presumably will have a higher tolerance for waiting, but if the wait is
too long, they leave). Again, your ability to hit those targets 95%+ of the
time would be predicted from the historical data.

You should be able to use statistical modelling (there are software programs
that do nothing but modelling) to predict all sorts of great information
based on the historical data, if you are willing to learn some complicated
statistics. Of course, the restaurants would have to be diligent about
clocking out tables to collect good data... and at that point, you'd also
need data on the time it takes to turn over (clean) a dirty table.

I strongly second David's suggestion - unless you have worked in the
restaurant environment and already have the knowledge, go pester folks at
multiple restaurants to see how they currently process reservations to
maximize business while maintaining customer service.
"jimmy" <ja**************@tiscali.co.ukwrote in message
news:11*********************@48g2000cwx.googlegrou ps.com...

Yes thanks alot this has helped me get a little further however i had
hoped to expand on this to offer a web interface for users to book
themselves in however i guess there is no easy way to do so as it will
be near impossible to estimate what time the table will be free.

I have had an idea whereby a query finds all suitable tables as
suggested, and then all tables are searched to see if there are any
reservations made for them. If there is a table that has no
reservations then this table can be allocated or else the reservation
times are searched to find the table with a reservation that is
furthest away from the requested reservation. If there are no tables
free within an hour of requested time then the reservation will not be
made.

Does this sound like a suitable solution for an 'unattended'
reservation?

[David Browne]

"jimmy" <ja**************@tiscali.co.ukwrote in message
news:11*********************@48g2000cwx.googlegrou ps.com...

Yes thanks alot this has helped me get a little further however i had
hoped to expand on this to offer a web interface for users to book
themselves in however i guess there is no easy way to do so as it will
be near impossible to estimate what time the table will be free.

I have had an idea whereby a query finds all suitable tables as
suggested, and then all tables are searched to see if there are any
reservations made for them. If there is a table that has no
reservations then this table can be allocated or else the reservation
times are searched to find the table with a reservation that is
furthest away from the requested reservation. If there are no tables
free within an hour of requested time then the reservation will not be
made.

Does this sound like a suitable solution for an 'unattended'
reservation?

I think you need to ask the pros. Many of these questions are really domain
business questions. EG how many reservations can I offer a night? How long
should I expect a table to sit? Are reservations taken for specific tables?

David

[jimmy]

Thanks guys, All your advice has been really helpful however i now
realise that this is obviously alot harder than i had first thought.
The web interface is not required for the project however i was
considering making for extra wow and maybe to distribute as an
OpenSource project. I have decided that i will implement the
'supervised' reservation system first and then look at creating the
'unsupervised' system as and when i have time.

Thanks once again,

James.

[aaron.kempf]

I think that you need to keep it in one table.. instead of searching
for a table that has no reservations

-Aaron

jimmy wrote:

Yes thanks alot this has helped me get a little further however i had
hoped to expand on this to offer a web interface for users to book
themselves in however i guess there is no easy way to do so as it will
be near impossible to estimate what time the table will be free.

I have had an idea whereby a query finds all suitable tables as
suggested, and then all tables are searched to see if there are any
reservations made for them. If there is a table that has no
reservations then this table can be allocated or else the reservation
times are searched to find the table with a reservation that is
furthest away from the requested reservation. If there are no tables
free within an hour of requested time then the reservation will not be
made.

Does this sound like a suitable solution for an 'unattended'
reservation?

[Simon Verona]

Of course there is the additional item of not wanting to seat too many
tables at the same time.. they will all then be ordering together and will
have longer waits for service.....

So maybe a flag for how many tables (people?) can be sat within any 15
minute period or something similar....

Just to make life that little more interesting...

Simon
"jimmy" <ja**************@tiscali.co.ukwrote in message
news:11**********************@80g2000cwy.googlegro ups.com...

Thanks guys, All your advice has been really helpful however i now
realise that this is obviously alot harder than i had first thought.
The web interface is not required for the project however i was
considering making for extra wow and maybe to distribute as an
OpenSource project. I have decided that i will implement the
'supervised' reservation system first and then look at creating the
'unsupervised' system as and when i have time.

Thanks once again,

James.

[aaron.kempf]

I still think that you should keep everything in ONE TABLE instead of
having a zillion different tables; one for each customer

-Aaron
Simon Verona wrote:

Of course there is the additional item of not wanting to seat too many
tables at the same time.. they will all then be ordering together and will
have longer waits for service.....

So maybe a flag for how many tables (people?) can be sat within any 15
minute period or something similar....

Just to make life that little more interesting...

Simon
"jimmy" <ja**************@tiscali.co.ukwrote in message
news:11**********************@80g2000cwy.googlegro ups.com...

Thanks guys, All your advice has been really helpful however i now
realise that this is obviously alot harder than i had first thought.
The web interface is not required for the project however i was
considering making for extra wow and maybe to distribute as an
OpenSource project. I have decided that i will implement the
'supervised' reservation system first and then look at creating the
'unsupervised' system as and when i have time.

Thanks once again,

James.