Link table design with many addresses: only two foreign keys or an additional primary key?

Question

This is undoubtedly a question for beginners, but I could not find a satisfactory answer. When creating a link table for many-to-many relationships, is it better to create a unique identifier or use only two foreign keys of the corresponding tables (composite key?).

If you look at the different diagrams of the Northwind database, I came across both versions. That is: an OrderDetails table with fkProductID and fkOrderID, as well as versions with the OrderDetailsID parameter added.

What's the difference? (also depends on the DB mechanism?). What are the advantages / disadvantages of SQL (or Linq)?

Thanks in advance for the explanation.

Tom

Answer 1

ORM is required to use non-composite primary keys to simplify queries ...

But it simplifies queries ...

At first glance, it makes it easy to remove or update a specific order / etc - until you understand that you need to know the identifier value first. If you need to search for this value based on the specifics of the orders, you would be better off using the criteria directly first.

But compound keys are complicated ...

In this example, the primary key constraint ensures that the two columns, fkProductID and fkOrderID, are unique and indexed (most databases these days automatically index primary keys if the clustered index does not already exist) using the best index for the table.

A single primary key approach means that OrderDetailsID indexed with the best index for the table (SQL Server and MySQL call them clustered indexes, and Oracle call them indexes only) and require an additional composite unique constraint / index. Some databases may require additional indexing beyond a unique constraint ... Thus, this makes the data model more complex and complex and has no use:

• Some databases, such as MySQL, set a limit on the amount of space that you can use for indexes.
• the primary key gets the most ideal index, but the value has nothing to do with the data in the table, so using the index associated with the primary key will be rare, if ever.

Conclusion

I see no advantage in the primary key of one column over the composite primary key. More work for extra overhead without a net benefit ...

Answer 2

I am using a PrimaryKey column. This is because the primary key uniquely identifies the record. If you have cascading update options in a table relationship, the values ​​of foreign keys can be changed between the SELECT and UPDATE / DELETE commands sent from the application.