如何在子类型中实现引用完整性?

I have the following tables in a relational database:

我在关系数据库中有如下表:

[Sensor]
LocationId [PK / FK -> Location]
SensorNo [PK]

[AnalogSensor]
LocationId [PK/FK -> Sensor]
SensorNo [PK/FK -> Sensor]
UpperLimit
LowerLimit

[SwitchSensor]
LocationId [PK/FK -> Sensor]
SensorNo [PK/FK -> Sensor]
OnTimeLimit

[Reading]
LocationId [PK/FK -> Sensor]
SensorNo [PK/FK -> Sensor]
ReadingDtm [PK]

[ReadingSwitch]
LocationId [PK/FK -> Reading]
SensorNo [PK/FK -> Reading]
ReadingDtm [PK/FK -> Reading]
Switch

[ReadingValue]
LocationId [PK/FK -> Reading]
SensorNo [PK/FK -> Reading]
ReadingDtm [PK/FK -> Reading]
Value

[Alert]
LocationId [PK/FK -> Reading]
SensorNo [PK/FK -> Reading]
ReadingDtm [PK/FK -> Reading]

Basically, ReadingSwitch and ReadingValue are subtypes of Reading and SwitchSensor and AnalogSensor are subtypes of Sensor. A reading can either be a SwitchReading or ValueReading value - it cannot be both, and a Sensor can either be an AnalogSensor or a SwitchSensor.

基本上，ReadingSwitch和ReadingValue是读取和开关传感器的子类型，模拟传感器是传感器的子类型。一个读数可以是一个开关读数，也可以是一个有价值的值——它不能同时是两个，一个传感器可以是一个模拟传感器或者一个开关传感器。

The only way I've come across to do this so far is here.

到目前为止，我唯一的方法就是在这里。

There surely must be a nicer way to do this sort of thing.

肯定有更好的方法来做这类事情。

The only other way I can think of is to not have sub types but completely expand everything:

我唯一能想到的另一种方法是没有子类型，但完全扩展了所有内容:

[SwitchSensor]
LocationId [PK/FK -> Location]
SensorNo [PK]

[AnalogSensor]
LocationId [PK/FK -> Location]
SensorNo [PK]

[SwitchReading]
LocationId [PK/FK -> SwitchSensor]
SensorNo [PK/FK -> SwitchSensor]
ReadingDtm
Switch

[AnalogReading]
LocationId [PK/FK -> AnalogSensor]
SensorNo [PK/FK -> AnalogSensor]
ReadingDtm
Value

[AnalogReadingAlert]
LocationId [PK/FK -> AnalogReading]
SensorNo [PK/FK -> AnalogReading]
ReadingDtm [PK/FK -> AnalogReading]

[SwitchReadingAlert]
LocationId [PK/FK -> SwitchReading]
SensorNo [PK/FK -> SwitchReading]
ReadingDtm [PK/FK -> SwitchReading]

Which might not be so bad but I also have tables that reference the Alert table, so they too would have to be duplicated:

这可能不是很糟糕，但我也有一些表引用了警告表，所以它们也必须被复制:

[AnalogReadingAlertAcknowledgement]
...
[AnalogReadingAlertAction]
...
[SwitchReadingAlartAcknowledgement]
...
[SwitchReadingAlartAction]

etc.

等。

Does this problem make any sense to anyone??

这个问题对任何人都有意义吗?

2 个解决方案

#1

None of that is necessary, especially not doubling up the tables.

这些都不是必要的，尤其是不能把表翻倍。

Introduction

Since the Standard for Modelling Relational Databases (IDEF1X) has been in common use for over 25 years (at least in the high quality, high performance end of the market), I use that terminology. Darwen, despite the great work he has done to progress ¹ consistent with the great work he has done to suppress the Relation Model, he was unaware of IDEF1X until I brought it to his attention in 2009, and thus has a new terminology² for the Standard terminology that we have been using for decades. Further, the new terminology does not deal with all the cases, as IDEF1X does. Therefore I use the established Standard terminology, and avoid new terminology.

