如何将子类型应用于SQL Server数据库? [英] How do I apply subtypes into an SQL Server database?

问题描述

我正在开发一个程序，您可以在其中注册投诉.投诉分为三种类型:internal(来自员工的错误)，external(来自另一家公司的错误)和supplier(供应商的错误).它们包含无法共享的不同数据.我目前有4个表格(投诉，员工，公司和供应商).这是表格的可视化效果:

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:

我对子类型有基本的了解，但我似乎无法将它们从ERD转换为实际的SQL Server数据库，或者至少在这种情况下.这大致是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):

投诉
投诉编号PK

Complaint
ComplaintId PK

员工
EmployeeId PK
员工姓名

Employee
EmployeeId PK
EmployeeName

公司
CompanyId PK
公司名称

Company
CompanyId PK
CompanyName

供应商
SupplierId PK
SupplierName

Supplier
SupplierId PK
SupplierName

注册投诉时，这3种类型中的任何一种都会导致错误，并且它们都存储不同的信息.在这种情况下，存储信息的最佳方法是什么?我曾考虑过在投诉表中放入两个鉴别符:ComplaintType和Id，这样我就可以指向要检查的表以及我需要的ID，但这不是很干净也不有效.

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.

请协助.

推荐答案

我强烈建议您不要使用"2个鉴别符"方法.根据ComplaintType字段，您将有效地拥有一个指向三个表之一的外键列.如果这样做，将绕过SQL Server提供的参照完整性检查以及外键附带的所有好处.在我上一份工作中，有一个名为EntityTypeIndexLabel的表，它是一个桥表"，将IndexLabel(基本上是元数据)附加到各种实体"，这些实体是许多不同的潜在表(文档，活页夹，工作流等).这简直太糟糕了.该表中的FK可以指向许多不同的表.孤立的记录可能会在任何地方弹出.必须实施额外的逻辑来确定要连接的表.一般而言，加入联接是一种痛苦.真是头疼.

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.

我认为您有两个选择:

-3列:EmployeeComplaintID，CompanyComplaintID，SupplierComplaintID. ComplaintID在所有表中都应该是唯一的(请在此处考虑GUID，而不要使用IDENTITY列).投诉中的每一行只会填充其中一个ID，其他两个将为NULL.然后，您只需在每个查询中对这些表进行LEFT OUTER JOIN即可获得所需的数据.

-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.

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

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

这篇关于如何将子类型应用于SQL Server数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！