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

361–370 of 568 posts

Re: You'll regret using natural keys

#361

Post by [deleted] »

sgarland wrote: post #345
mewpmewp2 wrote: post #315

Earlier quoted context omitted.

And then if you want to list other entities to that user you will have to start mapping the external id and foreign relationships every time to external users? And also if you are doing exception logging, for ids/primary keys there's higher odds of them being logged out, including your own logs and also external platforms. It feels like having primary key set up like this just will complicate everything unnecessarily…

The solution I outlined is the one GitLab and PlanetScale both use internally, so it has been tested at scale and works well, for both Postgres (the former) and MySQL (the latter). > It shouldn't contain information about the date, it shouldn't be auto increment, it should really be just random. That’s a great way to tank performance. You want your PK to be k-sortable.

[deleted]

Re: You'll regret using natural keys

#362

Post by hprotagonist »

corobo wrote: post #290
hprotagonist wrote: post #284

Earlier quoted context omitted.

or the journalism term itself diverged from the typographic one.

Aye this is what it seems to be having double checked the reply's claim Got to the Wikipedia page https://en.wikipedia.org/wiki/Slug_(publishing) which could possibly support the slimy conclusion of "it's a trail through the process" but that article has an etymology section that refers to the metal slug I guess it could mean both depending on whether you're looking for the meaning of the word or the meaning of the c…

another fun etymological rabbit hole for you: stereotype and cliché both probably originated as typographer jargon.

Re: You'll regret using natural keys

#363

Post by wavemode »

gregw2 wrote: post #264
wavemode wrote: post #246

Earlier quoted context omitted.

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

The other difference between HIPAA and GDPR is compliance rules. Under GDPR it is not a problem if all your employees who have access to your database have access to PII (since, from the perspective of GDPR, the customer has given consent to share their information with your organization as a whole). Sharing with third parties outside your organization is where you get into trouble.

But under HIPAA, even your own employees need to have a specific, documented justification for accessing customer PHI. If they can do their job just as well by only accessing a non-PHI dataset, you're required by law to only allow them access to a scrubbed, non-PHI dataset.

Re: You'll regret using natural keys

#364

Post by sgarland »

mewpmewp2 wrote: post #356
twobitshifter wrote: post #334

Earlier quoted context omitted.

YANGNI for 99% of projects and databases. When you get to global sharded nosql etc. you need to use UUIDs for anything and incrementing IDs falls over too.

I'm using UUIds by default for everything. Main point being that I don't have to worry about future restrictions. And incrementing IDs are also problematic yes, since they hide business information data within them. And I do think that I need it for much more than 1% of projects and DBs.

You’ll have to worry about performance tanking instead. If you’re using UUIDv7 then less so, but it’s still (at best) 16 bytes, which is double that of even a BIGINT.

Anyone who says UUIDs aren’t a problem hasn’t dealt with them at scale (or doesn’t know what they’re looking at, and just upsizes the hardware).

Re: You'll regret using natural keys

#365

Post by rhelz »

yuriks wrote: post #48
rhelz wrote: post #31

Natural keys are (quite literally) essential to defining entities. Quine had a great slogan for this: "No entity without identity!" Natural keys are how you determine the identity of an object, which is to say, if you have two different referring expressions, how can you tell whether they are referring to the same object, or different objects? Suppose you take the advice of this article, and use, say, social security…

> Suppose you take the advice of this article, and use, say, social security numbers to identify people. You seen to have misunderstood the point of the article: the author is recommending NOT using the SSN (a natural key) for primary keys, and instead to use an artificial, automatically generated key, so that the SSN is decoupled from the record and can potentially be updated.

But SSN's are artificial, automatically generated keys. They are not natural keys, they cannot be natural keys for persons, because not every person has one.

A natural key is a set of attributes which an entity has even if it is not in your database.

Re: You'll regret using natural keys

#366

Post by travisgriggs »

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.

Let’s not get sexist here! It also avoids random d1ck and c0ck sorts of tokens.

Re: You'll regret using natural keys

#367

Post by cimnine »

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…

Yes, and I like to combine two established concepts instead of rolling my own: URI and UUIDv7. So my IDs become `uri:customer_shortname:product_or_project_name:entity_type:uuid`. An example ID could be `uri:cust:super_duper_erp:invoice:018fe87b-b1fc-7b6f-a09c-74b9ef7f4196`. It's even possible to cascade such IDs, for example: `uri:cust:super_duper_erp:invoice:018fe87b-b1fc-7b6f-a09c-74b9ef7f4196:line_item:018fe882-43b2-77bb-8050-a1139303bb65`.

It's immediately clear, when I see an ID in a log somewhere or when a customer sends me an ID to debug something, to which customer, system and entity such an ID belongs.

UUIDv7 is monotonic, so it's nice for the database. Those IDs are not as 'human-readable' for the average Joe, but for me as an engineer it's a bliss.

Often I also encode ID's I retrieve from external systems this way: `uri:3rd_party_vendor:system_name:entity_type:external_id` (e.g. `uri:ycombinator:hackernews:item:40580549:comment:40582365` might refer to this comment).

Re: You'll regret using natural keys

#368

Post by reissbaker »

cogman10 wrote: post #298
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…

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 should be the only key you expose to end-users, to prevent leaking data via the German Tank Problem: https://en.wikipedia.org/wiki/German_tank_problem

Only create the second key if this is data you're exposing to users, of course — for data that's only used internally, just use the 64-bit auto-incremented PK and skip the added index bloat entirely.

Re: You'll regret using natural keys

#369

Post by ironchef »

nostrademons wrote: post #340

There's a better solution for many of the exceptional cases that the author describes: aliases & audit logs. Take for example the Danish CPR number. That's perfectly fine as a natural key; its definition is the first CPR number assigned. 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 tha…

You appear to be describing a classic type 2 or type 4 slowly changing dimension. (https://en.wikipedia.org/wiki/Slowly_changing_dimension )

Re: You'll regret using natural keys

#370

Post by gwbas1c »

mikedelfino wrote: post #268
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?

You can get kind-of close with driver's license numbers. They will change when US citizens move among states, (and we do that quite frequently.)

(People who don't drive have to get state-issued IDs that have the same number. At least, they have to if they want to buy do anything that requires proof of identification.)

Post reply on HN