因为建模关系数据库的标准(IDEF1X)已经使用了超过25年(至少在高质量、高性能的市场终端)，我使用了这个术语。Darwen,尽管他所做的伟大的工作进展1符合他所做的伟大作品抑制模型的关系,他不知道IDEF1X直到2009年我带它到他的注意力,从而有一个新的terminology2标准术语,我们已经使用了几十年。此外，新术语不像IDEF1X那样处理所有的情况。因此，我使用已建立的标准术语，并避免使用新术语。

• even the concept of a "distributed key" fails to recognise the underlying ordinary PK::FK Relations, their iimplementation in SQL, and their power.

  即使是“分布式密钥”的概念也无法识别底层的普通PK::FK关系、SQL中的iimplementation和它们的能力。

• The Relational, and therefore IDEF1X, concept is Identifiers and Migration thereof.

  关系，因此IDEF1X，概念是标识符和迁移。

• Sure, the vendors are not exactly on the ball, and they have weird things such a "partial Indices" etc, which are completely unnecessary when the basics are understood. But famous academics coming up with incomplete new concepts when the concept was standardised and give full treatment 25 years ago ... that, is unexpected.

  当然，供应商并不完全是在球上，而且他们有一些奇怪的东西，诸如“部分指标”等等，这些都是完全没有必要的，当基础被理解的时候。但是，当这个概念在25年前被标准化并给予充分的治疗时，著名学者提出了不完整的新概念。,是意想不到的。

Caveat

IEC/ISO/ANSI SQL barely handles 5NF adequately, and it does not support Supertype-Subtype structures at all; there are no Declarative Constraints for this (and there should be).

IEC/ISO/ANSI SQL几乎无法处理5NF，而且它根本不支持超类型亚型结构;这个(应该有)没有声明性约束。

• Therefore , in order to enforce the full set of Rules expressed in the Data Model, both SuperType::Subtype and Subtype::Supertype, we have to fiddle a little with CHECK Constraints, etc (I avoid using Triggers for a number of reasons).
• 因此，为了执行数据模型中所表达的完整规则集，两个超级类型::Subtype和Subtype:: SuperType，我们需要稍微修改一下检查约束条件，等等(出于多种原因，我避免使用触发器)。

Relief

However, I take all that into account. In order for me to effectively provide a Data Modelling service on *, without having to preface that with a full discourse, I purposely provide models that can be implemented by capable people, using existing SQL and existing Constraints, to whatever extent they require. It is already simplified, and contains the common level of enforcement. If there is any question, just ask, and you shall receive.

然而，我把所有这些都考虑进去了。为了使我能够有效地提供*上的数据建模服务，而不必预先面对一个完整的讨论，我特意提供了一些模型，这些模型可以由有能力的人来实现，使用现有的SQL和现有的约束，到他们需要的任何程度。它已经被简化了，并且包含了普通的执行级别。如果有什么问题，只要问，你就会收到。

We can use both the example graphic in the linked document and your fully IDEF1X-compliant ▶Sensor Data Model◀

我们可以使用链接文档中的示例图像和你完全IDEF1X-compliant▶◀传感器数据模型

Readers who are not familiar with the Relational Modelling Standard may find ▶IDEF1X Notation◀ useful. Readers who think a database can be mapped to objects, classes, and subclasses are advised that reading further may cause injury. This is further than Fowler and Ambler have read.

读者不熟悉关系建模标准可能会发现▶IDEF1X符号◀有用。认为数据库可以映射到对象、类和子类的读者会被建议进一步阅读可能会导致伤害。这比福勒和安布勒所读的还要多。

Implementation of Referential Integrity for Supertype-Subtype

There are two types of Supertype-Subtype structures.

有两种类型的超类型子类型结构。

Exclusive Subtype

Exclusive means there can be only one Subtype row for each Supertype row. In IDEF1X terms, there should be a Discriminator column in the Supertype, which identifies the Supertype row and which Subtype row exists for it.

