Hacker News Reader

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

Whatever you do, always let some manager decide about the actual key, then you can always blame them for their mistake :)

I 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

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

Most of the time you don't interact directly with elements, but with molecules. Oh, and ions. And radicals. And and....

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

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

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

Make it a composite key of atomic and mass numbers, then.

But then you need to have a stored procedure to maintain integrity every time an atom undergoes radioactive decay, that's got to be bad for performance!

But then you'd miss on crystal structure to distinguish your diamonds from your coal.

Which single element are they then?

Surely such a system would have a products table with something like nominal element fk, purity, mass columns anyway. (And stock table with fk to products.)

Then nothing wrong with 'natural' key on the elements, but sure, products are the thing you've (personally) made up, so of course there's no 'someone else's' key to use.

Diamonds and coal are both pure carbon (mostly carbon-12). So is graphite, and buckminsterfullerene, and a number of other things. Crystal structure matters a lot.

They're not pure carbon was my point. I wasn't saying crystal structure not important, I missed that but it'd be in my 'product derived from element x' table.

Actually, in that case that would not be a properly normalized key because the mass number (counts both protons and neutrons) depends on the atomic number(protons).

Also it gets fun if you want to store anti-particles too.

The periodic table is a human construct. So is the concept of an identifier.

Most elements are stable, especially most of the ones we deal with, and they fall in clear periods and rows based on chemical valence. This is true for main group elements and mostly true for transition metals.

I think the periodic table was discovered, not invented. My guess is that whenever we communicate with some advanced alien species, the periodic table is going to be one of those things we share.

Periodic table is recording multiple different patterns in atoms. Some better than others. By nature, its a dimensional reduction. There are many alternative renderings of the periodic table (or spiral, or cube, or ...) emphasizing different regularities. The one you refer to is just the most popular rendering.

The concept of an identifier comes with the concept of a bijection (or at least a injection). That is a truly basic concept in Mathematics.

You could argue Mathematics is a human construction. But at that point, isn't every concept a human construction? In that case it is hardly helpful, or meaningful to say a concept is a human construct, since all concepts are.

The problem isn't the concept of a bijective function, it is picking the right function and that is a social problem.

I’d wager that nonhuman intelligences out there in the universe have analogous tables to the periodic table.

And I suspect it would be possible for us to JOIN our periodic table onto theirs by matching on the atomic number field to create a mapping between, say, our names for the elements and theirs.

That seems unlikely to be something we would be able to do with literally any other dataset that an alien civilization has. Their star catalog will contain many of the same objects ours does - but there won’t be an obvious key that lets us join them up.

I guess there’s a few mathematical objects that would work - the list of Platonic solids maybe, or the symmetry groups… but in terms of real world physical things? Atomic numbers might be the only truly natural key there is.

The periodic table groups atoms by the number of protons they have and ignores other differences. Uranium 235 and Uranium 238 have the same "natural key" according to the periodic table. The periodic table is useful for solving a lot of problems, and for other problems where it doesn't work to treat U-235 and U-238 as the same, we (and other intelligences) use other ways of categorizing atoms.

Right, but there’s a reason we call them ‘isotopes of Uranium’ rather than saying U-238, Np-238 and Pu-238 are isotopes in the ‘family of atoms with atomic mass 238’.

Things with 92 protons in them behave chemically in similar ways. They form the same crystals and molecules.

If you were building a table of isotopes you’d put a column for ‘proton count’, and you’d find lots of the properties of the isotopes depend on the proton count not the atomic mass. So you could normalize that table structure out and create another table with the atomic number as the key, and put all the properties that are common to all isotopes of a given element in that table.

You would have made a table of elements and it would have the same primary key as the periodic table.

That’s what a natural key is. One that emerges naturally when you normalize data you have collected.

I suppose similarly ‘electrons in outer shell’ might emerge as a natural key by that process.

I don't think that's what people mean when they say "natural key." People say "natural key" when they're talking about a unique identifier (that's the "key" part) that is part of the domain model of an object (the "natural" part.)

If we drop the "unique identifier" aspect of a key, and we can distinguish between different objects that share the same value according to one key by using a different key, then anything can be a key. The number of pages in a book could be a "natural key." This conversation wouldn't exist if that's what people meant by "natural key," because the answer would be trivial.

> If we drop the "unique identifier" aspect of a key, and we can distinguish between different objects that share the same value according to one key by using a different key, then anything can be a key.

And the point the article is making, and that I was trying to emphasize further in my top-level comment, is that there are no perfectly unique identifiers. There are no UIDs in nature. UIDs do exist in human-designed systems, but they are only as good as the actual implementation of the system is, including its evolution over time.

Your own surrogate keys are the most reliable UIDs, because while you can still screw those up, it's in your power to avoid the mistake, and it's also in your power to fix any problem. Once you start using as keys "UIDs" that aren't owned by you, you lose both the ability to prevent bugs and to fix them.

