Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

You'll regret using natural keys

blog.ploeh.dk

Post reply on HN
Print view View original

201–210 of 568 posts

Re: You'll regret using natural keys

#201

Post by yellowapple »

aaronbrethorst wrote: post #69
Twisell wrote: post #64

Earlier quoted context omitted.

Here's a real world example if you run a script once every 5 minutes that launch sub-task you might be tempted to add a non natural auto-increment number that identify each occurence to create a link between the script and the subtasks. However it will be way more painless to use a timestamp of the script starting point a natural key. This way when shit happen you have a relevant and stable way to identify each occur…

Fast-forward a couple years: now I have scripts that launch sub-tasks more than once a second.

So use timestamps with sub-second precision, which virtually every SQL database supports (even ones like SQLite that don't have built-in date/time types).

Re: You'll regret using natural keys

#202

Post by bambax »

strnisa wrote: post #151

I've become a fan of unique, relatively short and "human-readable" IDs, such at the ones used by Stripe, e.g. `cus_MJA953cFzEuO1z` for an ID of a customer. Here's a Stripe dev article on the topic: https://dev.to/stripe/designing-apis-for-humans-object-ids-3... If you use JavaScript/TypeScript, you can make them like this: function makeSlug(length: number): string { const validChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcde…

Nice, but better to remove ambiguous / hard to read letters: ijlo, IJLO, and 01 (and maybe 7 as well?)

Re: You'll regret using natural keys

#203

Post by aswerty »

fuzztester wrote: post #179
aswerty wrote: post #101

Earlier quoted context omitted.

Surrogate keys do mirror reality though. As I once read in a Terry Pratchett book; if you replace the handle of an axe and then replace the head, is it still the same axe? For me, the answer is yes - since we imbue the axe with an identity outside of it's integral parts. That is what a surrogate key is. An identity. Which is an abstract concept that exists in the real world. And to pile on. The top comment is bad adv…

>As I once read in a Terry Pratchett book; if you replace the handle of an axe and then replace the head, is it still the same axe? Yes and no. It is the Axe of Theseus ;) https://en.m.wikipedia.org/wiki/Ship_of_Theseus

Thanks - I think somewhere in the back of my mind I was also aware of the Ship. But Pratchett is too good. The quote:

> This, milord, is my family's axe. We have owned it for almost nine hundred years, see. Of course, sometimes it needed a new blade. And sometimes it has required a new handle, new designs on the metalwork, a little refreshing of the ornamentation . . . but is this not the... axe of my family?

Re: You'll regret using natural keys

#204

Post by akira2501 »

jimbobthrowawy wrote: post #197
kstenerud wrote: post #172

In databases, never rely on data you don't control. "Natural" keys are an example of this. Names can be natural keys, but you don't control them. You don't control when or how a name changes, or even what makes a valid name. Addresses change. Or disappear. Or somehow can't be ingested by your system suddenly. Official registration numbers (SSNs, license plate numbers, business numbers etc) seem attractive, but once a…

Licence plate numbers are an interesting one, since what those mean varies from country to country. Here (Ireland), they are assigned to the car itself via VIN, are never meant to change once assigned, and are backdated based on information about the vehicle itself (e.g. year of first registration even if first registered in another country, following an older format if applicable), but in other countries they can be…

You don't have vanity plates in Ireland?

Re: You'll regret using natural keys

#205

Post by andrelaszlo »

kstenerud wrote: post #172

In databases, never rely on data you don't control. "Natural" keys are an example of this. Names can be natural keys, but you don't control them. You don't control when or how a name changes, or even what makes a valid name. Addresses change. Or disappear. Or somehow can't be ingested by your system suddenly. Official registration numbers (SSNs, license plate numbers, business numbers etc) seem attractive, but once a…

Official registration numbers, such as Swedish personal identification number, or "personnummer" (date of birth + serial + checksum [Luhn], where even serials are used for females and odd for males):

- It can take a few days before a newborn is assigned a number

- Non-citizens don't have one, but they can get a coordination number on the same format but with the date part incremented by 60 days.

- Citizens can have both a coordination number and a personal identification number in certain cases.

- They can be changed if the wrong birth date or gender registered at birth or during immigration, for protected identities, or for gender transitions.

Re: You'll regret using natural keys

#206

Post by nuancebydefault »

I'm a bit confused. I've never programmed databases, though i have a lot of programming experience.

The student made a structure that can be seen as a unit or table in a database. Is the database not clever enough to synthesize a unique id? Why does the programmer need to care about keys?

It is clear that the end user will not know anything about a key, it might be a byte offset in a file or a memory pointer. Why does the programmer need a key?

Re: You'll regret using natural keys

#207

