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

391–400 of 568 posts

Re: You'll regret using natural keys

#391

Post by trevor-e »

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…

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.

Re: You'll regret using natural keys

#392

Post by chuckadams »

phkahler wrote: post #378
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…

Something like that should have a built in check "digit" if people are going to see it and possibly type it in. For numeric values, making them all a multiple of 11 is a simple way to catch all single digit errors or single transpositions.

That's why VINs make a decent natural key, because they do have a check digit. Plus they're not opaque: if you look up the VIN and the make/model/year is completely different than the car in front of you, you know you either have the wrong VIN or the wrong car.

Re: You'll regret using natural keys

#393

Post by thatjoeoverthr »

One may notice this has something to do with mutability. If there isn’t a surrogate key, the record isn’t mutable. The database may let you change it, but the new record has a new identity. Mutability as a concept requires a common identity across time. Languages permitting mutability are using a pointer or reference as an implicit surrogate identity. A typical database can’t offer this, hence the need to put explicit surrogate keys into the schema. You cannot say “this changed” unless you can refer to both samples as a common “this”.

Re: You'll regret using natural keys

#394

Post by theelous3 »

Another counter example is when the thing you are recording literally _is_ the natural key. Phone numbers are a good example of this. There is no situation in which some data entry error will break a number. That is the creation of a new distinct (possibly) broken number. A phone number doesn't care about any externality in the world. It is what it is.

Re: You'll regret using natural keys

#395

Post by mrkeen »

sgarland wrote: post #332
mrkeen wrote: post #142

Earlier quoted context omitted.

> surrogate keys can be fixed size integers This launches into the other debate about PKs: using UUIDs rather than sequential keys.

This is less of a debate, and more of an indicator of who has had to work with a DB at scale using UUIDv4 everywhere. Don’t blow up your B+trees.

I guess.

Either your system is happy enough to route every new entity through "one DB at scale" so it can let your "one DB at scale" be in charge of an auto-incrementing long, or it isn't.

Re: You'll regret using natural keys

#396

Post by chuckadams »

vincentdm wrote: post #178
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 like the prefix idea: besides the duplication of information, it also becomes a liability if you ever rename things. Imagine you prefix all customer IDs with `cus_`, but at some point decide to rename Customer to Organization in your codebase (e.g. because it turns out some of the entities you are storing are not actually customers). Now you have some legacy prefix that cannot be changed, is permanently out o…

I have that exact issue with a couple different identifiers, and it's not a big deal. Usually it goes along with some data model change you already have to write compatibility code for, the new and old names tend to be related, and the old name tends to stick around other parts of the code anyway. Opaque IDs don't reduce the confusion there, documentation in appropriate places does.

Re: You'll regret using natural keys

#397

Post by banish-m4 »

Use db-generated UUID "id" primary keys. Add a bigserial "order" field to tables that require stable temporal ordering, and then either create a view to "order by" it or always add "order by" to stable-required statements.

Re: You'll regret using natural keys

#398

Post by chuckadams »

sgarland wrote: post #364
mewpmewp2 wrote: post #356

Earlier quoted context omitted.

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

Most databases with a UUID type store them as 128-bit integers, typically the same as a BIGINT. It's not like 378562875682765 is the bit representation of a bigint either. And if you're not using uuidv7 or some other kind of cluster-friendly id, you'd best be using a hash index, and if you're doing neither, you probably don't care about their size or performance anyway. You don't pick UUIDs blindly, but on balance, they solve a lot more problems than they cause.

Re: You'll regret using natural keys

#399

Post by dwheeler »

I've found that using a simple unique integer id as the key usually works well. Integers are easy to explain and are simpler to enter than uuids. There are always ttade-offs, but if there are no other compelling issues, simplicity is best.

Re: You'll regret using natural keys

#400

Post by brigandish »

lolinder wrote: post #390
brigandish wrote: post #389

Earlier quoted context omitted.

Do you often send the auto-incremented int (that would be the default substitute to this) when communicating with others? Then why would you send this? It's so strange an argument. Right now you have my username but not my email address, yet you can still query the website database and get certain data that you're allowed to see. There are so many ways to query a particular user's data, and they would all depend on w…

> Do you often send the auto-incremented int (that would be the default substitute to this) when communicating with others? It's not an int, but yes, we have a unique synthetic identifier that serves as the database PK and as a means of communicating about a customer in insecure channels without exposing PII. "Customer ID ### is having an issue with such-and-such." To turn your second part back around: why a natural…

> To turn your second part back around: why a natural key? What is the function of minting a natural key if humans are meant to use something else?

Because non-natural keys are unnecessary in the presence of a natural key, and unnecessary things bring in complexity.

> "Customer ID ### is having an issue with such-and-such."

Then you need access to the customer's ID, but the devil here is in the detail you didn't add, the such-and-such.

> communicating about a customer in insecure channels

Use secure channels…

Post reply on HN