5 ms·

The ultimate problem with natural keys is that their uniqueness is a property of business rules. Unless you live in some magical fairytale land where business r

by awj 9y ago

The ultimate problem with natural keys is that their uniqueness is a property of business rules. Unless you live in some magical fairytale land where business rules are stable, sensible, or both, depending on them for uniqueness is asking to have problems.

squeaky-clean 9y ago
> Unless you live in some magical fairytale land where business rules are stable, sensible, or both, depending on them for uniqueness is asking to have problems. Thankfully mine are legally enforced, so it works well :)
- ksenzee 9y ago
  Even that is not a forever guarantee. Laws can and do change.
  - squeaky-clean 9y ago
    Definitely true, but at a certain point you need to draw a line. If these things were to change, the entire product would have to change, not just what I use as my keys, so I think natural keys are a safe bet in my case.
  - dbenhur 9y ago
    And people act outside the bounds of the law. SSN seems like a good natural key candidate, until you discover some of the people your business interacts with are illegal aliens.
    - int_19h 9y ago
      Or legal aliens, like tourists. Or foreigners, who aren't even in the country. Or really anyone else who doesn't work in the country, and doesn't have to report taxes. Ironically, many illegal immigrants actually do report taxes. In which case they use ITIN, which is compatible to SSN format-wise (###-##-####)... but it is not unique over time.
- nvivo 9y ago
  Here in Brazil, we have the CPF (somewhat equivalent to US SSN), a "unique" number assigned by the government for identification. It's used by almost every business I have ever know as a key. Not always primary key, but certainly people treat it as unique. Except for the fact they are not unique, and there have been cases where people from different states have the same number because each state generated this "random" number, but didn't actually check with each other if it's really unique. It is legally enforced, no two people should ever have the same, but they do. The only way to have uniqueness is if you know the algorithm AND can control that it behaves as expected. Anything else is just kidding yourself.
- TeMPOraL 9y ago
  It works until it doesn't :). A lot depends on how well they're enforced. SSNs are a go-to example, but in other countries which have actual ID numbers for citizens[0], duplicates do happen every now and then because of e.g. clerical errors. Real world is messy, and something can be trivially fucked up, it will be. -- [0] - as opposed to the way US handles the SSN, where the number was explicitly forbidden from being used for identification, but people use it for it anyway.
  - codedokode 9y ago
    There are not only duplicates, but sometimes the documents can be reissued with another number or ID, sometimes there are people without the document that your system expects everybody to have.
- jrochkind1 9y ago
  > Thankfully mine are legally enforced, so it works well What, you think laws never change?
dghf 9y ago
There is an argument that if your data does not have natural keys -- that if it depends on the addition of arbitrary system IDs to prevent duplication -- then it is not truly relational.
- ScottBurson 9y ago
  Interesting that you would say that. It does seem to me that if your rows have these arbitrary IDs, they are more like objects than relational purists would like to admit. (I seem to recall that the relational people used to beat up on OODBs for lacking this relational purity. But OODBs are pretty much dead anyway, I guess.)
  - theoh 9y ago
    But, to state the obvious, anonymous items in computing (e.g. anonymous functions, or untitled/unaddressed draft emails etc.) generally need to be assigned an id for the software to keep track. The practice of avoiding natural keys and assigning unique ids is analogous. No paradigm is implied.
    - TeMPOraL 9y ago
      In programs, anonymous objects always have an unique ID automatically assigned - by the very virtue of being separate entities. Usually those IDs are what we call "references" in programming.
      - theoh 9y ago
        I'm talking about the level of e.g. programming language implementation. A lambda needs to be assigned an id, for example, but it's not a reference that the user can manipulate. It's an implementation detail, as are surrogate keys.
        TeMPOraL 9y ago
        You don't manipulate the reference directly (i.e. you shouldn't learn its numeric value (though you can - useful for debugging), and you can't change it) - but you do use it indirectly all the times with ==, eq, or however the referential equality operator is called in a given programming language. This is what, given (excuse the Java): MyObject a = new String("choo choo"); MyObject b = a; MyObject c = new String("choo choo"); allows you to observe that: a == b; a != c; b != c; even though: a.equals(b); a.equals(c); b.equals(c); under string comparison. This is exactly like surrogate keys. An implementation detail that's crucial to the meaning of the whole thing.
