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

301–310 of 568 posts

Re: You'll regret using natural keys

#301

Post by mgkimsal »

fuzztester wrote: post #179
aswerty wrote: post #101

Earlier quoted context omitted.

Surrogate keys do mirror reality though. As I once read in a Terry Pratchett book; if you replace the handle of an axe and then replace the head, is it still the same axe? For me, the answer is yes - since we imbue the axe with an identity outside of it's integral parts. That is what a surrogate key is. An identity. Which is an abstract concept that exists in the real world. And to pile on. The top comment is bad adv…

>As I once read in a Terry Pratchett book; if you replace the handle of an axe and then replace the head, is it still the same axe? Yes and no. It is the Axe of Theseus ;) https://en.m.wikipedia.org/wiki/Ship_of_Theseus

Or Trigger's Broom:

https://www.youtube.com/watch?v=BUl6PooveJE

Re: You'll regret using natural keys

#302

Post by rsynnott »

Spivak wrote: post #7

Natural keys are great when they're someone else's synthetic keys. My Discord bots make liberal use of this. model: DiscordMessage key = discord_message_id key = discord_user_id key = discord_channel_id key = message_type model: UserData key = discord_user_id key = field_name model: GuildFeature key = discord_guild_id key = well_known_feature_name model: DiscordEvent key = discord_event_id key = discord_guild_id This…

Does Discord guarantee, _absolutely guarantee_, that they will never change their ids? This might be a safe practice if they explicitly claim that, and you trust them absolutely. Not otherwise.

Re: You'll regret using natural keys

#303

Post by sgarland »

jrs235 wrote: post #243
epcoa wrote: post #176

Earlier quoted context omitted.

> At this point, you realize you can now introduce a synthetic key and you're fine. Except for having to update all foreign references. Some of those may be external further complicating issues. Emails are often among the worst keys because they are not terribly stable and they are reusable often enough to burn you.

Also are emails case sensitive or not? In some systems (that you don't control mind you) they are and others they are not...

Per RFC5321, the local part (before @) _may_ be case-sensitive, but in practice, it almost never is, and relying on case sensitivity is a recipe for disaster.

The domain must always be case-insensitive.

Re: You'll regret using natural keys

#304

Post by jval43 »

twobitshifter wrote: post #279
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.…

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

It's never a single database in the real world. As soon as you integrate something or have an API to something the keys are out there. Unless you add a translation layer, but then you could just as well use surrogate keys directly.

Re: You'll regret using natural keys

#305

Post by IlliOnato »

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…

Sorry, by your logic an ISSN would be a good key for a database of scientific journals. It's exactly what ISSN is invented for! Right? Right?

Been there, done that. Journals that changed their names (and identities) but not ISSN. That changed the ISSN but not the name/identity. Journal mergers which instead of obtaining a new ISSN kept one of the old ones. "Predatory journals" that "borrow" an ISSN (you may not consider them real journals, but you've got to track them anyway, even if only to keep them from being added to the "main" database). The list may go on and on.

And don't even start me on using even more natural ID, the journal name, perhaps in combination with some other pieces of data, like year the publication started, country of origin, language, etc... Any scheme based on this will need to have caveats after caveats.

(A fun fact: there were journals that ceased publication but later on "returned from the dead". Such resurrected journals are supposed to be new journals and to get a new ISSN. Sometimes this rule is followed...)

At the end, a "meaningless number" you assign yourself is the only ID that reliably works (in combination with fields representing relationships between journals).

The problem with keys that "have meaning" is that they appear to carry information about your entity. And in vast majority of cases this is correct information! So it's almost impossible to resist "extracting" this information from a key without doing actual database lookup at least mentally, and often in your software too. Hidden assumptions like this lead to bugs that are really hard to eliminate. A meaningless number on the other hand does not tempt one :-)

Re: You'll regret using natural keys

#306

Post by Longhanks »

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?

How about not doing that?

Re: You'll regret using natural keys

#307

Post by sgarland »

mewpmewp2 wrote: post #294
brigandish wrote: post #134

Earlier quoted context omitted.

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…

But now then if you want to expose a detail page for that user the id for identifying that page has to include all this potentially personal information about them? e.g. instead of mysocialmedia.com/users/2374927 you would be showing mysocialmedia.com/users/email@example.com-2024-06-05-mysocialmedia.com Then exposing a lot of information that you may have not wanted to expose.

You don’t have to use the PK as the URL slug. Even if you want to route that way, you can have an internal ID and external ID. This is one way to use something random like a UUIDv4 for display without incurring the costs (at least, some of them) of having it as a PK.

Re: You'll regret using natural keys

#309

Post by sgarland »

lstamour wrote: post #135
maweki wrote: post #125

Earlier quoted context omitted.

> I challenge you to come up with a single plausible example So I come from academia, but generally if you use a natural key as PK in a foreign key constraint it may be possible to express additional consistency criteria as CHECK-constraints in the referencing table. So this is a bad example, but say you have Name and Birthdate as your PK, and you have a second table where you have certain special offers sold to your…

> Some modern systems also technically allow FK on alternate keys As far as I can tell, all modern systems allow it, as it is part of the SQL standard that foreign keys can be either primary keys or unique indexes. Here's a brief quotation from a copy of ISO/IEC 9075-2:1999 (not the latest version) that I randomly found online: > If the specifies a , then the set of s contained in that shall be equal to the set of s…

> I should add that there is also a set of database design wisdom that suggests you should never use database constraints such as foreign keys, only app/api constraints, but that's a whole different tangent.

That’s less a DB design thought and more of a “devs with little formal training in RDBMS who only want to use it as a dumb store” thought.

Use the DB to its strengths. CHECK constraints add an infinitesimal amount of overhead for writes, and guarantee that there will never be invalid data written. A bad code change could allow, however briefly, for that to occur.

Re: You'll regret using natural keys

#310

Post by j16sdiz »

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't.

That's why everybody use SSN.

Post reply on HN