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

211–220 of 568 posts

Re: You'll regret using natural keys

#211

Post by benhurmarcel »

otherme123 wrote: post #95
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…

In Spain we have the DNI number, that a lot of people asume is unique, even database designers that use is as a natural key. Turns out the DNI can have, and actually have, a lot of duplicates. The police has a page explaining it ( https://citapreviadnipasaporte.es/dni/dni-duplicados-espana/ ), and how it's not a primary key in their databases, but a number entered manually from a pool of possible numbers. And number…

Also your DNI can change. Typically foreigners get a NIE (used for the same thing but a different format), and get a new DNI if they ever get Spanish nationality.

Re: You'll regret using natural keys

#212

Post by benhurmarcel »

the_gipsy wrote: post #87

In Spain the ID numbers are assigned at birth, carry no information, and cannot be changed. Each police comissary that registers birth gets a unique set of IDs to assign (per year or whatever). However. Mistakes still happen. A colleague had the same ID as someone else. He said he tried to change it, but it was impossible because it was such an impossible concept to any public servant involved. In the end he gave up…

Foreigners change from a NIE to a new DNI if they get Spanish nationality. So their ID number changes then.

Re: You'll regret using natural keys

#213

Post by wongarsu »

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…

Reddit prefixes their IDs with t1_ for comments, t2_ for accounts, etc. That sidesteps the renaming issue.

Though I believe they mostly do it because their IDs are sequential, so without prefix you wouldn't easily notice if you use the wrong kind of id. They also only apply prefixes at the api boundary when they base36 encode the IDs, the database stores integers

Re: You'll regret using natural keys

#214

Post by ThePowerOfFuet »

akira2501 wrote: post #204
jimbobthrowawy wrote: post #197

Earlier quoted context omitted.

Licence plate numbers are an interesting one, since what those mean varies from country to country. Here (Ireland), they are assigned to the car itself via VIN, are never meant to change once assigned, and are backdated based on information about the vehicle itself (e.g. year of first registration even if first registered in another country, following an older format if applicable), but in other countries they can be…

You don't have vanity plates in Ireland?

Many, many countries do not.

Re: You'll regret using natural keys

#215

Post by Knufferlbert »

garblegarble wrote: post #188
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…

This approach will randomly generate profanity. If the ID is visible to users it can cause some to get upset (and best-case simply looks unprofessional). On a purely technical level if visible in URLs it can cause links to be blocked/altered by e-mail filters / filtering proxies. It's generally a good idea to drop vowels for this reason.

We once had a rather angry Irish customer calling our support complaining that we called him a pikey (slur for gypsy). After some back and forth it turns out we just gave him an apikey.

We never had a similar issue with our random numbers/letters/reset passwords or anything like that which don't have any kind of "dont return profanity" protections. Though I agree, someone getting a randomly generated customer portal url or something containing fuck or similar would look bad. Our cloudfront or something (or was it main public facing s3 bucket? can't remember) starts with "gay" and was never picked up on.

Re: You'll regret using natural keys

#216

Post by ThePowerOfFuet »

garblegarble wrote: post #188
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…

This approach will randomly generate profanity. If the ID is visible to users it can cause some to get upset (and best-case simply looks unprofessional). On a purely technical level if visible in URLs it can cause links to be blocked/altered by e-mail filters / filtering proxies. It's generally a good idea to drop vowels for this reason.

I hear Scunthorpe is lovely this time of year.

Re: You'll regret using natural keys

#217

Post by Aaargh20318 »

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…

In the Netherlands we have something similar: the BSN (translated: citizen service number). This used to be the social security number, but because it was used by an increasing number or government agencies unrelated to social security it was changed to BSN.

One of the major problems with this number is that it has a special status under the law. There are very strict rules as to who can process and/or store this number and for which purpose. For example: a bank can process this when opening a bank account, under anti money laundering regulations, but they cannot use it to identify an existing customer.

If you originally set up your database to use SSNs you now have a problem. This actually happened with our chamber of commerce: if you registered a one-man business they used the SSN as the business id and you’re required as a business to publish this. Now it’s suddenly a number that is subject to strict privacy rules and they have to renumber all one-man businesses.

So that’s another problem with data you don’t control: the legal status of this data can change.

Re: You'll regret using natural keys

#219

Post by contingencies »

My traditional schema was always to have id in every table and to make it an integer, then for pretty much anything it'd be two tables, nouns for the thing and a one-to-many relationship with noun_names for the names, where noun_names was comprised of id/noun/language/name, keyed on noun and language, with the language column FK'd to languages.id. I'd also have a languages.code column for ISO or IANA code (sometimes both columns or aliases), and a language_names table with language_used/language_named. So you could name English in Frisian, and Chinese in Tibetan. Haven't needed to build SQL in ages, though that schema works great. Certainly never had an issue with it once UTF-8 became standard. If you build schemas without i18n, you're asking for trouble.

One good thing about using a standard integer key is the cost of indexes is low (low memory use). One good thing about using id everywhere is that it's short and self-explanatory to programmers from any culture.

Lots of casual queries built of subqueries like...

select * from noun_names where noun=(select id from nouns where ...) and language=(select id from language where code='en');

Always felt this was the most readable. Always felt that LEFT/RIGHT JOIN stuff was bonkers. Onboarded a lot of serious junior devs, never had an issue.

Re: You'll regret using natural keys

#220

Post by strnisa »

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…

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
Post reply on HN