MySQL relationship loop

Question

Got a small relational problem with a project I'm working on, I've tried googling the answer, but not sure what terminology to use to find my answer Lol

The problem to be solved:

A user can have many photos, and one of these photos can be a profile photo, but only one.

So for example:

CREATE TABLE user (
    ID Int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username varchar(25) NOT NULL
);
CREATE TABLE photo (
    ID Int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_ID Int NOT NULL,
    imgpath varchar(260) NOT NULL,
    FOREIGN KEY (user_ID) REFERENCES user (ID) ON UPDATE CASCADE
);
CREATE table userprofphoto (
    user_ID Int NOT NULL,
    photo_ID Int NOT NULL,
    FOREIGN KEY (photo_ID) REFERENCES photo (ID) ON UPDATE CASCADE,
    FOREIGN KEY (user_ID) REFERENCES user (ID) ON UPDATE CASCADE
);

So a query to get the users profile photo would be:

SELECT imgpath FROM photo WHERE photo_ID=(SELECT photo_ID FROM userprofphoto WHERE user_ID=:userid LIMIT 1)

This works, but it creates a relational loop. Should/Could I fix this?

I did think about removing the user_ID from the userprofphoto table, but then this would increase the query length:

SELECT imgpath FROM photo
LEFT JOIN userprofphoto ON userprofphoto.photo_ID=photo.ID
WHERE photo.user_ID=:user_id

Any help appreciated :D

It does ... Cos userprofphoto has FK user.ID and FK photo.ID, and photo has FK user.ID — Just Lucky Really, Feb 14 '14 at 14:12
That's 2 paths (from `userprofphoto` to `user`), not a loop. — ypercubeᵀᴹ, Feb 14 '14 at 14:13
I think your overall design is okay. I don't see any "LOOP" there. Also, you said (not exact words, paraphrase) that each user can only have 1 profile photo. So I suggest under userprofphoto, make user_ID unique. — Craftein, Feb 14 '14 at 14:14
I'm pretty sure there is a loop ... Starting to doubt myself a little now, which I don't like Lol :s ... There is a path from photo -> user, a path from userprofphoto -> photo and userprofphoto -> user ... So it's like a little love triangle — Just Lucky Really, Feb 14 '14 at 14:28
@user3139907 It's a loop if you think of the constraints as non-directional. If you try though to start from (any) table and move through the `->` paths, you can't return to where you started. — ypercubeᵀᴹ, Feb 14 '14 at 14:30

score 1 · Answer 1 · answered Feb 14 '14 at 14:16

Your first query does not make sense because imgpath is only in photos in your design. (By the way, I generally prefer to give tables plural names rather than singular. It helps to avoid conflicts with reserved words.)

One problem with your design is that the table userprofphoto has no constraint on it. So a user can have multiple profile photos. This is easily fixed by adding a unique constraint or unique index on the field user_ID in the table.

Other than this minor tweak, your design is fine. There are two other approaches.

One is to have a flag in the photo table that specifies that a photo is used for the profile. This is very hard to maintain in MySQL. Other databases have functionality that makes this easier.

However, the best solution may be to eliminate the userprofphoto table entirely and just add a column to user. This automatically enforces the constraint of there only being one profile photo. Of course, if you later decide to remove this limitation, then you need to reintroduce the userprofphoto table.

You might also want a constraint that the user actually owns the profile photo. That requires a trigger to implement in MySQL. You can probably handle this in the logic that adds and modifies the profile photo.

With this structure, your query would be:

SELECT imgpath
FROM users u join
     photos p
     ON u.profile_photo_ID = p.photo_ID;
WHERE u.user_ID = :user_id;

Yep ... Noticed my wrong query Lol ... changed it :D Also, cheers for the unique index idea — Just Lucky Really, Feb 14 '14 at 14:23
@Gordon if we add a `profile_photo_id` column to `user`, we'll then have a loop in the foreign key references. And since MySQL has not implemented deferred constraints, that would be really hard to manage. — ypercubeᵀᴹ, Feb 14 '14 at 14:27
@ypercube . . . You can add it afterwards with a separate `alter table` statement (http://www.sqlfiddle.com/#!2/e091d). — Gordon Linoff, Feb 14 '14 at 15:53
Yeah, you can do that. The real problems will come when you try to insert rows. — ypercubeᵀᴹ, Feb 14 '14 at 15:57

ypercubeᵀᴹ · Answer 2 · 2014-02-14T14:25:16.857

To enforce the requirements you mention, I would use the following design:

add a unique (or primary key) constraint on the profile table so every user has only one profile photo
change the two foreign key constraints from the profile table to one. With your current design, you could end with a profile photo of a user that is attached to a different user.

(I'd also not use the ID as name but user_ID, photo_ID, etc everywhere):

CREATE TABLE user (
    user_ID Int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username varchar(25) NOT NULL
);

CREATE TABLE photo (
    photo_ID Int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_ID Int NOT NULL,
    imgpath varchar(260) NOT NULL,
    UNIQUE (user_ID, photo_ID),          -- this is is needed for the FK below
    FOREIGN KEY (user_ID) 
      REFERENCES user (user_ID) 
      ON UPDATE CASCADE
);

CREATE table userprofphoto (
    user_ID Int NOT NULL,
    photo_ID Int NOT NULL,
    PRIMARY KEY (user_ID),         -- only one profile photo per user
    FOREIGN KEY (user_ID, photo_ID) 
      REFERENCES photo (user_ID, photo_ID) 
      ON UPDATE CASCADE
);

score 0 · Answer 3 · answered Feb 14 '14 at 14:15

You can remove the photo_ID from userprofphoto and add one column profile_photo on table photo the value will be 1/0 make add profile_photo=1 in your where condition.

When user select the profile photo you just update profile_photo=1, in this case you have to do two update first update to set profile_photo=0 where profile_photo=1 second update will be set set profile_photo=1 where ID=[PHPTO_ID]

MySQL relationship loop

3 Answers3