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

241–250 of 568 posts

Re: You'll regret using natural keys

#241

Post by smaudet »

biztos wrote: post #231
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 think that 0 and 1 are likely to cause problems when customers end up reading their "user ID" back to your employees in Customer Support Country over the phone. "It's one-three-oh-dee-ee-el. Yes, I'm sure, EL as in elephant."

I wonder if Unicode could be used to alter the characters such that these mistakes would be less possible, e.g. using ⓪.

Re: You'll regret using natural keys

#242

Post by nicce »

mootothemax wrote: post #167

You can get performance benefits from using natural keys, as many databases store rows in the order of a table's primary key (sometimes called the clustered index, though it may or may not have a unique constraint requirement depending on the DBMS and a few other bits). In the author's example, if the first column in the natural index was the city name (or city ID!), and locations are often pulled from the database b…

> This is why UUID-based systems can suffer worse read + write performance; their rows will be stored in the order of its UUIDs (that is, randomly spread around), making read, insert, and update performance lower.

This isn't always the case anymore. UUID standards have been developed in that mind, and they are not completely random anymore. UUIDs can give a hint, for example, about the time when it was created, which gives them some order.

Re: You'll regret using natural keys

#243

Post by jrs235 »

epcoa wrote: post #176
knallfrosch wrote: post #133

Earlier quoted context omitted.

> then we add the ability for users to update their email. At this point, you should verify the new email. At least until it is verified, you must track the old email. At this point, you realize you can now introduce a synthetic key and you're fine. Let's say you have a duplicate customer entry and the customer demands their accounts be merged. Now you can't identify the user by their key alone, since by definition,…

> At this point, you realize you can now introduce a synthetic key and you're fine. Except for having to update all foreign references. Some of those may be external further complicating issues. Emails are often among the worst keys because they are not terribly stable and they are reusable often enough to burn you.

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

Re: You'll regret using natural keys

#244

Post by meindnoch »

corobo wrote: post #234
mettamage wrote: post #194

Earlier quoted context omitted.

Why is such a thing called a slug?

Ooh I had this wonder a while back and jotted it down just in case anyone else ever wondered about it: Comes from the ye olde paper-based blogs they call newspapers. When an article is being put together it’s given a short name, sort of like a project name. This name would remain the same throughout the article’s life - from reporter through to editor - it left its trail through the process. Like a slug.

That's not what Wikipedia says about its etymology though.

>The origin of the term slug derives from the days of hot-metal printing, when printers set type by hand in a small form called a stick. Later huge Linotype machines turned molten lead into casts of letters, lines, sentences and paragraphs. A line of lead in both eras was known as a slug.

Re: You'll regret using natural keys

#245

Post by kassner »

winternewt wrote: post #155
delusional wrote: post #122

Earlier quoted context omitted.

We have the same problem in Denmark, most people just don't realize it. At my dayjob we get at least one person every year who changes gender and consequently gets a new SSN (the final digit is supposed to signify gender). Most people don't store SSNs so they never realize, but it does happen fairly frequently.

If Denmark is anything like Sweden there's also: - SSN from different ID space gets assigned to immigrants; when they become citizens they are assigned a new permanent SSN. - SSN:s have a long and a short form; the short form which cuts off century information can be the same for someone who is 5 years old and someone who is 105 years old. - When an unconscious patient comes in to the E.R. you don't know their SSN, s…

Also: the personnummer carries a date that often means birth date, but there are cases where it’s not, but I’ve seen a few system that just assumes it’s the same.

> SSN from different ID space gets assigned to immigrants; when they become citizens they are assigned a new permanent SSN

Having gone through that, my personummer didn’t change. Maybe that doesn’t happen anymore?

Re: You'll regret using natural keys

#246

Post by wavemode »

gregw2 wrote: post #200
wavemode wrote: post #43

Another massive annoyance with natural keys - privacy. If your table's primary key contains personal information, that PII now infects every other table that holds a foreign key to that table.

Thank you for your comment. It spurred me to think about this issue more thoroughly in a way I hadn't, even though this comment may disagree -- to some extent -- with your perspective. I have not seen clear guidelines about whether an organization's surrogate keys for persons are considered PII. (And this ambiguity has frustrated me for some time as I am unclear whether to take an aggressive or conservative view on l…

Your perspective is very thorough and interesting, but I can simplify the matter a lot - yes, from my personal experience it absolutely does matter for privacy regulation whether you use natural or surrogate keys.

I've worked on systems in the biotech space where certain tables and certain columns in our dataset were considered PHI (personal health information), and others weren't. An auto-incrementing user ID does not qualify as PHI under HIPAA. Whereas a person's name or email address most certainly does (since, the very fact that a particular person had received lab testing at a particular lab, is itself considered PHI.)

Those PHI bits of data were scrubbed from the dataset that most regular employees had access to. Casually allowing all employees to have access to PHI would not have been compliant with the law.

The restrictions on what can even be logged (when there's a system error, for example) was very much controlled. "User 12345 experienced " was perfectly fine (and greatly aided operational investigation, and customer support.)

Re: You'll regret using natural keys

#247

Post by corobo »

meindnoch wrote: post #244
corobo wrote: post #234

Earlier quoted context omitted.

Ooh I had this wonder a while back and jotted it down just in case anyone else ever wondered about it: Comes from the ye olde paper-based blogs they call newspapers. When an article is being put together it’s given a short name, sort of like a project name. This name would remain the same throughout the article’s life - from reporter through to editor - it left its trail through the process. Like a slug.

That's not what Wikipedia says about its etymology though. >The origin of the term slug derives from the days of hot-metal printing, when printers set type by hand in a small form called a stick. Later huge Linotype machines turned molten lead into casts of letters, lines, sentences and paragraphs. A line of lead in both eras was known as a slug.

Ooh interesting. It looks like I have either misinterpreted or found a source that misinterpreted (it was a few years back, unsure if I came to the conclusion or found it). I'll have to update my notes, cheers!

Apologies for the wetbrain hallucination, HN!

Re: You'll regret using natural keys

#248

Post by thefz »

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…

Been vehemently against surrogates my whole life, glad to find a kindred soul.

Re: You'll regret using natural keys

#249

Post by bjourne »

Hard disagree. You create tables for them to be queried by applications. For the Restaurants example the application clearly wasn't designed to handle Restaurant entries whose only field differs is rank. Consequently, you shouldn't allow data the application can't handle. Whenever the time comes and you think "ah, wouldn't it be great if multiple people could review the same restaurant in the same year?" you change the natural keys. You can always relax the rules. Tightening the rules is much harder because garbage data may already be present.

Keys are about the integrity of your application(s) and preventing corner cases by making them impossible.

Re: You'll regret using natural keys

#250

Post by gvx »

biztos wrote: post #231
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 think that 0 and 1 are likely to cause problems when customers end up reading their "user ID" back to your employees in Customer Support Country over the phone. "It's one-three-oh-dee-ee-el. Yes, I'm sure, EL as in elephant."

Anything that needs to be read over the phone should probably be written out using something like the NATO phonetic alphabet, split into smaller chunks if needed: "The code? It's kilo eight niner; one three mike; delta echo lima."
Post reply on HN