如何在循环参考数据库结构中保持数据完整性？

Question

我看过一些DataBase规范化和设计方法，以正确地构造数据，并读到循环引用不是最佳的，因为它危及数据的完整性。我有一个数据结构，其中我无法弄清楚如何消除循环引用或确保不引入错误引用。

数据库要求：

1. 每种水果都需要水果特有的属性。
2. 水果递送需要包含水果类型的水果。
3. 水果的交付需要指定所有应用属性。
4. 水果递送不能引用它不包含的水果类型的属性。

我想出的实现这一目标的设计如下：

​

这方面的问题是，可以在传递包含FruitTypeID=1的地方输入潜在的数据，但将其链接到FruitTypeID=2的属性。

解决这一问题的一个潜在方法是创建一个触发器，该触发器强制一个属性只能链接到同一个FruitType的传递。但我不喜欢它，因为它看起来脆弱和不太可靠。另一种方法是在某种程度上消除这些数据的循环，但我想不出一种仍然能满足需求的方法。

因此，问题是，我如何确保这些数据的数据完整性，并仍然符合规范？

Answer 1

你没有循环引用。许多一对一的协会是朝着单向的方向发展的.你所拥有的是两条需要保持一致的联系路径。

您可以通过将函数依赖项AttributeID -> FruitTypeID和DeliveryID -> FruitTypeID去定向为Attribute_Fruit，然后创建两个复合FK约束：

CREATE TABLE Attribute (
    ID INT NOT NULL,
    FruitTypeID INT NOT NULL,
    Name VARCHAR(255) NOT NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY (FruitTypeID) REFERENCES FruitType (ID),
    UNIQUE KEY (ID, FruitTypeID)
);

CREATE TABLE Delivery (
    ID INT NOT NULL,
    FruitTypeID INT NOT NULL,
    Amount INT NOT NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY (FruitTypeID) REFERENCES FruitType (ID),
    UNIQUE KEY (ID, FruitTypeID)
);

我向Attribute和Delivery添加了复合唯一键，以支持Attribute_Fruit表上的复合FK约束：

CREATE TABLE Attribute_Fruit (
    AttributeID INT NOT NULL,
    DeliveryID INT NOT NULL,
    FruitTypeID INT NOT NULL,
    PRIMARY KEY (AttributeID, DeliveryID),
    FOREIGN KEY (AttributeID, FruitTypeID) REFERENCES Attribute (ID, FruitTypeID),
    FOREIGN KEY (DeliveryID, FruitTypeID) REFERENCES Delivery (ID, FruitTypeID)
);

重叠复合FK约束将强制执行您要寻找的一致性。FruitTypeID在Attribute_Fruit中逻辑上是多余的，但是完整性是必需的，并且不存在由于FK约束而导致的更新异常的风险。

正如您所提到的，触发器是实现相同目标的另一种方法，并且正确实现，它们是可靠且不脆弱的。但是，我认为上面的方法更简单。

Answer 2

你需要做的是认识到“水果属性”不是交付的属性，而是水果的属性。因此，您应该将交付建模为与交付相关的多到多的交付，而无需提及水果属性。

更好的是:详细说明这些矩形的含义。也就是说，详细说明这些表中的行是如何描述真实世界中发生的事情的。这样做，你就会发现自己在思考这个问题。您会发现自己在适用于您的业务案例的所有方面都了解这个问题。解决方案会自然而然地出现。而且很可能涉及到比这里更多的矩形。你现在还没有这么做。至少你还没有向我们提供这方面的任何细节。任何试图做出回应的人都将被迫进行猜测，而这些猜测可能是无可救药的。就像我在第一段中所做的猜测一样(例如，我假设没有必要在对某些水果的某些属性应用更改之前完成的旧传递中保存哪些“属性”已经“传递”的记录)。