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

Improve this question
4
  • 1
    I'm with you you on that one, I do the same but mainly because I've stumbled across some pretty nasty bugs when mapping databases to consume them from my dev environment (vs)... sometimes composite keys are not so much liked by the orm mapper... Now that I think about it this hasn't happened in a while... maybe I'm just paranoid.
    – Nelson
    Commented Jul 6, 2017 at 12:33
  • 4
    You mention 3 points: "1.A primary key value must be unique, 2.The primary key should be as compact as possible, 3.Primary key value should be stable". Apart from 2, a composite key satisfies 1 and 3. And for point 2, the difference may not be that much. Depends on the sizes of the two columns.
    – ypercubeᵀᴹ
    Commented Jul 6, 2017 at 13:00
  • 2
    Nothing you have said justifies Method 1. What is the purpose of creating an extra column and an extra index? Are you actually using that ID attribute for something or is it just dead weight?
    – nvogel
    Commented Jul 6, 2017 at 21:48
  • Possible duplicate of dba.stackexchange.com/questions/6108/…
    – Joel Brown
    Commented Aug 16, 2021 at 11:21

1 Answer 1

Reset to default
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.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.