3

I have 3 tables in a reservation system for a restaurant. So far the software was used only by restaurant's staff, however we want to accept reservations online as well. We have some small tables for 2 that can be easily moved to each other and make room for bigger parties and I want to accept reservations automatically if all of the tables that can be combined are available.

tables: holds all tables for each area in the restaurant.

| id | min_capacity | max_capacity | name | area   |
|----|--------------|--------------|------|--------|
| 1  | 2            | 4            | #1   | Inside |
| 2  | 6            | 8            | #2   | Inside |

reservations: holds reservation details

| id | datetime            | name     | status   |
|----|---------------------|----------|----------|
| 1  | 2020-09-01 20:00:00 | John Doe | Upcoming |
| 2  | 2020-09-05 13:00:00 | Jane Doe | Upcoming |

And one pivot table that holds reservation <=> table relation:

| id | table_id | reservation_id |
|----|----------|----------------|
| 1  | 1        | 1              |
| 2  | 2        | 2              |

How can I store different combinations of tables (manually entered) and "attach" reservations to tables/table combinations (so I can check if tables are available for specific time) efficiently?

Edit: More details, as I am nowhere close to finding a solution:

I am trying to figure out a way of "describing" which tables can be combined with other tables and "discard" a combination if one of the tables in that combination is already reserved.

For example I have 3 small restaurant tables (not database tables :D):

  • #1 for 2-4 people
  • #2 for 2-4 people
  • #3 for 2-4 people

Tables #1 and #2 can be combined to make a table for 4-6 people and tables #2 and #3 can be combined to make a table for 4-6 people.

But! tables #1 and #3 cannot be combined, as they are too far away from each other.

Here's a little drawing of the situation: Floorplan & Combos

I want to manually "describe" which table can be combined with different table. I don't know how to store this data so I can reserve table #1 and that automatically "disqualifies" Combination #1 and Combination #3 (from the drawing) for the specific date & time.

0

1 Answer 1

2

It's a pretty interesting question and in general a very tricky one. For your question, there are some limitations that make it a lot easier. I'll assume here that a table can be combined with another table if there id:s are adjacent.

create table tables
( tabid int not null primary key
, min_capacity int default 2 not null
, max_capacity int default 4 not null );

insert into tables (tabid) values (1),(2),(3);

If such an assumption can't be made, there need to be additional information that describes which table combinations that are valid:

create table related_tables
( tabid int not null
, reltabid int not null
, primary key(tabid, reltabid)
-- anti-reflexive
, check (tabid <> reltabid));

-- symmetric
create view symmetric_related_tables as
select tabid, reltabid from related_tables
union all
select reltabid, tabid from related_tables;

I'll ignore the latter and also further assume that a maximum of 3 tables can be combined:

select x.tabid as tabcombo
    , sum(min_capacity), sum(max_capacity) 
from tables x 
group by x.tabid
union all
select 10*x.tabid+y.tabid
     , sum(x.min_capacity+y.min_capacity), sum(x.max_capacity+y.max_capacity)
from tables x
join tables y
   on y.tabid = x.tabid+1
group by x.tabid, y.tabid
union all
select 100*x.tabid+10*y.tabid+z.tabid, sum(x.min_capacity+y.min_capacity+z.min_capacity)
     , sum(x.max_capacity+y.max_capacity)
from tables x
join tables y
   on y.tabid = x.tabid+1
join tables z
   on z.tabid = y.tabid+1
   and y.tabid = x.tabid+1
group by x.tabid, y.tabid, z.tabid;

For the first leg of the union, the aggregation is not necessary, but I'll leave it for generality.

For the sample data above this results in:

tabcombo    sum(min_capacity)   sum(max_capacity)
1   2   4
2   2   4
3   2   4
12  4   8
23  4   8
123 6   8 

Some vendors support GROUP BY CUBE, which could have shortened the query, but AFAIK, MySQL does not.

The next question is how long a reservation lasts? If not a fixed amount of time, say 4 hours, we will need a length or a stop time. I'll assume:

create table reservations 
( reservation_id int not null primary key
, start_time datetime not null
, end_time datetime not null );

I'm in a bit of a hurry so this will only be a sketch, but for a specific time, the available tables with :S seats at the time :T can be determined as:

select x.tabid
    , sum(min_capacity), sum(max_capacity) 
from tables x
where not exists (
    select 1 
    from reservations r
    join reservation_tables rt
        on ...
    where rt.tabid = x.tabid
      and #T between r.start_time and r.end_time 
) 
group by x.tabid
having :S between sum(min_capacity) and sum(max_capacity) 
union all
...

for the remaining legs of the union, you will have to check that none of the involved tables are occupied.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.