设计问题:可过滤属性，SQL [英] Design question: Filterable attributes, SQL

问题描述

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

I have two tables in my database, Operation and Equipment. An operation requires zero or more attributes. However, there's some logic in how the attributes are attributed:

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

• Operation Foo requires equipment A and B
• Operation Bar requires no equipment
• Operation Baz requires equipment B and either C or D
• Operation Quux requires equipment (A or B) and (C or D)

用SQL表示这一点的最佳方法是什么?

我确定人们以前已经做过，但是我不知道从哪里开始.

I'm sure people have done this before, but I have no idea where to start.

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

(FWIW, my application is built with Python and Django.)

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

Update 1: There will be around a thousand Operation rows and about thirty Equipment rows. The information is coming in CSV form similar to the description above: Quux, (A & B) | (C & D)

更新2:的连词级别&析取不应该太深. Quux示例可能是最复杂的，尽管似乎有一个A | (D & E & F)情况.

Update 2: The level of conjunctions & disjunctions shouldn't be too deep. The Quux example is probably the most complicated, though there appears to be a A | (D & E & F) case.

推荐答案

考虑如何在OO设计中对操作进行建模:这些操作将是常见超类Operation的子类.每个子类将具有该操作所需的相应设备的强制对象成员.

Think about how you'd model the operations in OO design: the operations would be subclasss of a common superclass Operation. Each subclass would have mandatory object members for the respective equipment required by that operation.

使用SQL进行建模的方法是类表继承.创建一个通用的超级表:

The way to model this with SQL is Class Table Inheritance. Create a common super-table:

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.也可以通过为设备创建类表继承"超级表来将它们约束为正确的类型.

Then for each operation type, define a sub-table with a column for each required equipment type. For example, OperationFoo has a column for each of equipA and equipB. Since they are both required, the columns are NOT NULL. Constrain them to the correct types by creating a Class Table Inheritance super-table for equipment too.

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不需要任何设备，因此没有配备列:

Table OperationBar requires no equipment, so it has no equip columns:

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

表操作Baz具有一个必需的设备equipA，然后equipB和equipC中的至少一个必须是NOT NULL.为此使用CHECK约束:

Table OperationBaz has one required equipment equipA, and then at least one of equipB and equipC must be NOT NULL. Use a CHECK constraint for this:

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约束来确保每对中至少有一个设备资源为非空:

Likewise in table OperationQuux you can use a CHECK constraint to make sure at least one equipment resource of each pair is non-null:

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中执行此操作的最佳方法是使用声明性约束对业务规则进行建模.显然，这要求您每次创建新的操作类型时都创建一个新的子表.当运营和业务规则从未(或几乎没有)改变时，这是最好的方法.但这可能不符合您的项目要求.大多数人说:但是我需要一个不需要更改架构的解决方案."

This may seem like a lot of work. But you asked how to do it in SQL. The best way to do it in SQL is to use declarative constraints to model your business rules. Obviously, this requires that you create a new sub-table every time you create a new operation type. This is best when the operations and business rules never (or hardly ever) change. But this may not fit your project requirements. Most people say, "but I need a solution that doesn't require schema alterations."

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

Most developers probably don't do Class Table Inheritance. More commonly, they just use a one-to-many table structure like other people have mentioned, and implement the business rules solely in application code. That is, your application contains the code to insert only the equipment appropriate for each operation type.

依赖于应用程序逻辑的问题是，它可能包含错误，并且可能会插入不符合业务规则的数据.类表继承的优点是，在设计良好的约束条件下，RDBMS始终如一地强制执行数据完整性.您可以确保数据库从字面上不能存储错误的数据.

The problem with relying on the app logic is that it can contain bugs and might insert data the doesn't satisfy the business rules. The advantage of Class Table Inheritance is that with well-designed constraints, the RDBMS enforces data integrity consistently. You have assurance that the database literally can't store incorrect data.

但是，这也可能是限制，例如，如果您的业务规则发生变化并且您需要调整数据.在这种情况下，常见的解决方案是编写脚本以转储所有数据，更改架构，然后以现在允许的形式重新加载数据(

But this can also be limiting, for instance if your business rules change and you need to adjust the data. The common solution in this case is to write a script to dump all the data out, change your schema, and then reload the data in the form that is now allowed (Extract, Transform, and Load = ETL).

因此，您必须决定:是否要在应用程序层或数据库架构层中对此进行编码?使用这两种策略都是有正当理由的，但这两种方法都会很复杂.

So you have to decide: do you want to code this in the app layer, or the database schema layer? There are legitimate reasons to use either strategy, but it's going to be complex either way.

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

Re your comment: You seem to be talking about storing expressions as strings in data fields. I recommend against doing that. The database is for storing data, not code. You can do some limited logic in constraints or triggers, but code belongs in your application.

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

If you have too many operations to model in separate tables, then model it in application code. Storing expressions in data columns and expecting SQL to use them for evaluating queries would be like designing an application around heavy use of eval().

这篇关于设计问题:可过滤属性，SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！