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

451–460 of 568 posts

Re: You'll regret using natural keys

#451

Post by lolinder »

nostrademons wrote: post #419
phkahler wrote: post #376

Earlier quoted context omitted.

>> If a person's CPR number changes because they've changed their gender, you will want a separate table recording a.) the date of the change. The new CPR number is not valid before that time b.) the new gender c.) probably the reason for the CPR number change, since if the policy now is that they can change because of a gender change, there's a decent chance they'll be some other policy in the future that results in…

You need this complexity anyway - if you had surrogate keys, the primary key would be an opaque identifier, and to do anything involving the CPR, you'd need to join against your CPR table (which, again, needs to be 1:many because CPRs themselves are not a 1:1 relationship). The first CPR in this case becomes identical to your surrogate key - it's an opaque ID that you use to reference other tables in the DB - but wit…

> The first CPR in this case becomes identical to your surrogate key - it's an opaque ID that you use to reference other tables in the DB - but with the added benefit that for the common case, you don't need any additional lookups. You only need to lookup CPR changes if you don't find the CPR that the user gave you.

I'm not buying that this is a meaningful gain in either performance or code complexity.

In the world of synthetic keys I look up the CPR in the CPR table and join it to the user table using the synthetic ID. If I find a record for the CPR+user join then I'm set, if I don't then the customer doesn't exist.

In the world of natural keys, you're advocating that I first query the user table directly by CPR. Then if I turn nothing up I run a separate query with a join on the original CPR. Then if I still don't turn something up the customer doesn't exist.

The code in the second instance is obviously more complicated than the code in the first instance. It has increased risk of someone writing a bug because now there's a very tempting CPR field that will be right most of the time but wrong in some edge cases. Depending on the database and usage patterns, indexing on the CPR may be much less efficient than indexing on an autoincrementing integer.

The only thing it has going for it is that I might be able to avoid a single join on a very specific path where a user is looking up a customer by typing in the CPR. That seems like the wrong thing to optimize for in the face of all the downsides.

Re: You'll regret using natural keys

#452

Post by Piskvorrr »

Karellen wrote: post #429
trevor-e wrote: post #391

Earlier quoted context omitted.

URLs can be tricky and have plenty of gotchas depending on what you're trying to do. For example, the order of query params is free to change but it's still the same URL. Nothing that can't be worked around with a little normalization.

> For example, the order of query params is free to change but it's still the same URL. Are you sure that's guaranteed by any spec? I thought an end-point would be free to treat `?a=1&b=2` and `?b=2&a=1` differently. I mean, it would be a nightmarish implementation, but I don't think it would be non-conforming?

It's a mere convention: "a querystring of this type is an array of parameters, the order of which is irrelevant" - this is one of the tautological "valid except when it's not" non-rules (a surprising number of cases).

Example: ?a=1&b=2&a=3 - will the server treat this to be equivalent as ?a=3&b=2&a=1 , or ?b=2&a=1 , ?b=2&a=3 , or something else entirely? You'd need to check the serverside parsing implementation to be sure - those could even be valid (and distinct) filenames FWIW.

(And that's before you get to caching - "?b=2&a=1 is not to be served as a cached version of ?a=1&b=2")

Re: You'll regret using natural keys

#454

Post by jrs235 »

sgarland wrote: post #303
jrs235 wrote: post #243

Earlier quoted context omitted.

Also are emails case sensitive or not? In some systems (that you don't control mind you) they are and others they are not...

Per RFC5321, the local part (before @) _may_ be case-sensitive, but in practice, it almost never is, and relying on case sensitivity is a recipe for disaster. The domain must always be case-insensitive.

My point being is if you're using email as a key you "have to" treat it as case sensitive even though for most it's not. And yes, I agree it will be a recipe for disaster.

Re: You'll regret using natural keys

#455

Post by cogman10 »

groestl wrote: post #443
cogman10 wrote: post #432

Earlier quoted context omitted.

Well, you'd still likely want psuedo-random keys. You'd rather not have the underlying database doing extra work to shuffle around records as the pages get jumbled. One solution to that is having more complex keys. For example, in one of our more contentious tables the index includes an account id (32bit int) and then the id of the entity being inserted. This causes inserts for a given account to still be contiguous…

Not disagreeing. Point is, you need to know your domain, your technology, your write patterns, your downstream systems, etc to decide if a specific key scheme works to your advantage or not. All the more reason not to use natural keys, as they lock you in in that regard.

Absolutely agree.

I don't know how you can successfully maintain or develop software without developing an understanding of the underlying domain. I've seen devs try that route and the quality of their work has never been high.

Re: You'll regret using natural keys

#456

Post by kstenerud »

ss64 wrote: post #448
kstenerud wrote: post #316

Earlier quoted context omitted.

I developed safe32 for this reason. https://github.com/kstenerud/safe-encoding/blob/master/safe3... Notably, confusable characters are interchangeable when being ingested (although a machine encoder MUST always produce canonical output). https://github.com/kstenerud/safe-encoding/blob/master/safe3... So a user can confuse 1 for l, 0 for o, I for l, u for v, uppercase, lowercase etc, or the agent can say any of those…

Another approach is using base31 with all vowels removed https://ss64.com/ps/syntax-base31.html

You still have the problem of 1 vs l. And also it doesn't support user-error (reading 0 as O, or reading V as U).

Re: You'll regret using natural keys

#457

Post by sgarland »

parpfish wrote: post #52
Cyph0n wrote: post #33

Earlier quoted context omitted.

Ideally, you should aim to sanitize/normalize strings on the write side rather than resanitizing on every read.

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.

Re: You'll regret using natural keys

#458

Post by kstenerud »

what wrote: post #421
kstenerud wrote: post #316

Earlier quoted context omitted.

I developed safe32 for this reason. https://github.com/kstenerud/safe-encoding/blob/master/safe3... Notably, confusable characters are interchangeable when being ingested (although a machine encoder MUST always produce canonical output). https://github.com/kstenerud/safe-encoding/blob/master/safe3... So a user can confuse 1 for l, 0 for o, I for l, u for v, uppercase, lowercase etc, or the agent can say any of those…

Isn’t this just crockford encoding?

Every base-32 style encoding is Crockford at the core. The difference is in the alphabet, and also whether it requires padding or not (safe32 does not).

Crockford also incorporates error correction, which is unnecessary in modern systems since the underlying protocols do that already.

Re: You'll regret using natural keys

#459

Post by shkkmo »

randomdata wrote: post #438
gregw2 wrote: post #264

Earlier quoted context omitted.

Thanks for the reply. I will concede or defer to you in regards to PHI and HIPAA... it seems the philosophy behind HIPAA/PHI is very different than PII or GDPR. HIPPA is prescriptive. PII/GDPR are principle-based. HIPAA, it seems, has some text that it's not PHI if the risk is "very small" based on the opinion of someone with statistical expertise documents that it could be de-identified OR if the person avoids an ex…

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

Re: You'll regret using natural keys

#460

Post by Piskvorrr »

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.

And I've been bitten by this design numerous times. The most common bug: literally the birthday paradox, i.e. "these two people have been assigned the same number, and now we need to distinguish them in our database".
Post reply on HN