sql - 设计题: Filterable attributes, SQL

Question

我的数据库中有两个表，Operation 和 Equipment。一个操作需要零个或多个属性。但是，属性的​​归属方式有一些逻辑:

• 操作Foo需要设备A和B
• 操作酒吧不需要任何设备
• 行动Baz 需要设备B 和C 或D
• 操作Quux需要设备(A 或B)和(C 或D)

用 SQL 表示它的最佳方式是什么？

我敢肯定以前有人这样做过，但我不知道从哪里开始。

(FWIW，我的应用程序是用 Python 和 Django 构建的。)

更新 1:将有大约一千行 Operation 行和大约三十行 Equipment 行。信息以类似于上面描述的 CSV 格式出现:Quux, (A & B) | (C & D)

更新 2:连词和析词的层次不应该太深。 Quux 示例可能是最复杂的，尽管似乎有一个 A | (D & E & F) 案例。

Answer 1

考虑如何在 OO 设计中对操作建模:操作将是公共(public)父类(super class) Operation 的子类。每个子类都将具有该操作所需的相应设备的强制对象成员。

用 SQL 对此建模的方法是 Class Table Inheritance .创建一个普通的 super 表:

CREATE TABLE Operation (
  operation_id   SERIAL PRIMARY KEY,
  operation_type CHAR(1) NOT NULL,
  UNIQUE KEY (operation_id, operation_type),
  FOREIGN KEY (operation_type) REFERENCES OperationTypes(operation_type)
);

然后对于每个操作类型，定义一个子表，其中包含每个所需设备类型的列。例如，OperationFoo 对每个 equipA 和 equipB 都有一列。由于它们都是必需的，因此列为 NOT NULL。通过为设备创建类表继承 super 表，将它们限制为正确的类型。

CREATE TABLE OperationFoo (
  operation_id   INT PRIMARY KEY,
  operation_type CHAR(1) NOT NULL CHECK (operation_type = 'F'),
  equipA         INT NOT NULL,
  equipB         INT NOT NULL,
  FOREIGN KEY (operation_id, operation_type) 
      REFERENCES Operations(operation_d, operation_type),
  FOREIGN KEY (equipA) REFERENCES EquipmentA(equip_id),
  FOREIGN KEY (equipB) REFERENCES EquipmentB(equip_id)
);

表 OperationBar 不需要任何设备，因此它没有设备列:

CREATE TABLE OperationBar (
  operation_id   INT PRIMARY KEY,
  operation_type CHAR(1) NOT NULL CHECK (operation_type = 'B'),
  FOREIGN KEY (operation_id, operation_type) 
      REFERENCES Operations(operation_d, operation_type)
);

表OperationBaz有一个必需的设备equipA，那么equipB和equipC中至少有一个必须是NOT NULL。为此使用 CHECK 约束:

CREATE TABLE OperationBaz (
  operation_id   INT PRIMARY KEY,
  operation_type CHAR(1) NOT NULL CHECK (operation_type = 'Z'),
  equipA         INT NOT NULL,
  equipB         INT,
  equipC         INT,
  FOREIGN KEY (operation_id, operation_type) 
      REFERENCES Operations(operation_d, operation_type)
  FOREIGN KEY (equipA) REFERENCES EquipmentA(equip_id),
  FOREIGN KEY (equipB) REFERENCES EquipmentB(equip_id),
  FOREIGN KEY (equipC) REFERENCES EquipmentC(equip_id),
  CHECK (COALESCE(equipB, equipC) IS NOT NULL)
);

同样在表 OperationQuux 中，您可以使用 CHECK 约束来确保每对中至少有一个设备资源是非空的:

CREATE TABLE OperationQuux (
  operation_id   INT PRIMARY KEY,
  operation_type CHAR(1) NOT NULL CHECK (operation_type = 'Q'),
  equipA         INT,
  equipB         INT,
  equipC         INT,
  equipD         INT,
  FOREIGN KEY (operation_id, operation_type) 
      REFERENCES Operations(operation_d, operation_type),
  FOREIGN KEY (equipA) REFERENCES EquipmentA(equip_id),
  FOREIGN KEY (equipB) REFERENCES EquipmentB(equip_id),
  FOREIGN KEY (equipC) REFERENCES EquipmentC(equip_id),
  FOREIGN KEY (equipD) REFERENCES EquipmentD(equip_id),
  CHECK (COALESCE(equipA, equipB) IS NOT NULL AND COALESCE(equipC, equipD) IS NOT NULL)
);

这看起来工作量很大。但是你问的是如何在 SQL 中做到这一点。在 SQL 中执行此操作的最佳方法是使用声明性约束来为您的业务规则建模。很明显，这就要求你每次创建一个新的操作类型时，都需要创建一个新的子表。当操作和业务规则从不(或几乎从不)改变时，这是最好的。但这可能不符合您的项目要求。大多数人会说，“但我需要一个不需要更改架构的解决方案。”

大多数开发人员可能不执行类表继承。更常见的是，他们只是像其他人提到的那样使用一对多表结构，并仅在应用程序代码中实现业务规则。也就是说，您的应用程序包含仅插入适合每种操作类型的设备的代码。

依赖应用程序逻辑的问题是它可能包含错误，并且可能会插入不满足业务规则的数据。类表继承的优势在于，通过精心设计的约束，RDBMS 始终如一地强制执行数据完整性。您确信数据库确实不能存储不正确的数据。

但这也可能会受到限制，例如，如果您的业务规则发生变化并且您需要调整数据。在这种情况下，常见的解决方案是编写一个脚本来转储所有数据，更改您的模式，然后以现在允许的形式重新加载数据 (Extract, Transform, and Load = ETL)。

因此您必须决定:您是想在应用层还是在数据库架构层编写代码？使用任何一种策略都有合理的理由，但无论哪种方式都会很复杂。

---

关于您的评论:您似乎在谈论将表达式作为字符串存储在数据字段中。我建议反对那样做。数据库是用来存储数据的，而不是代码。您可以在约束或触发器中执行一些有限的逻辑，但代码属于您的应用程序。

如果您有太多操作无法在单独的表中建模，请在应用程序代码中对其进行建模。在数据列中存储表达式并期望 SQL 使用它们来评估查询就像围绕大量使用 eval() 设计应用程序一样。