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 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.

Please Ignore the ID column naming in the samples below...that is how they do it here.

enter image description here

closed as primarily opinion-based by mustaccio, Dan Guzman, Marco, LowlyDBA, McNets Jul 6 '17 at 15:34

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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. – Nelz Jul 6 '17 at 12:33
  • 2
    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ᵀᴹ Jul 6 '17 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 Jul 6 '17 at 21:48

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.

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