Menu
I see that most people just hate having a circular dependency in the database design. And since the support for this is "tricky" in most database engines, I was wondering if there's a way around this design:
I have a users table and a pictures table
Every picture has an userId (the user who inserted it) Every user has a profile picture
I might just create a ProfilePictures table, but it would cause issues in some other places (like picture comments).
I'm aware that there are some other questions related to this issue, but they're more related to partent-child relationships, which is not the case here.
So, is it ok to use a circular dependency here? or if not, how would you avoid it?
622
There are a couple of options to get rid of circular dependencies. For a longer chain, A -> B -> C -> D -> A , if one of the references is removed (for instance, the D -> A reference), the cyclic reference pattern is broken, as well. For simpler patterns, such as A -> B -> A , refactoring may be necessary.
Circular Dependency : Here when we try to delete a record from TABLE A, it throws an error message as Column P & Column R of TABLE B are depending on Column Q of TABLE A.
A simple way to break the cycle is by telling Spring to initialize one of the beans lazily. So, instead of fully initializing the bean, it will create a proxy to inject it into the other bean. The injected bean will only be fully created when it's first needed.
A circular dependency occurs when two classes depend on each other. For example, class A needs class B, and class B also needs class A. Circular dependencies can arise in Nest between modules and between providers. While circular dependencies should be avoided where possible, you can't always do so.
Without circular references between tables:
User
------
userid NOT NULL
PRIMARY KEY (userid)
Picture
---------
pictureid NOT NULL
userid NOT NULL
PRIMARY KEY (pictureid)
UNIQUE KEY (userid, pictureid)
FOREIGN KEY (userid)
REFERENCES User(userid)
ProfilePicture
---------
userid NOT NULL
pictureid NOT NULL
PRIMARY KEY (userid)
FOREIGN KEY (userid, pictureid) --- if a user is allowed to use only a
REFERENCES Picture(userid, picture) --- picture of his own in his profile
FOREIGN KEY (pictureid) --- if a user is allowed to use any
REFERENCES Picture(picture) --- picture in his profile
The only difference with this design and your needs is that a user may not have a profile picture associated with him.
With circular references between tables:
User
------
userid NOT NULL
profilepictureid NULL --- Note the NULL here
PRIMARY KEY (userid)
FOREIGN KEY (userid, profilepictureid) --- if a user is allowed to use only a
REFERENCES Picture(userid, pictureid) --- picture of his own in his profile
FOREIGN KEY (profilepictureid) --- if a user is allowed to use any
REFERENCES Picture(pictureid) --- picture in his profile
Picture
---------
pictureid NOT NULL
userid NOT NULL
PRIMARY KEY (pictureid)
UNIQUE KEY (userid, pictureid)
FOREIGN KEY (userid)
REFERENCES User(userid)
The profilepictureid can be set to NOT NULL but then you have to deal with the chicken-and-egg problem when you want to insert into the two tables. This can be solved - in some DBMS, like PostgreSQL and Oracle - using deferred constraints.
116
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
