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

401–410 of 568 posts

Re: You'll regret using natural keys

#401

Post by psychoslave »

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…

To my mind, it always felt so saddening that adoption of a truly straightforwardly readable notation for numbers never took of. I mean it’s so easy to do. You can start for example with a single syllable per digit, and for example only target CV syllables.

From this there is many possibilities, but for example, let’s consider only a base ten. Starting with vowels order o, i, e, a, u with mnemonic o, i graphically close to 0, 1 and then cyclically continue the reverse order in alphabet (So in a quick and dirty ruby implementation that could be something like:

    $digits = %w{k n}.product(%w{o i e a u}).map{it.join('')}
    def euphonize(number) = number.to_s.split('').map{$digits[it.to_i]}.join('-')
    euphonize(1234567890) # => "ki-ke-ka-ku-no-ni-ne-na-nu-ko"
That’s just one simple example of course, there are plenty of other options in the same vein. It’s easy to create "syllabo-digit" sets for larger bases just adding more consonants, go with some CVC or even up to C₀C₁VC₀C₁ if sets for C₀ and C₁ are carefully picked.

Re: You'll regret using natural keys

#402

Post by [deleted] »

brigandish wrote: post #400
lolinder wrote: post #390

Earlier quoted context omitted.

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

[deleted]

Re: You'll regret using natural keys

#403

Post by aaronbrethorst »

ltbarcly3 wrote: post #338
aaronbrethorst wrote: post #6

Earlier quoted context omitted.

That’s quite the absolute statement to make without even one example.

I don't think you read it carefully.

I did. I just hate encountering databases designed by people who take your advice to heart.

Re: You'll regret using natural keys

#404

Post by wasmitnetzen »

andrelaszlo wrote: post #205
kstenerud wrote: post #172

In databases, never rely on data you don't control. "Natural" keys are an example of this. Names can be natural keys, but you don't control them. You don't control when or how a name changes, or even what makes a valid name. Addresses change. Or disappear. Or somehow can't be ingested by your system suddenly. Official registration numbers (SSNs, license plate numbers, business numbers etc) seem attractive, but once a…

Official registration numbers, such as Swedish personal identification number, or "personnummer" (date of birth + serial + checksum [Luhn], where even serials are used for females and odd for males): - It can take a few days before a newborn is assigned a number - Non-citizens don't have one, but they can get a coordination number on the same format but with the date part incremented by 60 days. - Citizens can have b…

Nitpick: long-term residents get a personnummer as well, not just citizens.

Re: You'll regret using natural keys

#405

Post by psychoslave »

psychoslave wrote: post #401
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…

To my mind, it always felt so saddening that adoption of a truly straightforwardly readable notation for numbers never took of. I mean it’s so easy to do. You can start for example with a single syllable per digit, and for example only target CV syllables. From this there is many possibilities, but for example, let’s consider only a base ten. Starting with vowels order o, i, e, a, u with mnemonic o, i graphically clo…

And of course a naive implementation of the reverse is also trivial:

   def numerize(euphonism) = euphonism.split(?-).map{$digits.find_index(it)}.map{it.to_s}.join.to_i

Re: You'll regret using natural keys

#406

Post by TexanFeller »

banish-m4 wrote: post #397

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.

One downside is UUIDs take twice the space of a BIGINT which is minor on the surface, but makes a huge difference when doing lots of joins on that key. I used to see 3-4x difference in some queries even using postgres’ uuid type(not the string column of fools). Doubling the space used by your keys also means less of your indexes stay locked in RAM.

Re: You'll regret using natural keys

#407

Post by cogman10 »

reissbaker wrote: post #368
cogman10 wrote: post #298

Earlier quoted context omitted.

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…

For the number 2, I think one issue is that you are going to be semi-frequently whacking the db to do a mapping of that random string id back to the real id. OK for smaller entities but might be a pain if there's a lot of those ids to wrangle. You can throw a secondary index on it, but that will still have some minor fragmentation issues.

One benefit of a random id is if you are working with more complex data models it can make creating those easier/faster. Instead of having a centralized location to get new ids from (the DB) you can create ids on the fly from the application which can turn the write into a single action from the application rather than a dance of inserting the main table, getting the new id, then inserting to the normalized tables.

Re: You'll regret using natural keys

#408

Post by phkahler »

wild_egg wrote: post #382
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…

Why could you not join with the audit table and find historical billing information from the old CPRs?

>> Why could you not join with the audit table and find historical billing information from the old CPRs?

1) complexity and 2) what is actually tying the CPRs together? We're not going to have a CPR table per-customer, so all the CPRs of every customer are in the same table. Presumably the CPR table has a unique key for the customer that can be used to associate multiple CPRs with that person, so we have come full circle - just use that unique key in the audit table.

Re: You'll regret using natural keys

#409

Post by branko_d »

The author seems to suggest that the choice is between natural and surrogate key.

In fact, the choice is between natural and natural+surrogate key.

- If you have a natural key, you have to enforce it, otherwise you risk data corruption. The question is do you also need a surrogate key? Sometimes you do, sometimes you don’t.

- If you don’t have an obvious natural key, then your surrogate becomes meaningful. You have to use something to distinguish between two “equal but not identical” rows, so you end-up showing the surrogate in the UI etc. In other words, it is no longer “pure” surrogate.

Re: You'll regret using natural keys

#410

Post by nkozyra »

konschubert wrote: post #267
nkozyra wrote: post #261

Earlier quoted context omitted.

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.

I'm not sure why you want a uniqueness index then in this case. If it's not publicly visible (effectively a secondary index / reference), then you can't use it as an identifier.

This means you need something else (a slug, as discussed herein) or just use the ID (bad practice in general) or UUID (bad for humans). If you use - in this example - city and company name - you still have to enforce uniqueness, so you have a pseudo-slug anyway.

Post reply on HN