如何将子类型应用于SQL Server数据库？

I am working on a program in which you can register complaints. There are three types of complaints: internal (errors from employees), external (errors from another company) and supplier (errors made by a supplier). They hold different data which cannot be shared. I currently have 4 tables (complaint, employee, company and supplier). Here's a visualisation of the tables:

我正在制定一个可以注册投诉的计划。有三种类型的投诉：内部（员工的错误），外部（来自其他公司的错误）和供应商（供应商的错误）。它们包含无法共享的不同数据。我目前有4张桌子（投诉，员工，公司和供应商）。这是表格的可视化：

I have a basic understanding of subtypes but I cannot seem to translate them from an ERD into an actual SQL Server database, or at least in this scenario. This is roughly how the 4 tables look (irrelevant attributes omitted):

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

Complaint
ComplaintId PK

投诉ComplaintId PK

Employee
EmployeeId PK
EmployeeName

Employee EmployeeId PK EmployeeName

Company
CompanyId PK
CompanyName

公司CompanyId PK CompanyName

Supplier
SupplierId PK
SupplierName

供应商SupplierId PK供应商名称

When registering a complaint, the error is made by either of the 3 types and they all store different information. What is the best way to store information in this case? I have thought of putting 2 discriminators in the Complaint-table: ComplaintType and Id so I can point to which table to check and what Id I need, but that isn't very clean nor efficient.

注册投诉时，错误由3种类型中的任何一种产生，它们都存储不同的信息。在这种情况下，存储信息的最佳方法是什么？我曾想过在投诉表中放置2个鉴别符：ComplaintType和Id所以我可以指出要检查的表和我需要的ID，但这不是很干净也不高效。

Please assist.

请协助。

5 个解决方案

#1

I highly recommend you DO NOT use the "2 discriminators" method. You will effectively have a foreign key column that points to one of three tables, depending on the ComplaintType field. If you do this you will be by-passing the referential integrity checks supplied by SQL Server and all of the benefits that come with foreign keys. At my previous job, there was a table called EntityTypeIndexLabel which was a "bridge table" that attached IndexLabels (basically metadata) to various "entities", which were many different potential tables (Document, Binder, Workflow, etc...). This was simply awful. The FK in this table could point to many different tables. Orphaned records could pop-up everywhere. Extra logic had to be implemented to determine which table to join on. Joins were a pain to write in general. It was all kinds of headache.

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

I think your two options are:

我认为你的两个选择是：

-3 columns in Complaint: EmployeeComplaintID, CompanyComplaintID, SupplierComplaintID. ComplaintIDs should be unique across all of the tables (think GUIDs here instead of IDENTITY columns). Each row in Complaint will have only one of these IDs populated, the other two will be NULL. Then you can simply LEFT OUTER JOIN on these tables in every query to get the data that you need.

投诉中的-3列：EmployeeComplaintID，CompanyComplaintID，SupplierComplaintID。 ComplaintID在所有表中应该是唯一的（想想这里的GUID而不是IDENTITY列）。 Complaint中的每一行只会填充其中一个ID，另外两行将为NULL。然后，您可以在每个查询中简单地对这些表进行LEFT OUTER JOIN以获取所需的数据。

-One giant table with all of the possible fields you need for each complaint type, setting unused fields of other complaint types to NULL.

- 一个包含每种投诉类型所需的所有可能字段的巨型表，将其他投诉类型的未使用字段设置为NULL。

#2

See a few really good resources on the topic:

在这个主题上看到一些非常好的资源：

Implementing table inheritance in SQL Server
在SQL Server中实现表继承
How to model inheritance in a database
如何在数据库中建模继承
How do you effectively model inheritance in a database?
你如何有效地模拟数据库中的继承？

There's basically three well-known approaches:

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

Table per Subclass
每个子类的表
Table per Hierarchy
每个层次结构的表
Table per Concrete Type
每种混凝土类型的表

Each has pros and cons, shines in some situation and sucks in others - study the resources and see which of the three suits your needs the best.

每个人都有利有弊，在某些情况下闪耀，在其他情况下很糟糕 - 研究资源，看看哪三个最适合你的需求。

#3

Is the main issue that you need some sort of "serial number" to uniquely identify a complaint regardless of which type? Basically, then, you need a table for each type of complaint (as you will have, I think), plus the master "Complaint" table with the ComplaintId. Each of the type-specific tables will have a foreign key to Complaint.ComplaintId. You may find it useful to have a "type" field in Complaint, but that isn't really required for the model.

主要问题是您需要某种“序列号”来唯一标识投诉，无论哪种类型？基本上，那么，你需要一个表格用于每种类型的投诉（我认为你会有），以及带有ComplaintId的主“投诉”表。每个特定于类型的表都有一个Complaint.ComplaintId的外键。您可能会发现在Complaint中有一个“类型”字段很有用，但这并不是模型真正需要的。

#4

You can have a complaintSubTypeID with a FK relationship to the PK of all three of your subtype tables- employee, company, and supplier.

您可以拥有与所有三个子类型表（员工，公司和供应商）的PK具有FK关系的complaintSubTypeID。

#5

In response to you're comment on the accepted answer:

回应你对接受的答案的评论：

Below is a way to have a check check to ensure only one of the three keys has data:

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

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

#1