独占意味着每个超类型行只能有一个子类型行。在IDEF1X术语中，在超类型中应该有一个鉴别器列，它标识了超类型行，并为其存在子类型行。

• For more than two Subtypes, this is demanded, and I implement a Discriminator column.

  对于超过两个子类型，这是需要的，并且我实现了一个鉴别器列。

• For two Subtypes, since this is easily derived from existing data (eg. Sensor.IsSwitch is the Discriminator for Reading), I do not model an additional explicit Discriminator column for Reading. However, you are free to follow the Standard to the letter and implement a Discriminator.

  对于两个子类型，因为这很容易从现有的数据中派生出来(如。传感器。IsSwitch是用于读取的鉴别器)，我不为阅读建模一个额外的显式鉴别器列。但是，您可以*地遵循标准，并实现一个鉴别器。

I will take each aspect in detail.

我将详细讨论每一个方面。

1. The Discriminator column needs a CHECK Constraint to ensure it is within the range of values, eg: IN ("B", "C", "D"). IsSwitch is a BIT, which is 0 or 1, so that is already constrained.

   鉴别器列需要一个检查约束，以确保它在值范围内(例如:IN(“B”，“C”，“D”)。IsSwitch是位，它是0或1，所以它已经被约束了。

2. Since the PK of the Supertype defines its uniqueness, only one Supertype row will be allowed; no second Supertype row (and thus no second Subtype row) can be inserted.

   由于超级类型的PK定义了它的惟一性，所以只允许一个超类型行;没有第二个超类型行(因此没有第二个子类型行)可以插入。

   • Therefore it is overkill, completely redundant, an additional unnecessary Index, to implement an Index such as (PK, Discriminator) in the Supertype, as your link advises. The uniqueness is in the PK, and therefore the PK plus anything will be unique).

     因此，当您的链接建议时，在超类型中实现一个索引，例如(PK，甄别器)，它是多余的，完全多余的，一个额外的不必要的索引。唯一性在PK中，所以PK加任何东西都是唯一的。

   • IDEF1X does not require the Discriminator in the Subtype tables. In the Subtype, which is again constrained by the uniqueness of its PK, as per the model, if the Discriminator was implemented as a column in that table, every row in it will have the same value for the Discriminator (every Book will be "B"; every ReadingSwitch will be an IsSwitch). Therefore it is absurd to implement the Discriminator as a column in the Subtype. And again, completely redundant, an additional unnecessary Index, to implement an Index such as (PK, Discriminator) in the Subtype: the uniqueness is in the PK, and therefore the PK plus anything will be unique).

     IDEF1X不需要子类型表中的鉴别器。在子类型中，它又被它的PK的唯一性所限制，就像在模型中一样，如果鉴别器被实现为该表中的一列，那么它的每一行对鉴别器都具有相同的值(每一本书都是“B”;每个读开关都是一个IsSwitch。因此，将鉴别器作为子类型的列实现是荒谬的。再一次，完全冗余，一个额外的不必要的索引，在子类型中实现一个索引，例如(PK，鉴别器):唯一性在PK中，因此PK加任何东西都是唯一的。

   • The method identified in the link is a hamfisted and bloated (massive data duplication for no purpose) way of implementing Referential Integrity. There is probably a good reason the author has not seen that construct anywhere else. It is a basic failure to understand SQL and to use it as it is effectively. These "solutions" are typical of people who follow a dogma "SQL can't do ..." and thus are blind to what SQL can do. The horrors that result from Fowler and Ambler's blind "methods" are even worse.

     在链接中识别的方法是一个错误的、臃肿的(没有目的的大规模数据复制)来实现引用完整性。这可能是作者在其他地方没有看到的一个很好的原因。这是理解SQL并有效使用SQL的基本失败。这些“解决方案”是典型的遵循教条的“SQL不能做…”的人，因此看不到SQL能做什么。Fowler和Ambler的盲目“方法”带来的恐惧甚至更糟。

3. The Subtype PK is also the FK to the Supertype, that is all that is required, to ensure that the Subtype does not exist without a parent Supertype.

   子类型PK也是超类型的FK，这是所有必需的，以确保子类型不存在，而没有父类。

   • Therefore for any given PK, whichever Supertype-Subtype is inserted first will succeed; and whichever Supertype-Subtype is attempted after that, will fail. Therefore there is nothing to worry about in the Subtype table (a second Supertype row or a second Subtype row for the same PK is prevented).
     .
   • 因此，对于任何给定的PK，首先插入的超类型子类型会成功;在此之后，任何类型的子类型都将失败。因此，在子类型表中没有什么可担心的(为了防止相同的PK，第二个超类型行或第二个子类型行)。

4. The SQL CHECK Constraint is limited to checking the inserted row. We need to check the inserted row against other rows, either in the same table, or in another table. Therefore a User Defined Function is required.

   SQL检查约束仅限于检查插入的行。我们需要检查插入的行与其他行，或者在同一张表中，或者在另一个表中。因此，需要一个用户定义的函数。

   • Write a simple UDF that will check for existence of the PK and the Discriminator in the SuperType, and return 1 if EXISTS or 0 if NOT EXISTS. You will need one UDF per Supertype (not per Subtype).

     编写一个简单的UDF，它将检查PK的存在和超类型的鉴别器，如果存在或不存在，返回1。每个超类型需要一个UDF(不是每个子类型)。

   • In the Subtype, implement a CHECK Constraint that calls the UDF, using the PK (which is both the Supertype and the Subtype) and the Discriminator value.

     在子类型中，使用PK(即超类型和子类型)和鉴别器值来实现调用UDF的检查约束。

   • I have implemented this in scores of large, real world databases, on different SQL platforms. Here is the ▶User Defined Function Code◀, and the ▶DDL Code◀ for the objects it is based on.

     在不同的SQL平台上，我已经在许多大型的、真实的数据库中实现了这个功能。这是▶◀用户定义函数代码,和代码◀▶DDL对象是基于。

   • This particular syntax and code is tested on Sybase ASE 15.0.2 (they are very conservative about SQL Standards compliance).

     这个特殊的语法和代码在Sybase ASE 15.0.2中进行测试(它们对于SQL标准的遵从性非常保守)。

   • I am aware that the limitations on User Defined Functions are different for every SQL platform. However, this is the simplest of the simple, and AFAIK every platform allows this construct. (No idea what the Non-SQLs do.)

     我知道对于每个SQL平台，用户定义函数的限制是不同的。然而，这是最简单的，而且每个平台都允许这个构造。(不知道非sqls会做什么。)

   • yes, of course this clever little technique can be used implement any non-trivial data rule that you can draw in a Data Model. In particular, to overcome the limitations of SQL. Note my caution to avoid two-way Constraints (circular references).

     是的，当然这个聪明的小技巧可以被用来实现任何你可以在数据模型中绘制的非平凡的数据规则。特别是要克服SQL的局限性。注意避免双向约束(循环引用)。

5. Therefore the CHECK Constraint in the Subtype, ensures that the PK plus the correct Discriminator exists in Supertype. Which means that only that Subtype exists for the Supertype (the PK).

   因此，在子类型中检查约束，确保PK加上正确的鉴别器存在于超类中。这意味着只有该子类型存在于超级类型(PK)。

   • Any subsequent attempt to insert another Subtype (ie. break the Exclusive Rule) will fail because the PK+Discriminator does not exist in the Supertype.

     插入其他子类型的任何后续尝试。中断独占规则)将失败，因为PK+鉴别器不存在于超级类型中。

   • Any subsequen attempt to insert another row of the same Subtype is prevented by the uniqueness of its PK Constraint.

     由于其PK约束的唯一性，任何子序列试图插入另一行相同的子类型。

