Identity versus Composite values for Primary Keys in Many-To-Many Tables I am in the middle of an interesting "Data Architecture" discussion at a place I am contracting &amp; need some input.

As a habit, I always create a Surrogate Key as the Primary Key in my tables - be they Guid (UUID) or Identity values.  IMHO the synthetic-nature of a Surrogate Key has the distinct value of identifying a row without intruding on "real" or "natural" keys-or-values that may otherwise be useful Alternate Keys.

To me...it just seems natural - but maybe I am wrong.

Where I happen to be at the moment...they argue this good for "core" Entity tables, but is completely incorrect for Many-to-Many (relationship) tables.  While I will happily to oblige the customer...I disagree.

Thoughts behind my approach are:


A primary key value must be unique
The primary key should be as compact as possible
Primary key value should be stable
Primary Key identifies a tuple...not the data INSIDE the tuple


MY QUESTION: Which is either correct or better?  And Why?
- Please provide reasoning etc.

NOTE:
Please Ignore the ID column naming in the samples below...that is how they do it here.
A Many-to-Many mapping table must have a unique index on the foreign key columns.  And should normally have an additional unique index on the foreign key columns in the reverse order.

So while adding an additional synthetic key doesn't do any great harm, it's just not very useful.