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?
profile_image
as a nullable foreign key. When the user is created, it should be null until an avatar is chosen later, right?User
is loaded beforeImages
, to ensure the non-nullable FKey inImages
has a target. I still don't see an issue.mysqldump
, disabling/reenabling foreign key constraints is built-in.