For example, if you're making a library system and use a surrogate ID as a key, duplicates may happen - but they're always indicative of someone screwing up data entry, and can be trivially corrected by assigning a new ID for the entry. If you're using ISBN, or EAN-13, or some unholy hash of author/title/publication year, you're going to get a lot of corner cases, and you won't be able to fix them (you can't exactly change the barcode on everyone's copy of a printed book, just to undo the problems caused by the publisher playing funny games with SKUs).

> People say "natural key" when they're talking about a unique identifier (that's the "key" part) that is part of the domain model of an object (the "natural" part.)

Yes; under this phrasing, the point of TFA/my top comment is, your "natural key" is going to be only as good as your domain model of an object is - which, in practice, means it's going to be bad, because if your model defines some property correlated with real world to be unique, it's already broken.

Ultimately, the point is: there are no truly unique keys in the real world. Best you can do is to chose a key that's least likely to be duplicated in data. Perhaps so unlikely that, in non-critical systems, you can get away pretending it's unique.

You seem to be missing the rather important point that chemical elements are a thing. The atomic number uniquely identifies an element. There are not two distinct elements with atomic number 92 - there is one element, which we call Uranium. It has a number of isotopes (as do all elements - you can keep stuffing as many neutrons into a nucleus as your particle accelerator will let you). But Uranium is identifiably Uranium.

Sure, if I’m trying to build a table of atoms I can’t use their atomic number as a key. I would need some sort of UUID scheme. But in among all those atoms I’d find a bunch of them had stuff in common, and that that stuff is 100% correlated to each of those atoms’ atomic number.

See, the challenge here is to build a table of something where everyone can agree there is a single unambiguous natural key we can use for that thing.

And my (and OP’s) proposal is that you can do that with a table of ‘elements’ with ‘atomic number’ as the key.

You can’t do the same normalization trick with books.

Say you build a table of books with ‘title’, ‘author’, ‘publisher’, ‘year’, ‘ISBN’, ‘edition’, ‘page count’.

Are any of these - or combinations of them - actually a natural key of a common entity in disguise?

Maybe author/title/year? No. For one, I’m sure you can find distinct books that share these values. And ‘year’ is, it turns out, a synthetic key for identifying arbitrary Earth orbital cycles - not a ‘natural’ label for a time period at all. And ‘author’ names are a synthetic key too - many authors publish under multiple names (JK Rowling = Robert Galbraith) and names are just arbitrary keys assigned to specific consciousnesses embodied in particular bundles of cells for a period of years. And of course they aren’t unique, so… no good as a key anyway.

Sure you could say ‘well, I want to make “n page books” into an entity and extract that to a table but you wouldn’t find any properties you could extract out to that table - there isn’t anything that all 92 page books have in common that you could tag onto that relation.

Whereas your chemical elements table has LOTS of properties that are common to everything with each atomic number. Electron structure. What molecules it can form. What crystalline structures it forms. Sure, not its half life or its melting point, but still - useful facts that are common to all atoms with the same atomic number.

> You seem to be missing the rather important point that chemical elements are a thing.

I fundamentally disagree with this. Elements are a human-created categorization of atoms (granting that atoms are real) by atomic number.

> Maybe author/title/year? No. For one, I’m sure you can find distinct books that share these values.

Author/title/year is very much like atomic number: extremely useful and sufficient for very many practical purposes. But when dealing with books, sometimes we need more than author/title/year, and when dealing with atoms, sometimes we need more than atomic number.

Atomic number is a natural key to the elements, because it's part of the definition of element.

This is why natural keys don't exist in nature, but can exist for things we invent: because a key uniquely identifies any member of a set. A key makes every possible distinction. When we categorize things, we select some distinctions (such as the number of protons in an atom's nucleus) and throw away others (such as the number of neutrons.) The enumeration and definition of relevant distinctions is necessary for the existence of a key. We defined the elements, and as a result of how we defined them, they have a key. When we define a book table in a database, it has a key as a consequence of how it is defined.

If we don't define the set ourselves, as our invention, it is impossible to anticipate all of the possible distinctions between members, so it is impossible to construct a key.

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

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

Give everyone prime numbers and a N-way conference can be done with multiplication.

With some very long numbers and a hot market for short ones!

But think of the ease of conferencing!!!!

You just have to whip out your calculator (likely an app on your mobile device), type all the participants' numbers in, and...

Wait a minute!

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

A phone number is definitely not a number - for example, it can often be written as starting with a +. Not only that, but one number can have multiple valid expressions (eg. a number with and without a country code). That's ignoring the different ways people like to format them (spaces, dashes, letters, etc.)

Yes, and leading zeroes are significant in phone numbers.

I was about to suggest just hashing our DNA, but then, remembered identical twins+ exist.

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

and gene therapy