餐厅预订-餐桌组合

Question

我在一家餐厅的预订系统里有三张桌子。到目前为止，该软件仅供餐厅工作人员使用，但我们也希望在线接受预订。我们有两个小桌子，可以很容易地移动到彼此之间，并为更大的各方腾出空间。如果所有可以合并的桌子都可用，我想自动接受预订。

tables：在餐厅的每个区域都放着所有的桌子。

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

reservations：保留预订详细信息

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

和一个保存预订<=>表关系的枢轴表：

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

如何有效地存储不同的表组合(手动输入)和“附加”对表/表组合的保留(这样我就可以有效地检查表是否可用于特定时间)？

编辑:更多细节，因为我离找到解决方案还差得很远：

我试图找出一种“描述”哪些表可以与其他表组合的方法，并在该组合中的一个表已经被保留的情况下“丢弃”一个组合。

例如，我有3张小餐馆表(不是数据库表:D)：

• #1适用于2-4人
• # 2 -4人
• #3适用于2-4人

表1和表2可以组合成一个供4-6人使用的表，而#2和#3可以组合成一个供4-6人使用的表。

但!表1和表3不能合并，因为它们相距太远。

下面是一幅关于情况的小图：平面图与组合体

我想手动“描述”哪些表可以与不同的表组合。我不知道如何存储这些数据，这样我就可以为特定的日期和时间保留表#1并自动“取消”组合#1和组合#3 (从图中)。

Answer 1

这是一个非常有趣的问题，而且总的来说是一个非常棘手的问题。对于你的问题，有一些限制，使它更容易。我在这里假设，如果有id:s是相邻的，那么一个表可以和另一个表组合。

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

如果无法做出这样的假设，则需要提供其他信息来描述哪些表组合是有效的：

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;

我将忽略后者，并进一步假设最多可以合并3个表：

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;

对于联合的第一阶段，聚合是不必要的，但我将保留它作为一般性的。

对于上面的样本数据，结果如下：

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 

有些供应商支持按多维数据集分组，这可以缩短查询时间，但是AFAIK、MySQL不支持。

下一个问题是预订能持续多久？如果不是固定的时间，比如说4小时，我们需要一个长度或一个停止时间。我假设：

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

我有点着急，所以这只是一张素描，但在特定的时间里，可用的桌子上有:S当时的座位:T可以确定为：

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

对于联盟的其余部分，您必须检查所涉及的表中没有一个被占用。