如何在SQL Server数据库中应用子类型？

Question

我正在开发一个程序，用于登记投诉。有三种类型的投诉：internal（员工错误）、external（另一家公司的错误）和supplier（供应商的错误）。它们包含不同的数据，不能共享。当前有4张表（complaint、employee、company和supplier）。以下是这些表的可视化：

我对子类型有基本的了解，但我似乎无法将它们从ERD翻译成实际的SQL Server数据库，至少在这种情况下是这样。这大致是这4个表的样子（省略了不相关的属性）：

Complaint
ComplaintId PK

Employee
EmployeeId PK
EmployeeName

Company
CompanyId PK
CompanyName

Supplier
SupplierId PK
SupplierName

当登记投诉时，错误由其中的3种类型之一造成，它们都存储不同的信息。在这种情况下，存储信息的最佳方法是什么？我考虑过在Complaint-table中放置2个鉴别器：ComplaintType和Id，以便可以指向要检查的表和需要的ID，但这并不太清晰也不高效。

请协助解答。

- Fusyion

Answer 1

以下是关于该主题的一些非常好的资源：

• 在SQL Server中实现表继承
• 如何在数据库中建模继承关系
• 在数据库中如何有效地建模继承关系？

基本上有三种众所周知的方法：

• 子类表格法
• 层次结构表格法
• 具体类型表格法

每种方法都有其优缺点，在某些情况下表现优异，在其他情况下则比较差劲 - 请研究这些资源，并查看哪种方法最适合您的需求。

- marc_s

Answer 2

我强烈建议您不要使用“2个鉴别器”的方法。这样，您将有效地拥有一个外键列，该列指向三个不同的表之一，具体取决于ComplaintType字段。如果您这样做，将会绕过SQL Server提供的参照完整性检查以及所有与外键相关的好处。在我的上一份工作中，有一个名为EntityTypeIndexLabel的表，它是一个“桥接表”，将IndexLabels（基本元数据）附加到各种“实体”上，这些实体是许多不同的潜在表（Document、Binder、Workflow等）。这简直太糟糕了。这个表中的FK可以指向许多不同的表。孤立的记录可能随时出现。必须实现额外的逻辑来确定要连接的表。总的来说，连接是很麻烦的。这是一种头疼的问题。

我认为您有两个选择：

- Complaint中的3列：EmployeeComplaintID、CompanyComplaintID、SupplierComplaintID。ComplaintIDs应在所有表中都是唯一的（请考虑GUID而不是IDENTITY列）。每行Complaint只会填充其中一个ID，其他两个将为空。然后，您可以在每个查询中简单地LEFT OUTER JOIN这些表，以获取所需的数据。 - 一个包含所有投诉类型所需的所有可能字段的巨大表，将其他投诉类型的未使用字段设置为NULL。

- Mario

---

谢谢您的建议，我一定会远离2个鉴别器的方法。我也尝试过3列方法，但还没有使用任何约束条件。是否可以强制至少一个字段不为空？如果可以，您会如何实现？ - Fusyion

我选择接受你的答案作为“答案”，因为它很有信息量，而且我也选择了你的第一个选项来使事情正常运转。 - Fusyion

@Mario 我不认为 ComplaintType 列和三个单独的表会有问题。每个子表都包含对 Parent 表的外键引用。请参见此 fiddle：http://sqlfiddle.com/#!3/6118b6/2 唯一的问题是可能会引用错误的 Parent，即不正确类型的 parent。但这可以通过引入更多限制和扩展外键来解决。请参见此 fiddle：http://sqlfiddle.com/#!3/58cd0/1，但由于 SQL Server 浪费空间存储空列，所以我会选择一个大表格存储所有类型并在不适用的列中存储空值。 - Anderson

Answer 3

您需要的主要问题是需要一种“序列号”来唯一标识投诉，而不管其类型是什么吗？基本上，您需要为每种投诉类型（我认为您会有）创建一个表，以及带有ComplaintId的主“投诉”表。每个特定类型的表都将具有对Complaint.ComplaintId的外键。在模型中，您可能会发现将“类型”字段添加到投诉中很有用，但这并不是必需的。

- Andrew

---

我考虑过为每种投诉类型单独创建表格，但是每个表格中有太多冗余属性了。 - Fusyion

然后将这些属性放入主要的“投诉”表中，仅在每个更具体的表中放入唯一的元素。 - Andrew

Answer 4

针对您在已接受答案中的评论：

以下是一种检查方法，以确保仅有三个键中的一个具有数据：

alter table complaint_master 
    add constraint loc_attribute_has_one_value 
    check ( 
        (case when complaint_employee is null then 0 else 1 end) + 
        (case when complaint_supplier is null then 0 else 1 end) + 
        (case when complaint_external is null then 0 else 1 end)  = 1 
    ); 

- joefromct

Answer 5

你可以在complaintSubTypeID中建立一个外键关系，将其与三个子类型表-员工、公司和供应商的主键相关联。

- Beth

---

我已经尝试过这个，但是您在投诉表中输入的ID不是必须要与所有三个表格中的ID匹配吗？ - Fusyion

不，三者之一。假设键是互斥的。 - Beth

那我一定做错了什么。我在我的投诉表中创建了一个“SubTypeId”，并创建了与所有三个表的主键的关系。除了在所有三个表中都相同的那个Id之外，我无法输入任何其他Id。 - Fusyion

您应该能够在主表中输入pk1，然后在其中一个子表中输入pk1。然后在主表中输入pk2，在其中一个子表中输入pk2。 - Beth