Database Design – Designing a Database with Circular References for One-of-Many Case

database-designdesignrelational-database

Consider the following case: there are two models Image and User. Users have associated images and they can pick one of them as their profile image. There are essentially two ways of modeling this:

1)

User:
   id - primary key
   profile_image - nullable foreign key to Image

Image:
   id - primary key
   user - foreign key to User

2)

User:
   id - primary key

Image:
   id - primary key
   user - foreign key to User
   profile - boolean true/false

Both of these approaches have some problems.

In the first case:

  • well-known chicken-egg problem (e.g. foreign keys must be off when recovering from backup)
  • It can happen that profile image points outside of domain of images associated with user
  • There is a redundant dependency link (i.e. one more relation when compared to the first solution). That will have some impact on an application code
  • What if we later decide to have an ordering on images where the first one is the profile one?

The second case:

  • There is slight data redundancy (many times false)
  • true can occur more than once
  • slightly less intuitive (at least for me)

So my question is, which of these two solutions is better/cleaner?

Best Answer

For a simple project I would probably adopt the second solution. On the other hand, first solution may be slightly modified to avoid your mentioned drawbacks. You could model a profile image as a special case of an image, i.e. inheritance in OOP or is-a hierarchy in relational DB.

Therefore, leaving out the profile_image column and introducing table 

Profile image:
   image_id - foreign key to Image
   user_id - foreign key to User

with unique constraint on user_id and possibly dedicated primary key column + indexes on columns used for the desired use case.

Only problem may be the sorting, where you'd need keep some weights (a column in Image table) in consistence with Profile image table.

Related Topic