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

[Lemon_Juice]

@r937

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

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.

@r937

you are welcome to rewrite this for your auto_increment scenario

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

@Jeff_Mott

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

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

@Jeff_Mott

It’s only when you need to relate the junction as a foreign key that even a 2-column key would be inconvenient.

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.

@Jeff_Mott

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.

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).

@Jeff_Mott

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.

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.