How to design this database to avoid cyclic dependency?

Question

There are two tables:

1. User
2. Address

User contains a reference to Address.

Address contains the columns CreatedBy and ModifiedBy, which is reference to User.

How do I design this database to avoid a cyclic dependency?

Answer 1

Instead of searching for tips and tricks (deferred constraints included) I would suggest that you simply design your way out of this "reference lock" -- so try something like this:

---

Facts

• User(UserID) exists.
• Address(AddressID) was created by User(UserID).
• Address(AddressID) was created on Date(DateCreated).
• Address(AddressID) was last modified by User(UserID) on Date(ModifiedOn).
• User(UserID) resides at Address(AddressID) since Date(ValidFrom).

---

Constraints

• Each Address was created by exactly one User. It is possible that more than one Address was created by the same User.

• Each Address was created on exactly one Date. It is possible that more than one Address was created on the same Date.

• For each Address and Date, that Address was modified by at most one User on that Date.

• For each User and Date, that User resides at most one Address since that Date.

---

Logical

---

As far as mandatory address is concerned, verify that on the application layer and wrap the loading statements into a transaction -- that way you'll get all or nothing.

Answer 2

You don't have a choice but to create the cyclic dependency in 2 operations as below because one table does not exist when you create the first one.

CREATE TABLE A (A_ID INT PRIMARY KEY, B_FK INT);
CREATE TABLE B (B_ID INT PRIMARY KEY, A_FK INT REFERENCES A(A_ID));

ALTER TABLE A ADD B_FK INT;

If you wish to avoid cyclic dependency. Then you need to remove one REFERENCES constraint or you can add a DELETE and UPDATE CASCADE reference in one way. You could also implement a TRIGGER if your logic is somewhat complex.