SQL: Avoid circular dependencies

Question

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?

So, a user may insert many pictures. A picture is inserted by exactly one user. And a user (may) have one profile picture (or none). Correct? — ypercubeᵀᴹ, Jan 21 '12 at 16:02
I wouldn't use a circular dependency unless there were absolutely no other way to do it. In 25+ years of database work, I've never seen a case where there weren't alternatives. One problem most people don't think about (until it's too late) is that circular dependencies break a lot of third-party tools, like CASE, diagramming, and reverse engineering tools. — Mike Sherrill 'Cat Recall', Jan 21 '12 at 18:43

ypercubeᵀᴹ · Accepted Answer · 2012-01-21T18:31:54.720

12

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.

edited Jan 21 '12 at 18:31

answered Jan 21 '12 at 16:07

ypercubeᵀᴹ

113,259
19
174
235

Thanks, actually the second one is the design I have now. And I could just add the ProfilePictures table but that won't solve the issue of the circular dependencies (i.e. I cannot delete a User without deleting the picture, and I cannot delete the picture without deleting the user, so it's basically the same as having two tables and allowing null for the profilepictureid in users) – willvv Jan 21 '12 at 16:46
My first approach has no circular dependencies. – ypercubeᵀᴹ Jan 21 '12 at 16:48
1

In the second system, if Picture(PictureID) is the primary key, the combination Picture(UserID, PictureID) cannot help but be unique too. You seem to be insisting that a user cannot have a profile picture that they did not insert - which may be the intention. – Jonathan Leffler Jan 21 '12 at 16:58
@JonathanLeffler: I insisted on this Unique Key, mainly because MySQL seems to not be happy when you reference a combination that doesn't have a Unique Key defined. It may work fine in other DBMS (where the Uniqueness is implied, when the Primary Key is just a part of the referenced combo). – ypercubeᵀᴹ Jan 21 '12 at 17:02
But you are right, I assumed that a user cannot have a profile picture that they did not insert. Perhaps this was not the intention of the OP. The references should be changed then. Nice spotting. – ypercubeᵀᴹ Jan 21 '12 at 17:04
@willvv: Can a user have a profile picture that someone else inserted? – ypercubeᵀᴹ Jan 21 '12 at 17:08

score 2 · Answer 2 · answered Jan 21 '12 at 17:55

2

If only a few pictures are stored per user, you could just have a flag in the pictures telling if a picture is the profile picture or not.

answered Jan 21 '12 at 17:55

Olivier Jacot-Descombes

104,806
13
138
188

1

the main issue with this approach is that a user might have more than one picture set as profile picture! (this could sometimes be avoided using conditional constraint or nulls) – JoeCodeFrog Mar 13 '18 at 17:18

score 0 · Answer 3 · answered Jan 21 '12 at 16:07

0

Users has UserID and ProfilePictureID

Pictures

Has a PictureID, a Picture and a UserID

Delete the user and their profile picture will/can be deleted by PictureID, and any other pictures by userid if desired.

Don't even consider this circular as such.

answered Jan 21 '12 at 16:07

Tony Hopkinson

20,172
3
31
39

1

What do you mean by "don't consider this circular"? The problem I have is that I cannot delete the user without deleting the picture and I cannot delete the picture without deleting the user. – willvv Jan 21 '12 at 16:48
Well if you'd have posted your schema when I answered I would have seen that. Not a design decision I'd have made, it causes problems you see. :) – Tony Hopkinson Jan 22 '12 at 00:05

SQL: Avoid circular dependencies

3 Answers3