What is the definition of bad circular dependency?

Question

I have been searching for while for a good resource, where the circular dependency is well explained, unfortunately nothing good found. Therefore I tried to understand exactly which kind of circular dependency I should avoid. The problem is I found some resources which explain in contradictory manners. Could someone explain exactly please which types of circular dependency we should avoid (and why)?

Taking these relationship as an example:

Source

This relationship is mentioned here to be bad (I do not understand why).

However, the same relationship is mentioned to be not a problem (and is described as NOT circular) here:

Models <--------------------------- SuperSets
   ^                                    ^
   |                                    |
   |                                    |
Tasks  <---------------------------- Sets

Source

Another example is this:

Source

I also do not understand why is this a circular relationship?

All of the previous relationships seem to me not curcular (the arrows direction is not going back to the same point). I think there is something wrong in my understanding for circular dependency term. Could some one explain that for me please in general, and particularly also maybe on the previous examples?

Damir Sudarevic · Answer 1 · 2017-12-18T21:45:47.413

There is nothing circular in any of these cases. In "circular reference" scenario you have "a chicken and an egg problem" -- can not insert a row into a table because it is always missing a reference to some other table, like in:

user {user_id, address_id, ...}
  PK {user_id}

FOREIGN KEY {address_id} REFERENCES address

--
--
address {address_id, user_id, ...}
     PK {address_id}

FOREIGN KEY {user_id} REFERENCES user

The only problem that the author of that article has is not understanding database design. The problem in his examples is simply lousy design stemming from insistence on single column PKs (IDs), and not understanding how business logic relates to DB constraints.

In the second example, his design assigns re-sellers commission based on the user and the product, instead on an actual purchase, which makes no sense business-wise. In the first example it is possible to assign a user to a task outside his project scope; again nothing to do with "circular", but not knowing how to implement constraints.

For the second example you may consider something like:

Product {ProductID}
     PK {ProductID}

Customer {CustomerID}
     PK {CustomerID}

Reseller {ResellerID}
     PK {ResellerID}

--
--
Purchase {PurchaseID, CustomerID, ProductID, ResellerID, ...}
      PK {PurchaseID}

FOREIGN KEY {CustomerID} REFERENCES Customer
FOREIGN KEY {ProductID}  REFERENCES Product
FOREIGN KEY {ResellerID} REFERENCES Reseller

And if you insist on the Commission table, say if not every purchase involves re-seller.

Product {ProductID}
     PK {ProductID}

Customer {CustomerID}
      PK {CustomerID}

Reseller {ResellerID}
      PK {ResellerID}

--
--
Purchase {PurchaseID, CustomerID, Product_ID, ...}
      PK {PurchaseID}

FOREIGN KEY {CustomerID} REFERENCES Customer
FOREIGN KEY {ProductID}  REFERENCES Product


--
--
Commission {PurchaseID, ResellerID, ...}
        PK {PurchaseID}

FOREIGN KEY {PurchaseID} REFERENCES Purchase
FOREIGN KEY {ResellerID} REFERENCES Reseller

And for the first example:

Project {ProjectID, ...}
     PK {ProjectID}

User {UserID, ...}
  PK {UserID}

TaskType {TypeID, ...}
      PK {TypeID}

ProjectRole {RoleID, ...}
         PK {RoleID}

--
--
Task {TaskID, ProjectID, TypeID, ...}
  PK {TaskID}
  AK {TaskID, ProjectID}

FOREIGN KEY {ProjectID} REFERENCES Project
FOREIGN KEY {TypeID}    REFERENCES TaskType

--
--
ProjectTeam {UserID, ProjectID, RoleID, ...}
         PK {UserID, ProjectID}

FOREIGN KEY {UserID}    REFERENCES User
FOREIGN KEY {ProjectID} REFERENCES Project
FOREIGN KEY {RoleID}    REFERENCES ProjectRole

--
--
ProjectAssignment {UserID, ProjectID, TaskID, ...}
               PK {UserID, ProjectID, TaskID}

FOREIGN KEY {UserID, ProjectID} REFERENCES ProjectTeam
FOREIGN KEY {TaskID, ProjectID} REFERENCES Task

If you prefer single-column PKs then you can ADD them to ProjectAssignment and ProjectTeam tables, but you must keep the existing ones as AKs (unique) and reference them in foreign keys where applicable.

Note PK = primary key
     AK = alternate key (unique)
     All attributes (columns) NOT NULL

What is the definition of bad circular dependency?

1 Answers1