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

471–480 of 568 posts

Re: You'll regret using natural keys

#471

Post by bigstrat2003 »

vsuperpower2020 wrote: post #420
coooolbear wrote: post #416

Earlier quoted context omitted.

By ‘find an article’ you mean find ~10 real citations including the resolution of an authority a long time ago and to tell the reader it is not clear or definitive? Better to be careful and let any individuals or communities tell you what they want. I have Roma connections in my family and at one point the word we’d use is ‘gypsy’. But, because I’m not Roma myself, if I came across some other group I wouldn’t assume…

I don't care what they want. A lot of people are tired of playing these language games.

Also, "do what people want" is fine for your interactions with an individual. But it's not a viable general rule for language, where we need one single approach. I think saying gypsy unless someone personally tells you they would rather you don't call them a gypsy is perfectly reasonable.

Re: You'll regret using natural keys

#472

Post by fnordsensei »

ceejayoz wrote: post #318
fnordsensei wrote: post #281

Earlier quoted context omitted.

One thing I’ve been looking for in an ID generator is a way to supply a blocklist. There are a number of character combinations I’d like to avoid in IDs, because they might be offensive or get stuck in filters when copy-pasted (e.g. in a URI). This can be solved in user space by regenerating if the character sequences are detected, but this a) skews the distribution, and b) potentially takes time, especially when the…

Just take out the vowels and numbers that can look like vowels. Nixing 0 means no b00bs IDs, and avoids 0/O; I usually take out 1/I as well.

That goes some of the way, but I can think of a few problematic sequences that are only consonants and/or numbers.

Re: You'll regret using natural keys

#473

Post by randomdata »

shkkmo wrote: post #459
randomdata wrote: post #438

Earlier quoted context omitted.

> SSN ... which are definitely not surrogate keys. Surely SSN is a surrogate key? They are not naturally derived. The early ones were serial (i.e. an auto-incrementing field) and more recent ones are randomly generated (i.e. a UUID).

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!

Re: You'll regret using natural keys

#474

Post by mypalmike »

crabmusket wrote: post #114
mypalmike wrote: post #106

Earlier quoted context omitted.

Conversely, certain queries can be much faster by using natural keys when the FK is all that you need in the result rather than additional fields in the primary table. In this case, the primary table doesn't need to be queried at all. This doesn't generally overcome the benefits of synthetic keys, but it is an optimization sometimes put into practice.

Aren't you literally describing an index?

No. Maybe an example helps. You have a users table with username as natural PK. You have an access table with timestamps of users hitting a service, with FK of username. If you query the access table for a range of timestamps and just want the usernames, they're right there in the results of the access table query. If you had instead used a synthetic user_id key, the db would have to do an additional lookup in the users table via a join for each user_id to get the usernames.

Re: You'll regret using natural keys

#475

Post by madcaptenor »

ceejayoz wrote: post #317
strnisa wrote: post #220

Earlier quoted context omitted.

Thanks for all the improvement suggestions! Taking them into account, the `makeSlug` function becomes: function makeSlug(length: number): string { const alphabet = "0123456789abcdefghjkmnpqrstvwxyz"; let result = ""; for (let i = 0; i

I like to nix vowels and things that look like them, i.e. 0, to avoid random b00bs sort of tokens.

Sure, but I can see people still getting offended if "fck" showed up.

Re: You'll regret using natural keys

#476

Post by naasking »

Piskvorrr wrote: post #463
naasking wrote: post #325

Earlier quoted context omitted.

> I once made the mistake of using an external ID as a primary key. What a day it was when they were changed on me. I've kept with this advice for the most part, but I'm tempted in some cases to use the external id when there's some guarantee of stability and universality. Like 2 and 3 digit ISO country codes.

Not that I'd get about 5 different ISO country code changes (with some flipflopping) just by sitting in this very same spot for a couple of decades. "Stability" in country codes, bah humbug.

Your geographical location's sorting into a country might not be stable, but I'm referring to the ISO codes that name the country, which should be relatively stable.

Re: You'll regret using natural keys

#477

Post by naasking »

notfed wrote: post #431
naasking wrote: post #285

Earlier quoted context omitted.

> For the record: the valid chars string is 62 characters, so naively using a modulo on a random byte will technically introduce a bias Indeed, there's no reason you couldn't just add "_" and "-" or "." as well to complete the set. Your identifier will still be URL-safe. I've been using this type of encoding for years [1] for these kinds of ids to use in URLs, and encoding/decoding is super-fast with some bit shifts.…

- and _ tend to break text selection.

I'm not sure what you mean by "break". If you mean that touching or double-clicking on a block of text only extends up to the nearest symbols, that's true. But if your text selection UX is not terrible then it should be simple to extend that further.

That said, iOS and Android text selection have gotten worse recently, IMO.

Re: You'll regret using natural keys

#478

Post by codr7 »

Piskvorrr wrote: post #444
fhars wrote: post #312

Earlier quoted context omitted.

That is "AT", isn't it? (No, it isn't, if you look closely enough.)

It's obviously a �. Or perhaps a □ . Maybe an ¾, on odd Wednesdays? (Unicode has its strengths. Making up replacement characters isn't one.)

I happen to know that the biggest ski resort reservation system in Scandinavia contains a function called MaybeOnATuesday(), but to my knowledge it's never called.

Re: You'll regret using natural keys

#479

Post by senderista »

reissbaker wrote: post #368
cogman10 wrote: post #298

Earlier quoted context omitted.

A problem with this approach is it's not monotonical. Especially if you want to use this thing as an index in a database, you'll run into problems where you try doing middle insertions frequently, which causes fragmentation. The solution to this problem is making the higher order characters time sorted [1]. You don't need to go all out like uuid, you can have a pretty low resolution. It's more important that new inse…

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.

Re: You'll regret using natural keys

#480

Post by senderista »

rav wrote: post #450
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…

Instead of a random string ID, you can devise a fixed secret key and expose the auto-incremented ID xor the fixed secret key as the public-facing ID. This saves you the separate index but still avoids the German tank problem. But it gives you a new problem, namely a secret that's hard or impossible to rotate.

XOR isn't secure enough, but you're on the right track. Instead, use an actual block cipher.
Post reply on HN