mysql - 经常需要交集时存储多对多 SQL 关系的正确方法？

Question

我有越来越多的机器列表，每台机器都连接到不同类型的设备并且可以做不同的事情，我正在尝试为此构建某种排队系统。到目前为止，我的第一个想法是使用数据库表 machines 存储可用的机器，我将能够根据指定的功能从中选择所需的机器。所以表格看起来像这样:

machineId machineName 状态 equipment1 equipment2 network1 network2
1 次免费测试 0 1 1 0

代码会有一个参数列表，并执行搜索所有参数的代码。例如执行 SELECT * FROM Machines WHERE equipment2=1 AND network1=1 AND network2=0 AND status LIKE 'FREE'。

这在理论上可行，但有两个我想避免的问题:

1. 每个字段都需要在代码中定义，添加新列时也是如此
2. 有些字段是互斥的。 I.E 我不希望连接到网络 2 的计算机在某些情况下运行某些测试，并且某些设备会阻止机器执行某些基本功能(即使是那些不需要和设备的功能)，所以我必须添加该代码对其进行了特定的排除。例如。上面的 AND network2=0。

我的下一个想法是使用连接表。所以表格将是:

机器:
machineId machineName 状态

设备:
equipemntId equipmentName 独占

然后我会有一个联结表

机器设备:
machineId 设备Id

但是，如果我想要一台具有 equipment2 并可以访问 network1，但无法访问 network2 的机器(因为它是标记为独占)，并且也是免费，这对我来说太复杂了，我不知道该怎么做，或者即使这是正确的方法...

这是多对多的正确方法吗？我缺少哪些 SQL 片段？

Answer 1

您的第二个标准化版本是正确的方法。您可以使用 having 子句(也可以使用 where 子句)来解决您的查询。对于您的示例，查询是这样的:

select m.machineid
from machines m join
     machinesequipment me
     on me.machineid = m.machineid join
     equipment e
     on me.equipmentid = e.equipementid
where m.status = 'free'
group by m.machineid
having sum(e.equipmentname = 'equipment1') > 0 and   -- has "equipment1
       sum(e.equipmentname = 'equipment2') = 0;      -- does not have "equipment2"

我不知道 network 是如何存储在此模型中的，但包含它可能只是对此查询的一个简单调整。

对于网络，您可以添加其他子句，例如:

sum(e.equipmentname = 'network1') > 0

处理网络。