6. The only bit that is missing (not mentioned in the link) is the Rule "every Supertype must have at least one Subtype" is not enforced. This is easily covered in Transactional code (I do not advise Constraints going in two directions, or Triggers); use the right tool for the job.

   唯一缺少的部分(链接中没有提到)是“每个超类型必须至少有一个子类型”的规则没有执行。这在事务性代码中很容易涵盖(我不建议在两个方向或触发器中进行约束);使用合适的工具来做这份工作。

Non-exclusive Subtype

The Supertype (parent) can host more than one Subtype (child)

父类(父类)可以托管多个子类型(子类型)

1. There is no one Subtype to be identified.

   没有可以识别的子类型。

   • The Discriminator does not apply to Non-exclusive Subtypes.

     鉴别器不适用于非独占子类型。

   • The existence of a Subtype is identified by performing an existence check on the Subtype table, using the Supertype PK.

     通过使用超类型PK在子类型表上执行存在检查来确定子类型的存在。

2. Simply exclude the CHECK Constraint that calls the UDF above.

   简单地排除调用UDF的检查约束。

   • The PRIMARY KEY, FOREIGN KEY, and the usual Range CHECK Constraints, adequately support all requirements for Non-exclusive Subtypes.
   • 主键、外键和通常的范围检查约束，充分支持非独占子类型的所有需求。

