Keys in M-to-M crosstabs?

Question

SQL

I must say right away that I understand different decisions and can justify them, but I would like to hear the opinions of others.
A common situation: there are three tables - two entities and a crosstab that implements a many-to-many relationship between these two entities. Example: experts (Expert) and technologies (Technology) that they own are connected many-to-many (ExpertTechnology). The challenge is how to organize the keys in the crosstab.
Solution 1: The crosstab should have two fields, ExpertId and TechnologyId, for each FOREIGN KEY field and make another PRIMARY KEY for a pair of fields (ExpertId, TechnologyId).
Solution 2:The crosstab should have three fields, ExpertTechnologyId, ExpertId and TechnologyId, for ExpertId and TechnologyId we make a FOREIGN KEY and we make a PRIMARY KEY for the ExpertTechnologyId field and an alternative unique key for a pair of fields (ExpertId, TechnologyId).
The first option is quite optimized, and it seems like nothing else is needed, but the second option is good because you can always refer to this ExpertTechnologyId, instead of referring to a pair (ExpertId, TechnologyId), well, some software has an internal limitation to work only with tables where the first field must be a unique identifier.

Answer 1

I use both options but in different situations.
1 - option - if the connection does not have characteristics, for example, content and tags
2 - option - if the connection has characteristics - i.e. for example, if you need to limit the available number of subobjects from the context of the first entity to the second, then this limit is stored in a table with a link, it needs to be manipulated, for this a personal identifier is entered

Answer 2

PRIMARY KEY on the ExpertTechnologyId field can be omitted (in some cases it can reduce performance and is not always really in demand), but the ExpertTechnologyId field itself, as it seems to me, is necessary. It is convenient for debugging queries, it comes in handy in the program. And as you yourself wrote, often the first option is upgraded to the second.

Answer 3

I never applied the second option - there was no need. On the web, for example, if you need to remove a group from a user, then it is easier to take the already passed user_id and pass the group_id, which, as a rule, is still needed at least for the link, than to enter an additional id for the link.
In my opinion, it all depends on you, if it is more convenient for you to work with an additional key, then you need it, if not, then no. In terms of performance, it is almost equivalent, only you need to remember to set up indexes.
I think that both options are correct, do as you like.