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

481–490 of 568 posts

Re: You'll regret using natural keys

#481

Post by parpfish »

sgarland wrote: post #457
parpfish wrote: post #52

Earlier quoted context omitted.

That helps part of it, but there are still places for problems to pop up. - you need to do an adhoc query to look something up so you have to type in the key in a where clause - Or you used different sanitation methods in two different databases and you need to join things now - you try to join the table that had the sanitized email key to a different table that just so happens to have email but it wasn’t sanitized b…

> you need to do an adhoc query to look something up so you have to type in the key in a where clause Having a surrogate key here doesn’t help. A WHERE predicate can be rewritten as an INNER JOIN quite easily. Or you could use a subquery, or a CTE. Many options. The other problems discussed are an engineering culture problem. Either you value correctness and consistency or you don’t.

not sure how converting a WHERE to a JOIN helps when you're doing adhoc queries like this to look up records:

`SELECT * FROM users WHERE email LIKE 'FirstName@domain.net'`

Re: You'll regret using natural keys

#482

Post by shkkmo »

randomdata wrote: post #473
shkkmo wrote: post #459

Earlier quoted context omitted.

SSN is absolutely not a surrogate key. If you received a piece of information from an external source, it is data, not a surrogate key. If you use data as a key, then that is a natural key, if you invent a value to use as an identifier, that is an artificial or surrogate key. If an API provides you an ID for a record, that is data. If you use it as a key, that is also a natural key in your system.

> If you received a piece of information from an external source, it is data, not a surrogate key. It may not be your surrogate key, but it is someone's!

Possibly, you don't actually know since it is external data.

Re: You'll regret using natural keys

#483

Post by randomdata »

shkkmo wrote: post #482
randomdata wrote: post #473

Earlier quoted context omitted.

> If you received a piece of information from an external source, it is data, not a surrogate key. It may not be your surrogate key, but it is someone's!

Possibly, you don't actually know since it is external data.

We do know because the database owner has openly talked about how the keys are derived. That still doesn't make it a good key for your database, but I can assure you that the world doesn't revolve around you. It is someone's surrogate key – therefore it is a surrogate key.

Re: You'll regret using natural keys

#484

Post by cogman10 »

senderista wrote: post #479
reissbaker wrote: post #368

Earlier quoted context omitted.

IMO it's nice to have two keys: 1. An auto-incremented 64-bit (unless you have a good reason, in which case 32-bit is fine) primary key, used internally for foreign key relations. This will generally result in less index bloat on associated tables, and fast initial inserts. 2. A public-facing random string ID. Don't use this internally (other than in an index on the table it's defined for), since it's large. But this…

I don't understand why you need to maintain two separate keys: instead of generating a random key, why not just encrypt the auto-increment key using a secret key? This is the approach used by e.g. cloud providers that use auto-increment keys internally but don't want them to be guessable.

I think the biggest problem with this approach is it effectively pins you to a encryption key and algorithm (unless you embed some information in the key that lets you version the key, gotta think of that upfront).

Imagine, for example, that you picked DES and "kangaroo" as the secret several years back. You are now pinned to an algorithm and key with known security problems and a weak key.

Re: You'll regret using natural keys

#485

Post by jimbobthrowawy »

akira2501 wrote: post #204
jimbobthrowawy wrote: post #197

Earlier quoted context omitted.

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?

You can get vanity numbers, but 1 is reserved for the region's mayor, and anything 1000 or below is assigned based on a raffle system. Other than that, it's first come first served.

It can only go on that one car though, so there's not as much value in doing it. I've mainly seen bus rental companies do it.

Re: You'll regret using natural keys

#486

Post by konschubert »

bjourne wrote: post #289
konschubert wrote: post #251

Earlier quoted context omitted.

You can use a UUID as a primary key and still enforce uniqueness on a combined index of city and name.

But what's the point? You're complicating the data for no apparent gain. Only terrible RDBMSes which don't support multi-column keys require surrogate keys.

Because you can remove or change the uniqueness constraint without having to worry about foreign key relationships.

Re: You'll regret using natural keys

#488

Post by teknopaul »

Don't see the problem with ssn as I'd. People have two, get two rows in your database. Not the end of the world often. database it's a tool thus limitation is not an error. E.g. maybe trans peeps want to forget their old ID, maybe not. your own key has no chance of reflecting any reality in the world.

Re: You'll regret using natural keys

#489

Post by hobs »

The_Colonel wrote: post #77
hobs wrote: post #49

Earlier quoted context omitted.

The main thing is that the synthetic key should never leave the database and never be displayed in the app - if you want to have another key that represents the human oriented key do it, but it should be another field, an indexed field even, but one that has a lot less monotonic sequential properties that are inherent to synthetic database identifiers. You want to change that human key? Sure. You want to to complain…

I assume you hint at the security aspect of monotonic keys? I've found this issue a bit overblown. It's basically security by obscurity, which is a nice bonus, but not something your security model can be based on. I mean, it is a good practice to expose some kind of non-sequential key (e.g. UUIDv7), but it doesn't seem to me like a dealbreaker.

Sort of, I have way more experience with clients saying that an invoice is missing (which turned out to be a rolled back transaction) and then I get to explain how transactions work, and then if the customer is smart enough they'll say something like "then why the hell is the invoice number part of that?"

Re: You'll regret using natural keys

#490

Post by CodesInChaos »

whyever wrote: post #169
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…

Please don't use % to generate integers from a range, it's not uniform, which can be disastrous if you rely on your numbers not being predictable. You can use crypto.randomInt instead.

That is definitely an improvement, but I find that concern a bit exaggerated.

If all you need is unpredictability, a minor bias is sub-optimal but not disastrous. The 256 % 62 bias used here should reduce the min-entropy per character by 5%. And you can easily minimize the bias by using larger integer than a single 8-bit byte. There few algorithms where minor biases cause a disaster, like DSA.

Post reply on HN