Just assume it’s a super critical feature to have user configurable colourful labels
actually, I wanted an AI on user_has_photo_has_label and reference it in user_has_photo_has_label_has_colour - but it looks like you meant that?
Did you mean lavender in a label? You are right on this one, this would need an extra join. And also “which photos are labelled with lavender colour” would also need an extra join.
well, I never said anything about not having to join!
Ok, let me check When looking for users or photos which have a label of a colour - more hassle because of an extra join.
But those are not common queries I would want to run. A much more common query would be one used to fetch labels with their colours on a page. Let’s say there is a page with a user’s favourited photo (corresponding to user_has_photo) and I want to fetch all labels with their colours. How would you do this? Here is my take:
SELECT label.name,
(SELECT GROUP_CONCAT(colour)
FROM colour
INNER JOIN user_has_photo_has_label_has_colour
ON user_has_photo_has_label_has_colour.colour_id=colour.id
WHERE
user_has_photo_has_label_has_colour.user_has_photo_has_label_user_has_photo_user_id=user_has_photo_has_label.user_has_photo_user_id
AND user_has_photo_has_label_has_colour.user_has_photo_has_label_user_has_photo_photo_id=user_has_photo_has_label.user_has_photo_photo_id
AND user_has_photo_has_label_has_colour.user_has_photo_has_label_label_id=user_has_photo_has_label.label_id
) AS label_colours
FROM user_has_photo_has_label
INNER JOIN label
ON label.id=user_has_photo_has_label.label_id
WHERE
user_has_photo_has_label.user_has_photo_user_id=1
AND user_has_photo_has_label.user_has_photo_photo_id=2
If I had an AI PK on user_has_photo_has_label then this would be simpler:
SELECT label.name,
(SELECT GROUP_CONCAT(colour)
FROM colour
INNER JOIN user_has_photo_has_label_has_colour
ON user_has_photo_has_label_has_colour.colour_id=colour.id
WHERE
user_has_photo_has_label_has_colour.user_has_photo_has_label_id=user_has_photo_has_label._id
) AS label_colours
FROM user_has_photo_has_label
INNER JOIN label
ON label.id=user_has_photo_has_label.label_id
WHERE
user_has_photo_has_label.user_has_photo_user_id=1
AND user_has_photo_has_label.user_has_photo_photo_id=2
In this case the simplification is in the subquery WHERE clause and not in a join but this is what I meant. You are right that some selects will require an additional join with the added AI PK but considering those would be uncommon selects I’m not yet 100% sold - this would depend on specific requirements. The 3-column WHERE clause in the first subquery is what I mean by “noise and hassle”