Appropriate use of surrogate key?

Question

Say a particular country has many sights (monuments, parks, museums), and of course a particular sight can exist in many states.

In terms of a logical ERD model, a junction table should be used between the M:N relationship.

However, would it suffice to simply use the PKs of the two adjoining tables as the PK of the junction table?

That is, you have a COUNTRY, SIGHTS and SIGHT_TYPE table, where SIGHTS is the junction table. Assuming a particular country can have multiple museums (i.e. MUSEUM is a SIGHT_TYPE), this would mean each record in the SIGHTS table could not be uniquely identified, wouldn't it?

i.e. assuming MUSEUM has SIGHT_TYPE_ID of 2 and GERMANY has a country_id of 22. For multiple museums, wouldn't you have this occurring if the above was true? 22 2 - Museum A 22 2 - Museum B

Thus, is the use of a surrogate key, say, SIGHT_ID, absolutely essential in this case to serve as a unique identifier?

In other words, generally the use of the PKs of the two adjoining tables is used as the PK for the junction or composite table, however are cases like these exceptions to that?

Thank you

Answer 1

Composite keys for junction tables (made up of FKs) are completely OK at the logical design level. They guarantee uniqueness of rows in the junction table. They also guarantee that all of the participants in a given instance of the relationship participate in only one instance of the relationship. If you are using some sort of app builder tool, that tool has to be capable of dealing with a composite PK.

Normally, that constraint is exactly what you want. If you add an extra column to the junction box to serve as a primary key, you are either going to have to add a unique constraint on the two foreign keys or rely on the application(s) to prevent erroneous duplicates. Most of the time, these solutions are not as good.

At the physical design level, it depends on what you are going to do with the data, what volume of data you are going to store, and on your particular DBMS. I would tend to go without the extra surrogate key in an initial design, and add it only if it saves more than it costs.