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-design
There 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?
Best Answer
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 byexactly one
User.It is possible that more than one
Address was created bythe same
User.Each
Address was created onexactly one
Date.It is possible that more than one
Address was created onthe same
Date.For each
Addressand
Date,that
Address was modified byat most one
User onthat
Date.For each
Userand
Date,that
User residesat most one
Address sincethat
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.