Reference

For further detail; a diagrammatic overview including details; and the distinction between Subtypes and Optional Column tables, refer to this Subtype document.

为进一步细节;一个图表概述，包括细节;以及子类型和可选列表之间的区别，请参阅此子类型文档。

Note

1. I, too, was taken in by C J Date's and Hugh Darwen's constant references to "furthering" the Relational Model. Based consistent evidence, after many years of interaction, I have concluded that their work is in fact, a debasement of it. They have done nothing to further Dr E F Codd's seminal work, and everything to damage and suppress it.

   我也被cj Date和Hugh Darwen不断提到的“进一步”关系模型。基于一致的证据，经过多年的互动，我得出结论，他们的工作实际上是一种堕落。他们没有做任何事来进一步研究E·F·考德博士的开创性著作，以及所有损害和压制它的东西。

2. They have private definitions for Relational terms, which of course severely hinders any communication. They have new terminology for terms we have had since 1970, in order to appear that they have "invented" it. Typical of frauds and thieves.

   它们对关系术语有私有定义，这当然严重阻碍了任何通信。从1970年开始，他们就有了新的术语，以表明他们已经“发明”了它。典型的骗子和小偷。

#2

The second option is also fraught with issues - e.g. for Sensors (and assuming that SensorNo is a surrogate key), because you don't have a base table, the SensorNo surrogate is either not unique between the sub class tables, unless you use a klugey mechanism to issue keys across all the sub-class tables (or use a guid).

第二个选项也充满了问题——例如传感器(假设SensorNo代理键),因为你没有一个基本表,子之间的SensorNo代理是没有独特的类表,除非你使用一个klugey机制问题的钥匙在所有子类表(或者使用guid)。

This would be amplified if you had user interface which 'combines' different types of sensors, e.g. a list showing a union of Analog and Switch sensors.

如果你有用户界面，这将被放大，因为用户界面“结合”了不同类型的传感器，例如一个显示模拟和开关传感器结合的列表。

I would recommend that you stay with your first pattern, and then encapsulate the insertion and maintenance of these tables with well tested, transactional code.

我建议您使用您的第一个模式，然后将这些表的插入和维护封装在经过良好测试的事务性代码中。

e.g. Create Insertion Procedures for the various SubTables, which insert the respective base and subclass records under a unit of work. You can go further by then revoking INSERT privileges on ANY of the tables thus forcing insertion via the SPROC's.

为各种子表创建插入过程，在一个工作单元中插入相应的基和子类记录。您可以更进一步，然后在任何表上撤销插入特权，从而强制通过SPROC的插入。

You could also run a daily integrity report which checked that there were no violations of your inheritance structure.

您还可以运行一个每日完整性报告，该报告检查没有违反您的继承结构。