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:
User: id - primary key profile_image - nullable foreign key to Image Image: id - primary key user - foreign key to User
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?