CODEWITHSUNDEEP
databasedatabase-designreferencedatabase-relations
Mercury
Mercury

Reputation: 732

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.

Database requirements:

  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:

enter image description here 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.

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?

Upvotes: 0

Views: 211

Answers (2)

Erwin Smout
Erwin Smout

Reputation: 18408

What you need to do is recognize that "fruit attributes" are not a property of deliveries but only of fruits. So you should model deliveries as associated many-to-many with deliveries and without any mention of fruit attributes.

Better still : spell out what these rectangles are supposed to mean. That is, spell out what kinds of fact the rows in these tables are stating about what happened in the real world. Do that, and you'll find yourself thinking about the problem. You'll find yourself understanding the problem in all of the aspects that apply to your business case. And the solution will come naturally. And most likely will involve many more rectangles than what you have here. You currently have not done this. At the very least you haven't provided us with any details about that. And anyone trying to respond will be forced into guesswork that might be hopelessly off. Like the guesswork I did myself in my first paragraph (where I ASSUMED for example that there's no need to keep a record of which "attributes" have been "delivered" in an old delivery that was done before some change was applied to some attribute of some fruit).

Upvotes: 1

reaanb
reaanb

Reputation: 10065

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.

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

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

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.

Upvotes: 3

Related Questions