How to design to avoid duplicate Foreign keys



  • I wasn't really sure how to title this question. I have three tables User, Photo and Album. A user has many Photo objects and many Album objects. However, after a User has added a given photo they may choose to put it in a single Album (a Photo does not have to have an album linked to it).

    How do I get around the redundant user column (Album.user and Photo.user) when a Photo is associated with an Album to avoid db anomalies?

    I can't get rid of the user column on Photo since I wouldn't be able to get to it if it is not linked to / is deleted from an Album. The same goes for Album. I have some experience with normalizing tables but I don't know how to avoid this without using db constraints.

    Here's a crude schema image:

    db schema



  • Make both user_id and album_id in photo nullable, but add a check constraint that makes sure that exactly one of them is set. If you cannot change both attributes in the same statement, you can use a deferred constraint trigger instead of the check constraint.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2