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

261–270 of 568 posts

Re: You'll regret using natural keys

#261

Post by nkozyra »

konschubert wrote: post #251
bjourne wrote: post #249

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 t…

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

If they conflict and are user facing identifiers aren't you then forced to add uniqueness to the city and name, ala /chicago/chipotle_s4, potentially bleeding some business details?

I don't love uuids as public identifiers for a number of reasons but not hinting details about your data is one nice thing about them.

Re: You'll regret using natural keys

#262

Post by nottorp »

mths wrote: post #23
ray_v wrote: post #9

Feels like this could have used a few more solid examples up-front. I think another good example would be PlayStation Network using the natural key of "gamer tags" as the primary key to identify players would be a good example. Since this effectively locks players into having to keep a gamertag in order to uniquely identify them in the service -- instead of having a synthetic key that carries no meaning or data other…

Last I checked, Steam still has me logging in with my two decade old hotmail address as my account name. At least it's not something that shows publicly, I think.

I wouldn't swear it, but I think when I changed my Steam email the process didn't require access to the old email, just knowing the user and pass.

Re: You'll regret using natural keys

#263

Post by scrollaway »

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 don’t recommend using random bytes for this. What I have done in my previous projects is take a uuid6, reserve a couple of bytes inside of it to replace with an object ID, and I convert that to obj_XXXXXXX.

This means you can store them in the db not as a string but as a uuid, which is a lot more performant. You also get time stamping for free.

Re: You'll regret using natural keys

#264

Post by gregw2 »

wavemode wrote: post #246
gregw2 wrote: post #200

Earlier quoted context omitted.

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 n…

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 explicit list of 18 things that it cares about (see items (A) through (R) on page 96 of https://www.hhs.gov/sites/default/files/ocr/privacy/hipaa/ad... ).

One of the first 17 things might be a surrogate key ("account number") in one's system but if you look through the others, the rest are things like name, SSN, biometrics, IP#s, etc, which are definitely not surrogate keys.

The "OR" language makes the statistical expertise (and "principles" of privacy) irrelevant if you avoid the 18 things; that avoidance forms a "safe harbor" of sorts so you don't have to do any heavy thinking/lifting.

The 18th ("(R)") element of what is considered PHI does seem to refer to surrogate keys but in a manner which creates a clear carve-out/safe-harbor for them not being PHI. That 18th form of PHI is "Any other unique identifying number, characteristic, or code, except as permitted by paragraph (c) of this section;"

But that paragraph (c) section seems indicate identifiers such as surrogate integer/guid keys kept within a system, as long as they are 1) not derived from an individual's information (ie like integer or UUID surrogate keys) and 2) which are maintained solely in the system are not considered as element-18-"R"-PHI:

"(c) Implementation specifications: Re-identification. A covered entity may assign a code or other means of record identification to allow information de-identified under this section to be re-identified by the covered entity, provided that: (1) Derivation. The code or other means of record identification is not derived from or related to information about the individual and is not otherwise capable of being translated so as to identify the individual; and (2) Security. The covered entity does not use or disclose the code or other means of record identification for any other purpose, and does not disclose the mechanism for re- identification."

By my reading, a surrogate key maintained within a system is thus clearly not PHI under HIPAA. I never looked at the details of HIPAA until today since it hasn't applied much to my data and have been focused more on PII/GDPR. I appreciate you describing the context of your remarks.

Re: You'll regret using natural keys

#265

Post by 8372049 »

corobo wrote: post #247
meindnoch wrote: post #244

Earlier quoted context omitted.

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!

I'm not sure you're hallucinating. The dictionary I checked lists the printing and journalism terms separately. It's quite possible they have diverging etymologies, meaning both can be correct:

5. Print. a. a thick strip of type metal less than type-high. b. such a strip containing a type-high number or other character for temporary use. c. a line of type in one piece, as produced by a Linotype. 8. Journalism. a. a short phrase or title used to indicate the story content of a piece of copy. b. the line of type carrying this information.

Re: You'll regret using natural keys

#266

Post by jgalt212 »

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

Or just drop the L and O, like CUSIP does.

Re: You'll regret using natural keys

#267

Post by konschubert »

nkozyra wrote: post #261
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.

If they conflict and are user facing identifiers aren't you then forced to add uniqueness to the city and name, ala /chicago/chipotle_s4, potentially bleeding some business details? I don't love uuids as public identifiers for a number of reasons but not hinting details about your data is one nice thing about them.

Hmm, I am not sure if I get you.

The cool thing about enforcing uniqueness on a secondary index is that you can just change or remove the uniqueness constraint anytime without breaking foreign key relations.

Re: You'll regret using natural keys

#268

Post by mikedelfino »

Kon-Peki wrote: post #30

> how about a personal identification number? In Denmark we have the CPR number, and I understand that the US Social Security Number is vaguely analogous. The US SSN is not guaranteed to be unique, the SSN assigned to a person could change, there is no guarantee that a person with an SSN assigned to them is a US citizen, and there is no guarantee that a US citizen has an SSN - they must be requested, and you don’t ne…

How to uniquely identify an American citizen?

Re: You'll regret using natural keys

#269

Post by zarzavat »

kassner wrote: post #254
meekaaku wrote: post #236

I am firmly in the control your primary key, preferably uuid. Quick question for database gurus here. Is it ok to have a currency table using the currency code as primary key?

Likely no, there are currencies without an ISO 4217 code, i.e.: https://en.wikipedia.org/wiki/Faroese_kr%C3%B3na

This is interesting but it doesn’t really seem like a separate currency. It’s more like Scottish pound vs English pound, it’s the same currency but different banknotes for different regions in the same state.

Re: You'll regret using natural keys

#270

Post by bradleyankrom »

ourmandave wrote: post #232
yashap wrote: post #97

Earlier quoted context omitted.

This is bad advice. Say I have a user table, and the email is unique and required, and we don’t let users update their email, and we don’t have user deletion. If I’m going natural PK, I make email the primary key. But … then we add the ability for users to update their email. But it should still be the same user! This is trivial if we have a surrogate primary key, a nightmare if we made email the natural primary key.…

If I’m going natural PK, I make email the primary key. Welcome to the Mr. Cooper mortgage provider website. Your logon is your email and you can't change it. If you used your cable provider email you're stuck with them for the life of your 30 year mortgage.

Oh, also, we've been breached and your information is available for purchase on the dark web. Fun!
Post reply on HN