Take the 2-minute tour ×
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's 100% free, no registration required.

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?

share|improve this question
4  
Are you sure the ModifiedBy isn't a reference to the database user that made the change, rather than the applications user (which would reside in the User table)? Either way, it doesn't really matter. I don't see why there's an actual problem? –  Phil May 6 '13 at 11:01
    
Which one will created first. As CreatedBy ANd Modifiedby is a must data. And user table should not have addressid as null.. Chicken-egg problem. As i solve this by creating a new table UserAddress which contains the reference for userid and addressid –  Shashi May 6 '13 at 11:07
2  
You could always used deferred constraints if your DBMS supports them. –  Colin 't Hart May 6 '13 at 11:08
    
NB The relational model supports doing two inserts or updates as one atomic operation, it's a real shame this isn't supported in SQL (as deferred constraints -- even though I suggested them -- are quite horrible). –  Colin 't Hart May 7 '13 at 16:50

2 Answers 2

up vote 5 down vote accepted

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

enter image description here


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.

share|improve this answer

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.

share|improve this answer
1  
Removing the constraints removes the cyclic dependency from the definition, but not the design. You could add an Events table to record the UserID and AddressID that last created or modified the address, but that just moves the dependency one step further away. Going the other way, if the User table had CreatedBy and ModifiedBy columns, the cyclic dependency would exist in one table. This is similar to an employee table with a supervisor column where the supervisor is also an employee. As Phil indicated - Not a problem. –  Leigh Riffel May 6 '13 at 17:32
    
@LeighRiffel I agree. But the Events table you propose does actually remove all cyclic dependencies. –  ypercube May 6 '13 at 18:18
    
@ypercube Indeed it does; not sure how I got that wire crossed. To be clear you probably shouldn't create an Events table even though it does remove the cyclic dependency. –  Leigh Riffel May 6 '13 at 18:26
    
Anyway, I don't think this answer addresses the problem. The question (I think) is about how to avoid the cyclic path at all, not how to create the FKs with the cyclic path in the first place. –  ypercube May 6 '13 at 19:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.