如何在循环引用数据库结构中保持数据完整性？ [英] How to preserve data integrity in circular reference database structure?

问题描述

我已经研究了一些数据库规范化和设计方法以正确地构造数据，并且阅读到循环引用并不是最佳选择，因为它危害了数据完整性。我有一个数据结构，无法确定如何消除循环引用或确保没有引入错误的引用。

I have looked at some DataBase normalisation and design methods to properly structure data and have read that circular referencing is not optimal as it jeopardises data integrity. I have a data structure in which I am unable to figure out how to eliminate a circular reference or to ensure that no faulty references are introduced.

数据库要求：

1. 每种水果类型都需要特定的水果属性


2. 交付水果所需的水果类型也需要
   
3. 水果交付需要指定所有应用属性


4. 水果交付不能引用其不包含的水果类型的属性

1. Each type of fruit requires fruit specific attributes
2. A fruit delivery requires a fruit type which it contains
3. A delivery of fruit needs to specify all applying attributes
4. A fruit delivery can not reference attributes of fruit types that it does not contains

我为实现此目的而设计的设计如下：

The design I came up with to accomplish this is the following:

这个问题是，可能在交货包含FruitTypeID = 1但将其链接到FruitTypeID = 2的属性的地方输入了可能的数据。

The problem with this is that potentially data could be entered where a Delivery contains FruitTypeID=1 but it is linked to a Attribute of FruitTypeID=2.

一种解决t的潜在方法他的任务是创建一个触发器，强制将一个属性只能链接到同一FruitType的Delivery。但是我不喜欢它，因为它看起来很脆弱而且也不是很可靠。另一种方法是以某种方式消除此数据的圈，但我无法找到一种方法仍可以满足要求。

One potential way of solving this is to create a trigger that enforces that a Attribute can only be linked to a Delivery of the same FruitType. But I don't like it as it seems fragile and not very reliable. Another way would be to somehow eliminate the circle for this data, but I can not figure out a way to do so that still fits the requirements.

所以问题是我可以确保该数据的数据完整性并且仍然符合规范吗？

So the question is how would I ensure data integrity for this data and still fit the specifications?

推荐答案

您没有循环引用。多对一关联指向单方向。您所拥有的是两个关联路径，需要保持一致。

You don't have circular references. Many-to-one associations are directed in the direction of the one-side. What you have is two paths of association that need to be kept consistent.

您可以通过对功能依赖性 AttributeID->进行规范化来实现此目的。 FruitTypeID 和 DeliveryID-> FruitTypeID 放入 Attribute_Fruit ，然后创建两个复合FK约束：

You can do it by denormalizing the functional dependencies AttributeID -> FruitTypeID and DeliveryID -> FruitTypeID into Attribute_Fruit and then creating two composite FK constraints:

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 中添加了复合唯一键， 交付以支持 Attribute_Fruit 表上的复合FK约束：

I added composite unique keys to Attribute and Delivery to support composite FK constraints on the Attribute_Fruit table:

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约束，没有更新异常的风险。

Overlapping composite FK constraints will enforce the consistency you're looking for. FruitTypeID is logically redundant in Attribute_Fruit but required for integrity, and there's no risk of update anomalies due to the FK constraints.

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

As you mentioned, triggers are an alternative way to achieve the same, and implemented correctly, they're reliable and not fragile. However, I consider the approach above to be simpler.

这篇关于如何在循环引用数据库结构中保持数据完整性？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！