4

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?

  • Out of interest couldn't it be a many-to-many? As in why could one image not be the avatar of mutliple users? – James Nov 23 '13 at 22:02
  • @James: These are not avatars but rather photos that a user uploads to his profile. Avatar model probably would not have a link to User (if there is a predefined set of avatars in the system), just User would have link to his Avatar. So there would not be the cyclic-reference problem. – clime Nov 23 '13 at 22:10
  • There isn't a chicken-and-egg problem at all. You have profile_image as a nullable foreign key. When the user is created, it should be null until an avatar is chosen later, right? – Izkata Nov 24 '13 at 6:02
  • 1
    @clime If loading a dump the issue you're talking about, you're going to have to disable constraint checks anyway, or go through a much more complicated procedure to guarantee User is loaded before Images, to ensure the non-nullable FKey in Images has a target. I still don't see an issue. – Izkata Nov 24 '13 at 13:59
  • 1
    @clime Oh, and at least with mysqldump, disabling/reenabling foreign key constraints is built-in. – Izkata Nov 24 '13 at 14:01
4

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.

  • The solution you suggest is very similar to my second suggestion. It is just that the type of ProfileImage is explicit here. The advantage is that you can place the unique constraint on it but the uniqueness is solvable also in my second solution with partial unique indexes (they are not in mysql though). – clime Nov 24 '13 at 0:01
  • 1
    I have upvoted this solution because it basically solves all the problems but the price is that there is one more layer (so it's less efficient), which is why I wouldn't chose it. – clime Nov 24 '13 at 0:46
  • Pretty much what I was attempting to convey (albeit badly) with my many-to-many comment - additional association table – James Nov 24 '13 at 12:24
1

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.