Post by inopinatus »

strnisa wrote: post #151

I've become a fan of unique, relatively short and "human-readable" IDs, such at the ones used by Stripe, e.g. `cus_MJA953cFzEuO1z` for an ID of a customer. Here's a Stripe dev article on the topic: https://dev.to/stripe/designing-apis-for-humans-object-ids-3... If you use JavaScript/TypeScript, you can make them like this: function makeSlug(length: number): string { const validChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcde…

I'm not convinced that all Stripe IDs are wholly random strings. I just decoded the base62 part of four genuine "acct_" objects, which at 16 characters are just shy of representing a 12 byte value (log2 62^16 =~ 95.3), and they all have a leading byte of "00000011" and two of them even have a leading 32 bits that is very suspiciously close to an epoch timestamp of a couple of years ago.

There is a similarly suspicious pattern in some of their longer identifiers, invoices for example at 24 characters (ca.143 bits) all seem to have a first byte of "00000010".

Even in the article you've linked to, look closely at the IDs:

      pi_3LKQhvGUcADgqoEM3bh6pslE
      pm_1LaXpKGUcADgqoEMl0Cx0Ygg
     cus_1KrJdMGUcADgqoEM
    card_1LaRQ7GUcADgqoEMV11wEUxU
Notice the consistently leading low-integer values (a 3, then three 1s)? and how it's almost always followed by a K or an L? That isn't random. In typical base62 encoding of an octet string, that means the first five or six bits are zero and the next few bits have integer adjacency as well. It also looks like part of the customer ID (substring here, "GUcADgqoEM", which is close to a 64-bit value) is embedded inside all of the other IDs and then followed by 8 base62 characters, which might correspond to 48 bits of actual randomness (this is still plenty, of course).

Based on these values it seems there's a metadata preamble in the upper bits of the supposedly "random" value, and it's quite possible that some have an embedded timestamp, possibly timeshifted, and a customer reference, as well as a random part, and who knows maybe there's a check digit as well.

It's possible - albeit this is not analytical but more of a guess - that the customer ID includes an epoch-ish timestamp followed by randomness or (worst case, left field) is actually a sequence ID that's been encrypted with a 64-bit block cipher and 32 bits of timestamp as the salt, or something similar (pro tip: don't try that at home).

My view is that either Stripe's engineering blog is being disingenuous with the truth of their ID format, or they're using a really broken random value generator. If the latter, I hope it's only in scope of their test/example data.

Re: You'll regret using natural keys

#208

Post by akira2501 »

aswerty wrote: post #101
jklowden wrote: post #27

You think your surrogate key will save you? It will not. The world has an external reality that needs to be reflected in your database. If the unique identifier for your object — VIN, CUSIP, whatever — if it changes, the world will henceforth refer to it by both. You will need to track both. Adding a synthetic key only means you have to track all three. Plus you have to generate a meaningless number, which is actuall…

Surrogate keys do mirror reality though. As I once read in a Terry Pratchett book; if you replace the handle of an axe and then replace the head, is it still the same axe? For me, the answer is yes - since we imbue the axe with an identity outside of it's integral parts. That is what a surrogate key is. An identity. Which is an abstract concept that exists in the real world. And to pile on. The top comment is bad adv…

> since we imbue the axe with an identity outside of it's integral parts.

Typically for the purposes of ownership. So it's really part of a a hierarchical identity scheme.

Re: You'll regret using natural keys

#209

Post by inopinatus »

mettamage wrote: post #194
strnisa wrote: post #151

I've become a fan of unique, relatively short and "human-readable" IDs, such at the ones used by Stripe, e.g. `cus_MJA953cFzEuO1z` for an ID of a customer. Here's a Stripe dev article on the topic: https://dev.to/stripe/designing-apis-for-humans-object-ids-3... If you use JavaScript/TypeScript, you can make them like this: function makeSlug(length: number): string { const validChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcde…

Why is such a thing called a slug?

It's an old typesetting term that found its way into content management systems. https://archive.nytimes.com/www.nytimes.com/times-insider/20...

Re: You'll regret using natural keys

#210

Post by benhurmarcel »

prmoustache wrote: post #175
otherme123 wrote: post #95

Earlier quoted context omitted.

In Spain we have the DNI number, that a lot of people asume is unique, even database designers that use is as a natural key. Turns out the DNI can have, and actually have, a lot of duplicates. The police has a page explaining it ( https://citapreviadnipasaporte.es/dni/dni-duplicados-espana/ ), and how it's not a primary key in their databases, but a number entered manually from a pool of possible numbers. And number…

I've seen banks or insurers use DNI as user login.

All of them do I think. But from experience, they can change it for your account.
Post reply on HN