2

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.

enter image description here

Prisoner ZERO
  • 129
  • 1
  • 5

1 Answers1

3

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.

David Browne - Microsoft
  • 49,000
  • 3
  • 53
  • 102