Identity versus Composite values for Primary Keys in Many-To-Many Tables
2
votes
1
answer
2838
views
I am in the middle of an interesting "Data Architecture" discussion at a place I am contracting & 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 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.
- Please provide reasoning etc. **NOTE:**
Please Ignore the ID column naming in the samples below...that is how they do it here.

Asked by Prisoner ZERO
(129 rep)
Jul 6, 2017, 12:24 PM
Last activity: Aug 16, 2021, 11:01 AM
Last activity: Aug 16, 2021, 11:01 AM