4 ms·

> Tutorials show harmful patterns. Try to find a sample SQL database, for any vendor, that does not employ artificial table primary keys. This was a requiremen

> Tutorials show harmful patterns. Try to find a sample SQL database, for any vendor, that does not employ artificial table primary keys. This was a requirement in the 1980s so that your queries would execute before the heat death of the universe, but that has not been the case for decades. Microsoft is particularly guilty of this. Artificial keys are an anti-pattern. (And, btw, there's a dearth of literature on why artificial keys are an anti-pattern.) Here's the only sample database I know of that consistently uses natural keys across all tables, created by a SQL educator who knows his stuff. https://github.com/ami-levin/Animal_Shelter https://github.com/ami-levin/Animal_Shelter

msla 3y ago
> Artificial keys are an anti-pattern. (And, btw, there's a dearth of literature on why artificial keys are an anti-pattern.) Why, in specific, are artificial keys an anti-pattern? Seems like they preemptively solve problems. For example, using an SSN as a "natural" primary key is both a potential security nightmare and a data normalization nightmare if someone shows up with two or more SSNs, which does happen. (Yes. Yes. My great uncle had at least two SSNs. You won't convince me it can't happen.) Similarly for other "natural" data which isn't bound to obey the properties DBAs need for primary keys.
Tehdasi 3y ago
Never specifically heard about this terminology until now, but after looking, yeah, I can see why ppl prefer 'artificial' keys to natural ones. From the animal shelter example, for people it uses email as the 'natural' key, which immediately runs up against 1,2,3 and 7 in https://beesbuzz.biz/code/439-Falsehoods-programmers-believe-about-email https://beesbuzz.biz/code/439-Falsehoods-programmers-believe...
- jackfoxy 3y ago
  "Natural Key" actually refers to column(s) already available in the dataset you are turning into a row that is unique by row, and thus a candidate for being the key. (Subject to all the normalization, etc.). An artificial key is a column of made-up data added to the rest of the column. It has no use other than to provide uniqueness. In most cases there is already a unique candidate key in your data.
bsuvc 3y ago
I am curious based on your opinion of this. Do you work in academia or business?
redhale 3y ago
"X is an anti-pattern" comments/articles, offered with no backing evidence, is a spot-on example of bullshit tech content.
wizofaus 3y ago
I literally just read a very convincing post, with good examples, on why relying solely on natural keys is rarely a good idea. Nothing in that repo convinced me otherwise (and alarm bells certainly went off when I saw a table called Species where the primary key was a varchar field "Species" with values like "cat" and "raccoon", neither of which are species at all).
- wolfgang42 3y ago
  We just had https://matt-schellhas.medium.com/unnatural-keys-425a68ee350c https://matt-schellhas.medium.com/unnatural-keys-425a68ee350... on HN a couple of weeks ago, too: https://news.ycombinator.com/item?id=36101544 https://news.ycombinator.com/item?id=36101544
  - wizofaus 3y ago
    Yep, that was the exact article I'd read!
msla 3y ago
This is, now, obviously a troll account, and an example of bullshit in tech.

msla 3y ago

> Artificial keys are an anti-pattern. (And, btw, there's a dearth of literature on why artificial keys are an anti-pattern.) Why, in specific, are artificial keys an anti-pattern? Seems like they preemptively solve problems. For example, using an SSN as a "natural" primary key is both a potential security nightmare and a data normalization nightmare if someone shows up with two or more SSNs, which does happen. (Yes. Yes. My great uncle had at least two SSNs. You won't convince me it can't happen.) Similarly for other "natural" data which isn't bound to obey the properties DBAs need for primary keys.

Tehdasi 3y ago

Never specifically heard about this terminology until now, but after looking, yeah, I can see why ppl prefer 'artificial' keys to natural ones. From the animal shelter example, for people it uses email as the 'natural' key, which immediately runs up against 1,2,3 and 7 in https://beesbuzz.biz/code/439-Falsehoods-programmers-believe-about-email https://beesbuzz.biz/code/439-Falsehoods-programmers-believe...

jackfoxy 3y ago
"Natural Key" actually refers to column(s) already available in the dataset you are turning into a row that is unique by row, and thus a candidate for being the key. (Subject to all the normalization, etc.). An artificial key is a column of made-up data added to the rest of the column. It has no use other than to provide uniqueness. In most cases there is already a unique candidate key in your data.

bsuvc 3y ago

I am curious based on your opinion of this. Do you work in academia or business?

redhale 3y ago

"X is an anti-pattern" comments/articles, offered with no backing evidence, is a spot-on example of bullshit tech content.

wizofaus 3y ago

I literally just read a very convincing post, with good examples, on why relying solely on natural keys is rarely a good idea. Nothing in that repo convinced me otherwise (and alarm bells certainly went off when I saw a table called Species where the primary key was a varchar field "Species" with values like "cat" and "raccoon", neither of which are species at all).

wolfgang42 3y ago
We just had https://matt-schellhas.medium.com/unnatural-keys-425a68ee350c https://matt-schellhas.medium.com/unnatural-keys-425a68ee350... on HN a couple of weeks ago, too: https://news.ycombinator.com/item?id=36101544 https://news.ycombinator.com/item?id=36101544
- wizofaus 3y ago
  Yep, that was the exact article I'd read!

msla 3y ago

This is, now, obviously a troll account, and an example of bullshit in tech.