6 ms·
This. I'm actually surprised the linked Wikipedia article on "Natural key" only mentions one potential disadvantage, that somewhen in the future, maybe, at some
by TeMPOraL 3y ago
This. I'm actually surprised the linked Wikipedia article on "Natural key" only mentions one potential disadvantage, that somewhen in the future, maybe, at some point, somehow, the other system may change, and then your keys are bust. They even give US SSNs as an example of something that may stop working as unique key in the future.
There are no "natural keys". They don't exist in nature. They don't exist as a thing in our physical reality. And when it comes to human-assigned identifiers, it should be emphasized much more strongly that whatever external thing you think is a good unique ID, it isn't.
Names? Addresses? Dates? Nope - have you heard of the "Falsehoods programmers believe about..."?
Government IDs? Nope. US SSN was not designed to be used as UID from the start, but even when you have an identifier that was - like e.g. PESEL number in Poland - it's still not an unique ID, because people and computers make mistakes. Multiple people having the same PESEL is something that's been known to happen.
(Side note: "identification number" really means "opaque string". IDs are not numbers. If adding or multiplying them together doesn't make sense, they're not numbers.)
Anything business adjacent? Don't make me laugh. The article covered a lot of the reasons why any kind of tracking IDs are broken for the purpose - most of them dealing with the messy nature of reality - but businesses are businesses. They cut corners. They lie and cheat. That's why you see the same EAN code covering multiple products a store might even try and price differently. That's why SKUs get reused. That's why these days you often have multiple distinct products under the same SKU - first, quality batch, released to get early positive reviews, and then the main batch made with much cheaper and lower-quality parts. Product names? That's even worse - you'll have variations such as "extra batch of super-low-quality, reduced functionality, made specifically for Black Friday, so it can be sold under the same name as the OG product, seemingly at big discount".
This is not to say "never use other people's IDs". There's a trade-off here, but making a good choice starts with assuming that the external "unique ID" is neither unique, nor a good ID, and you'll have to deal with it at some point.
- amelius 3y agoWhatever you do, always let some manager decide about the actual key, then you can always blame them for their mistake :)
- genuine_smiles 3y ago> There are no "natural keys". They don't exist in nature. They don't exist as a thing in our physical reality. And when it comes to human-assigned identifiers, it should be emphasized much more strongly that whatever external thing you think is a good unique ID, it isn't. It seems to me that nature has plenty of natural keys, it’s human created constructs that don’t. For example, the periodic table has natural keys.
- rini17 3y agoMost of the time you don't interact directly with elements, but with molecules. Oh, and ions. And radicals. And and....
- TeMPOraL 3y agoAnd isotopes. And transient, unstable configurations that may exist for a brief moment before decaying into something that slots into the periodic table better. And also elements aren't a thing, quantum fields are. This is why you sometimes see things that just don't fit the idea of distinct atoms. But then quantum fields also aren't a thing. We don't know what exactly is the thing. We only have increasingly sophisticated working models.
- rini17 3y agoNo need to go to such philosophical depths. We're only poor engineers trying to come up with workable model. The periodical table of elements was a true boon and it is unlikely these "foreign keys" will change, but still - it alone isn't fully suitable as data model for chemistry.
- dale_glass 3y agoYeah, but you don't want to use that as a primary key for your stock database. It'll work fine until you find you want to keep track of U-235 and U-238 separately. What constitutes identity is context-dependent. Yeah, on one level they're the same thing. On another they aren't.
- Throw10987 3y agoI find myself coming back to Data and Reality on a periodic basis to remind me that the model is just the model. https://www.bkent.net/Doc/darxrp.htm https://www.bkent.net/Doc/darxrp.htm
- EdwardDiego 3y agoI was about to suggest just hashing our DNA, but then, remembered identical twins+ exist.
- bruce511 3y agoOh it's worse than that. There are lots of edge cases with DNA. Chimera exist [1], plus there are issues with transplant recipients and so on. I expect there are more edge cases I don't know about. [1] https://www.scientificamerican.com/article/3-human-chimeras-that-already-exist/#:~:text=A%20chimera%20is%20essentially%20a,to%20make%20two%20separate%20organisms https://www.scientificamerican.com/article/3-human-chimeras-....
- drivers99 3y agoand gene therapy
- bruce511 3y agoIf I could go back 25 years and give myself advice, it would be this. A Lot of pain over my career would have been avoided. I would also tell myself not to use sequential numbers for surrogate keys. They're massively convenient, but a real pain later on (data security, and distributed data being just two cases.) I know there are folks who like natural keys, but frankly IMO they're a straight-up bug. YMMV.
- sabas123 3y ago> (Side note: "identification number" really means "opaque string". IDs are not numbers. If adding or multiplying them together doesn't make sense, they're not numbers.) Eeehhh I'm not sure if this necessarily true. A telephone number is definitely a number but addition doesn't make all too much sense on them. If you want to specifically talk about objects that require addition and multiplication to be properly defined on them then you can always use Groups and Rings.
- OJFord 3y agoYou could construct a telephone numbering system where the sum of two 'real' numbers could be dialed to start a three-way conference call. That would be.. amusing, to some subset of users.
- kroltan 3y agoGive everyone prime numbers and a N-way conference can be done with multiplication.
- kweingar 3y agoA phone number is a number in a colloquial sense (a string of digits) but I would hate to use any computer system where phone numbers are stored or reasoned about as actual mathematical numbers.
- JadedBlueEyes 3y ago