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

271–280 of 568 posts

Re: You'll regret using natural keys

#271

Post by hprotagonist »

8372049 wrote: post #259
gvx wrote: post #250

Earlier quoted context omitted.

Anything that needs to be read over the phone should probably be written out using something like the NATO phonetic alphabet, split into smaller chunks if needed: "The code? It's kilo eight niner; one three mike; delta echo lima."

Having come from a military background where using that is second nature, I'm constantly surprised how rarely I meet civilians who understand it effortlessly. When picking up a package I say "the code is Oscar Foxtrot three-fife" and you see the person processing for a long time to extract the first letter of the word. I've started saying "OF, that's Oscar Foxtrot, 3-5" to help them out. In other words, asking a cust…

digikey phone personnel all speak NATO. it’s wonderful.

Re: You'll regret using natural keys

#272

Post by brazzy »

ThePowerOfFuet wrote: post #216
garblegarble wrote: post #188

Earlier quoted context omitted.

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.

I hear someone got buttbuttinated there recently.

Re: You'll regret using natural keys

#273

Post by imtringued »

jklowden wrote: post #27

You think your surrogate key will save you? It will not. The world has an external reality that needs to be reflected in your database. If the unique identifier for your object — VIN, CUSIP, whatever — if it changes, the world will henceforth refer to it by both. You will need to track both. Adding a synthetic key only means you have to track all three. Plus you have to generate a meaningless number, which is actuall…

>Plus you have to generate a meaningless number, which is actually a choke point in your data throughput.

No it isn't. Working with natural keys in general involves using compound primary keys since it is unlikely that any lone field is suitable as primary key. Comparing an integer is quick. Comparing three string fields in a join is not.

Re: You'll regret using natural keys

#274

Post by lordnacho »

begueradj wrote: post #156

One of the most interesting part to me is the fact he is a university professor in a good country but rides a used/second hand car... in an era where status and success are highlighted by displaying what one buys and owns on social media.

Cars in Denmark carry a huge tax, it's not unusual that you can only afford a used car. Plus public transport in the cities is pretty ok.

Re: You'll regret using natural keys

#275

Post by bjourne »

zarzavat wrote: post #258
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’t change primary keys, that’s the point, because you don’t know where they are. For example if an old key is in a URL, and that URL is in a browser bookmark, now you need redirects, so you need to keep all the old keys around forever. Keys should be random or sequential, never contain information. If you want to enforce uniqueness then use a unique index/constraint.

I think you are confused about the terminology because a primary key is a uniqueness constraint. They can be changed in any RDBMS worth its salt. Keys should not be random and your URL example is a case in point. The url /germany/berlin/2023/mcdonalds contains no surrogate key and is immensely more useful than the url /review?uuid=1kjksdhh3244ygdvvgdd2345.

Re: You'll regret using natural keys

#276

Post by mrweasel »

winternewt wrote: post #155
delusional wrote: post #122

Earlier quoted context omitted.

We have the same problem in Denmark, most people just don't realize it. At my dayjob we get at least one person every year who changes gender and consequently gets a new SSN (the final digit is supposed to signify gender). Most people don't store SSNs so they never realize, but it does happen fairly frequently.

If Denmark is anything like Sweden there's also: - SSN from different ID space gets assigned to immigrants; when they become citizens they are assigned a new permanent SSN. - SSN:s have a long and a short form; the short form which cuts off century information can be the same for someone who is 5 years old and someone who is 105 years old. - When an unconscious patient comes in to the E.R. you don't know their SSN, s…

Immigrants and refugees will get a "replacement CPR number", which is outside the normal space/range of CPR numbers. Once registered as living in Denmark, they'll get a real CPR number.

Danish SSNs doesn't have a long or short form, you used the seventh digit to do a table lookup to see if the person is born in 18XY, 19XY or 20XY. The date of birth is always ddmmyy, there is no long form. So if the seventh digit is 9, and the 5-6 digit is between 00 and 36, then you're born in between 2000 and 2036, if the 5-6 digits are 37 to 99, then your born in the 1900. But you need the published table to figure that out.

Last point, there is backup system for unconscious patients, but it should be the same across all medical records as these are somewhat standardized.

Re: You'll regret using natural keys

#277

Post by TeMPOraL »

brigandish wrote: post #134
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.…

You require three fields (or four): email at registration, a date for that entry (together these create a natural key), and current email (this one not part of the key and editable). We're almost all the way to a Tag URI[0], so you could combine it with the user's name or username or any other identifier that fits the spec[1] (you could even use the website's own name) and you have a (definitely two thirds, probably…

Except you're encoding PII in the ID, which makes them plainly visible to people who should not have access to user data, and hard or impossible to change. Sure, I could e.g. change my e-mail and the contact data would be updated, but you still have the old e-mail associated with my account via ID. I'm not sure this would fly under GDPR.

Re: You'll regret using natural keys

#279

Post by twobitshifter »

yashap wrote: post #97
jklowden wrote: post #27

You think your surrogate key will save you? It will not. The world has an external reality that needs to be reflected in your database. If the unique identifier for your object — VIN, CUSIP, whatever — if it changes, the world will henceforth refer to it by both. You will need to track both. Adding a synthetic key only means you have to track all three. Plus you have to generate a meaningless number, which is actuall…

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

ON UPDATE CASCADE is not the nightmare that you are making it out to be. (My impression from the article is that this is a single SQL database being discussed.)

Re: You'll regret using natural keys

#280

Post by bux93 »

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…

A phone company I was a customer of used my phone number as the customer Id. Which doesn't work great for people, or companies, with more than one phone number. They could also only provision one SIM per phone number, and not use a phone number tied to a SIM as a virtual number, and also the primary (customer Id) phone number had to be tied to a SIM.

I ported my phone number away from them, but what if I ever go back? Will my old data be there? Including my old address? What if my phone number gets recycled, and some-one else gets that phone number and ports it to them? So many questions.

Post reply on HN