Is it a bad practice to use primary key in junction tables?

[quote=“Lemon_Juice, post:20, topic:197054, full:true”]
well, I never said anything about not having to join!
[/quote]quite right, you said the join would involve more columns, which is kinda just “less noise and hassle” again

it was me who mentioned you’d have to do extra joins :wink:

you’re using an unnecessarily complex query which makes it harder to write than it should be

try this –

SELECT label.name , GROUP_CONCAT(colour.colour) AS colours FROM user_has_photo_has_label AS upl INNER JOIN label ON label.id = upl.label_id INNER JOIN user_has_photo_has_label_has_colour AS uplc ON uplc.user_id = upl.user_id AND uplc.photo_id = upl.photo_id AND uplc.label_id = upl.label_id INNER JOIN colour ON colour.id = uplc.colour_id WHERE upl.user_id = 1 AND upl.photo_id = 2 GROUP BY label.name

(some unnecessarily verbose names were mercifully renamed)

you are welcome to rewrite this for your auto_increment scenario

:slight_smile:

These table names are hurting my brain. :-p Why wouldn’t you call that sucker “user_favorite_photo_label_colors”?

Overall I agree that your practical usage example looks like a situation where it’s better to have a single-column primary key in what would otherwise be a junction table. But I think you need to emphasize more clearly that the problem arises only when you need to reference the junction as a foreign key. Because otherwise, the composite key could span 10 columns and it wouldn’t be a problem. It’s only when you need to relate the junction as a foreign key that even a 2-column key would be inconvenient.

For what it’s worth, in Doctrine terminology (circling back to OP), we would probably stop thinking of these things as junction tables and start thinking of them as separaite entities, and rather than a single many-to-many relationship with an implicit junction table, instead there would be a new entity with two one-to-many relationships. In Juice’s example, for instance, there would be a UserFavoritePhoto entity with a one-to-many relationship with users and a one-to-many relationship with photos.

in any data modelling discussion as well

only techies think of “junction” tables as instantiations of many-to-many relationships

each FK is its own entity relationship

especially in grandchildren structures like userfavouritephotos_gots_labels_gots_colors

To my brain they are of equal complexity - you are doing one more join while I’m doing a subquery. But certainly yours is much better formatted and the aliases also make it more readable.

I will do it in my mind - I’m getting rid of the two conditions in the second join :smile:

This was a very quick act of designing a database and I just let Mysql Workbench leave the default table names :smiley:

I wouldn’t say it is very inconvenient - it can be a slight inconvenience having to use a composite key but as r937 showed getting rid of the composite FK can have its drawbacks, too.

Correct - but I think a good ORM would be able to make out all the relations based on the composite keys without having to replace them with AI primary keys - in other words, this would be transparent to the user (developer).

Yes, but I fear that the deeper we go down the chain of relationships the farther the translated objects would be in appearance and behaviour from the database and we would end up with two very distinct worlds. That is of course the goal of ORM but I’m not sure if this would make things easier for the developer. I’ve never really queried such complex data structures with ORM but I suppose the task of getting all user’s labels with colours would be quite an undertaking without resorting to SQL.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.