How to design this database to avoid cyclic dependency?

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?

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.

StackExchange Database Administrators Q#41574, answer score: 9