Many-to-many link table design : two foreign keys only or an additional primary key?

Question

Hello experts, this is undoubtedly a newbie question, but I haven't been able to find a satisfactory answer. When creating a link table for many-to-many relationships, is it better to create a unique id or only use two foreign keys of the respective tables (compound key?).

Looking at different diagrams of the Northwind database for example, I've come across both 'versions'. That is: a OrderDetails table with fkProductID and fkOrderID and also versions with an added OrderDetailsID.

What's the difference? (does it also depend on the DB engine?). What are the SQL (or Linq) advantages/disadvantages?

Thanks in advance for an explanation.

Tom

Answer 1

I'm used to use PrimaryKey column. It's because the primary key uniquely identify the record. If you have a cascade-update settings on table relations, the values of foreign keys can be changed between "SELECT" and "UPDATE/DELETE" commands sent from application.

Answer 2

A common practice is to have a unique auto generated single id as physical key and a unique constraint on multiple columns for the logical key

Answer 3

ORMs have been mandating the use of non-composite primary keys to simplify queries...

But it Makes Queries Easier...

At first glance, it makes deleting or updating a specific order/etc easier - until you realize that you need to know the applicable id value first. If you have to search for that id value based on an orders specifics then you'd have been better off using the criteria directly in the first place.

But Composite keys are Complex...

In this example, a primary key constraint will ensure that the two columns--fkProductID and fkOrderID--will be unique and indexed (most DBs these days automatically index primary keys if the clustered index doesn't already exist) using the best index possible for the table.

The lone primary key approach means the OrderDetailsID is indexed with the best index for the table (SQL Server & MySQL call them clustered indexes, to Oracle they're all just indexes), and requires an additional composite unique constraint/index. Some databases might require additional indexing beyond the unique constraint... So this makes the data model more involved/complex, and for no benefit:

• Some databases, like MySQL, put a limit on the amount of space you can use for indexes.
• the primary key is getting the most ideal index yet the value has no relevance to the data in the table, so making use of the index related to the primary key will be seldom if ever.

Conclusion

I don't see the benefit in a single column primary key over a composite primary key. More work for additional overhead with no net benefit...