jholman 9y ago
I strongly agree. Natural keys are an ivory-tower pattern, and a software-development anti-pattern.
holri 9y ago
Why not change the definition of natural keys then?

by awj 9y ago

squeaky-clean 9y ago

> Unless you live in some magical fairytale land where business rules are stable, sensible, or both, depending on them for uniqueness is asking to have problems. Thankfully mine are legally enforced, so it works well :)

ksenzee 9y ago
Even that is not a forever guarantee. Laws can and do change.
- squeaky-clean 9y ago
  Definitely true, but at a certain point you need to draw a line. If these things were to change, the entire product would have to change, not just what I use as my keys, so I think natural keys are a safe bet in my case.
- dbenhur 9y ago
  And people act outside the bounds of the law. SSN seems like a good natural key candidate, until you discover some of the people your business interacts with are illegal aliens.
  - int_19h 9y ago
    Or legal aliens, like tourists. Or foreigners, who aren't even in the country. Or really anyone else who doesn't work in the country, and doesn't have to report taxes. Ironically, many illegal immigrants actually do report taxes. In which case they use ITIN, which is compatible to SSN format-wise (###-##-####)... but it is not unique over time.
nvivo 9y ago
Here in Brazil, we have the CPF (somewhat equivalent to US SSN), a "unique" number assigned by the government for identification. It's used by almost every business I have ever know as a key. Not always primary key, but certainly people treat it as unique. Except for the fact they are not unique, and there have been cases where people from different states have the same number because each state generated this "random" number, but didn't actually check with each other if it's really unique. It is legally enforced, no two people should ever have the same, but they do. The only way to have uniqueness is if you know the algorithm AND can control that it behaves as expected. Anything else is just kidding yourself.
TeMPOraL 9y ago
It works until it doesn't :). A lot depends on how well they're enforced. SSNs are a go-to example, but in other countries which have actual ID numbers for citizens[0], duplicates do happen every now and then because of e.g. clerical errors. Real world is messy, and something can be trivially fucked up, it will be. -- [0] - as opposed to the way US handles the SSN, where the number was explicitly forbidden from being used for identification, but people use it for it anyway.
- codedokode 9y ago
  There are not only duplicates, but sometimes the documents can be reissued with another number or ID, sometimes there are people without the document that your system expects everybody to have.
jrochkind1 9y ago
> Thankfully mine are legally enforced, so it works well What, you think laws never change?

dghf 9y ago

There is an argument that if your data does not have natural keys -- that if it depends on the addition of arbitrary system IDs to prevent duplication -- then it is not truly relational.

ScottBurson 9y ago
Interesting that you would say that. It does seem to me that if your rows have these arbitrary IDs, they are more like objects than relational purists would like to admit. (I seem to recall that the relational people used to beat up on OODBs for lacking this relational purity. But OODBs are pretty much dead anyway, I guess.)
- theoh 9y ago
  But, to state the obvious, anonymous items in computing (e.g. anonymous functions, or untitled/unaddressed draft emails etc.) generally need to be assigned an id for the software to keep track. The practice of avoiding natural keys and assigning unique ids is analogous. No paradigm is implied.
  - TeMPOraL 9y ago
    In programs, anonymous objects always have an unique ID automatically assigned - by the very virtue of being separate entities. Usually those IDs are what we call "references" in programming.
    - theoh 9y ago
      I'm talking about the level of e.g. programming language implementation. A lambda needs to be assigned an id, for example, but it's not a reference that the user can manipulate. It's an implementation detail, as are surrogate keys.
      - TeMPOraL 9y ago
        You don't manipulate the reference directly (i.e. you shouldn't learn its numeric value (though you can - useful for debugging), and you can't change it) - but you do use it indirectly all the times with ==, eq, or however the referential equality operator is called in a given programming language. This is what, given (excuse the Java): MyObject a = new String("choo choo"); MyObject b = a; MyObject c = new String("choo choo"); allows you to observe that: a == b; a != c; b != c; even though: a.equals(b); a.equals(c); b.equals(c); under string comparison. This is exactly like surrogate keys. An implementation detail that's crucial to the meaning of the whole thing.

jholman 9y ago

I strongly agree. Natural keys are an ivory-tower pattern, and a software-development anti-pattern.

holri 9y ago

Why not change the definition of natural keys then?