Designing a database with circular references for the-one-of-many case

Question

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?

Answer 1

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.

Answer 2

You have to be more specific about the semantic meaning of your entities. Don't use users or user_ids, because the word "user" does not express the kind of usage. If you have some specific kind of usage in mind use that instead of just user. For example if it is an ownership, call it an ownership.

This are the entities with their primary keys. We have images, persons and ownerships.

create table image (
  id integer,
  data text,
  primary key (id));

create table person (
  id integer,
  name text,
  profile_image integer,
  primary key (id));

create table ownership (
  image_id integer,
  person_id integer,
  primary key (image_id, person_id));

This are the relations. An ownership is a n:m relation between images and persons. The profile image is a 1:n relation between persons and ownerships.

alter table ownership
  add foreign key (image_id) references image (id);

alter table ownership
  add foreign key (person_id) references person (id);

alter table person
  add foreign key (id, profile_image) references ownership (person_id, image_id);

Add some images.

insert into image values (11, 'image 1');
insert into image values (12, 'image 2');
insert into image values (13, 'image 3');

And some persons.

insert into person values (21, 'alice');
insert into person values (22, 'bob');

And define, which person owns which image.

insert into ownership values (11, 21);
insert into ownership values (12, 22);

And now Bob wants to set his profile image:

-- update person set profile_image = 11 where id = 22;
update person set profile_image = 12 where id = 22;
-- update person set profile_image = 13 where id = 22;

Only the second update works fine. The first and the third fail, because of the foreign key constrain, which enforces, that only those images are used as profile image, which are owned by the specific person. The first update fails, because image 11 belongs to Alice and not Bob. And in the third update fails, because image 13 has no owner.

Answer 3

In the end it is an easy thing. The first case (the circular reference case) breaks separation of code in an application. Because being profile image is a feature of relationship between user and profile. It is not a separate relationship and it should not be modelled as such because doing so is hard overkill. If you do so, you get the uniqueness but at crazy price. The code is going more tightly-coupled, possibly slower and slightly duplicated at some points.

The uniqueness of the non-circular solution can be handled by partial unique index (user, profile=True). This index also makes things fast. In fact a simple lookup of user's profile image by (user_id, True) is going to be as fast and maybe even faster (cause the index is smaller) than lookup by image.id.

As a final argument, imagine that you want fetch all profile images. In the first case, you necessarily need to join with users. In the second case, you don't need to.