Mysql – Restaurant reservations – Tables combinations

database-designMySQL

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.

Best Answer

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.

Related Question