CODEWITHSUNDEEP
sqldatabase-designcircular-dependency
willvv
willvv

Reputation: 8639

SQL: Avoid circular dependencies

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?

Upvotes: 6

Views: 12849

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115590

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.

Upvotes: 12

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112482

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.

Upvotes: 2

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

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.

Upvotes: 0

Related Questions