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?

Answer 1

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.