There are two tables:
- User
- 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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityThere are two tables:
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?
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
(UserID)
exists.(AddressID)
was created by User(UserID)
.(AddressID)
was created on Date(DateCreated)
.(AddressID)
was last modified by User(UserID)
on Date(ModifiedOn)
.(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.
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.