You'll regret using natural keys

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.

Or building on that example, maybe at first we always require an email from our users. But later we also allow phone auth, and you just need an email OR a phone number. And later we add user name auth, SSO, etc. Again, all good with surrogate primary keys, a nightmare with natural primary keys.

There are countless examples like this. You brought up cars, same thing with licence plates, for example. Or even Social Security Numbers/Social Insurance Numbers - in Canada SINs are generally permanent, but temporary residents can have their SIN change if they later become permanent residents, but they’re still the same person.

You want your entities to have stable identity, even if things you at one time thought gave them identity change. Surrogate primary keys do that, natural primary keys do not. Don’t use natural primary keys, use surrogate primary keys with unique constraints/indexes.

I challenge you to come up with a single plausible example where you’re screwing yourself by choosing surrogate PK + unique constraints/indexes. Meanwhile there are endless examples where you’re screwing yourself by choosing natural PK.

The surrogate key uniquely identifies a row in your database, which is an entity just as real and significant as the car or the employee or what-have-you. Don't confuse the two!

I agree with you that having a surrogate key isn't going to save you from the reasons why natural keys can be difficult. The complexity has to go somewhere. But not having a unique identifier for each row is going to make things extra difficult.

Terrible advice.

Surrogate keys are keys are a layer of indirection.

They don't fix all problems, but they fix some problems.

Not least of which is performance. Often natural keys are character strings, whereas surrogate keys can be fixed size integers, saving index sizes on your FKs.

Sorry. This is a very bad advice. I just had to fight tooth and nail to make my lead turn around from this disastrous decision. Using a lot of external IDs as our own row primary keys and then they get propagated to all other tables as foreign keys and what not. One day the foreign key chances or God forbid, the formatting changes in external systems, now we need to fix our whole database and all codes instead of a small isolated place.

Generate your own unique keys for everything; add a few more unique constraints if needed. A bit more work but never a regret.

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 advice! Surrogate keys provide sanity - god save you if you have to work in a database solely using natural keys.

In the wise words of Patsy, "it's only a model".

The real world is resistent to clean abstractions and abstractions are distressingly subject to change. What made your row unique today is quite likely to become non-unique in the days/months/years to come.

Always use surrogate keys. Your future self will thank you.

There is a model of a thing and there is a row that stores a representation of that model of a thing.

They both are things. Ignoring the last one might be tempting, but it’s not practical.

Interestingly your own way of thought is applied, but now a level deeper again.

How do you model a row? What makes it unique? A surrogate ID is the only sensible unique identifier for such a thing as there is no “natural key” that would make sense for instances of something so general as “Row”.

What you were saying amounts to “don’t model the thing holding the model”, but experience shows the thing holding the model is itself an (often unwilling) active part of systems.

Someone here gave the example of wrongly entered PK’s by administrative personnel handing live customers. That’s IMO a good example of why you need an extra layer on top of your Actual Model(c). I can think of more.

How do you deal with the Ship of Theseus/Trigger's broom? There's literally nothing that defines said object apart from its history.

In short, you advise us to foresee the future, explore unknown unknowns and expect high-precision true answers from the outside. Good advice, not for this universe. You can only get false negatives in this one.

A synthetic key means “we think exists”. There exists a contract, a medical record, a person, in a real world, in our opinion. We record these existence identities into our model by using an always-unique key. Then there’s a date, an appointment #, a name, etc. You can refer to an entity by its identity, or search by its attributes. If you use searches in place of identity references, you get non-singletons eventually and your singleton-based logic breaks.

In terms of the Danish CPR that is mentioned here, the way we actually solved the challenge in the national architecture strategy was to define your social security number(s) as what we call an address on your person. I’m not sure why it was called an address, but it’s basically a UUID and the information. Maybe it’s because it was first used to store addresses?

Anyway. Unless your system has not yet implemented the national strategies (which by now are approaching 24 years) then changing a Danish Social Security won’t actually matter because it’s just an added address to your person “object”. So basically you’ll now simply have two, and only which is marked as the active. Similar to how you’ve got an array of addresses in which you have lived but only one main address active.

It did indeed cause a lot of issues historically because it was used as a natural key… though with it being based on dates, it was never really a good candidate for a key since you couldn’t exactly make it into a number unless you had some way to deal with all the ones beginning with 0 being shorter than the rest. Anyway… it was used as a key, and it was stupid.

Anyway I both agree and disagree with you. Because we’ve successfully modelled the real world with virtual keys, but you access it by giving any number of natural keys. Like… you can find the digital “me” by giving a system my CPR number, but you can also find me by giving a system my current address. Technically you could find me by giving a name, but good luck if you’re dealing with a common name. There is a whole range of natural keys you can use to identify a “digital” person, but all of it leads from a natural key into a web of connected virtual keys.

All of it is behind some serious gatekeeping security if you’re worried. Or at least it’s supposed to be.

Model your data on the real world. Do not depend on spherical horses.

Yes, and normalize it. https://en.wikipedia.org/wiki/Database_normalization

Adding a synthetic key only means you have to track all three. Plus you have to generate a meaningless number, which is actually a choke point in your data throughput.

This is true up to a point. You can add more data to the system to continue to generate natural, composite keys. However at some point you move from a database to an event stream, or you have to track events that aren't really needed for what your doing...

Denormalization then takes precedence and a generated key makes sense again. https://en.wikipedia.org/wiki/Denormalization

how will the surrogate key protect you?

It isnt about protection, it's about not collecting the natural data to identify the event that caused the issue. Its denormalization by omission in effect.

Been vehemently against surrogates my whole life, glad to find a kindred soul.

When I integrate systems, I use that system's natural key (love it when it's a unique ID, but in the systems I work in - it almost never is).

That said, I use that natural key as the "link" to my internally managed, normalized database.

There's nothing that says I cannot add unique identifiers that would replicate the natural key. In fact, that's good design.

In that case, using this natural datum as an ID is a contradiction, because we are now removing the uniqueness constraint. It's fine to model the real world, but the real world also includes your data, and you may want to identify unique _records_ as it is not a universal truth that data can be corrected on entry.

The natural key forces you to think about what makes the row unique. What identifies it. Sometimes, it makes you go back to the SME and ask them what they mean. Sometimes it makes you reconsider time: it’s unique now, but does it change over time, and does the database need to capture that? In short, what are the boundaries of the Closed World Assumption? You need to know that too, to answer any "not exists" question.

Not really because your natural ID has to also account for the problem of garbage data and account for the SME's not actually being experts. And I can give a real world example of this happening; the Canadian Long Gun registry.

For anyone that doesn't know, prior to the early mid 90's or so Canadian gun laws only required registration of pistols. I might be incorrectly remembering but IIRC the records were not handled at a national level either but I could be wrong. Around that time new laws were introduced that among other things required registration.

Most guns by then had serial numbers so all you had to do was tie a gun to a serial number with some characteristics and voila, you've got a natural identifier, right? That's what the experts say.

Well as it turns out, reality isn't quite so kind. A lot of firearms makers such Cooey from around the 1940's to the 1970's produced guns without a serial number. In other cases the serial number was present from the factory but were damaged, or the part that had thee number had been replaced without replacing the number or had been replaced with a wrong number. In rare cases the serial number from the factory was wrong because of a mistake when the worker manually stamped in the number.

So already the idea to uniquely identify using some sort of simple classifier was already flawed. They attempted to solve the issue of guns without serial numbers but those stickers were cheaply made and readily fell off, and owners that were already peeved about the program to not bother with trying to paperwork correct.

Which segways to the next problem. There was an extremely high rate of errors in registration forms being submitted. The most famous example I'm aware of was someone registering a Black and Decker soldering gun as a firearm, something he had done in protest. As humorous as it was, it the revelation that a soldering gun had been classified as a firearm unveiled another fundamental problem.

The error rate was so high, and the pressure to show progress so great, that the someone in leadership (I can't remember if it was the government or RCMP) directed the data entry clerks to just plug the data in with no validation as is. Didn't matter if the data was wrong, or made no sense, or contradicted other entries already in the database. The intent being to just get all the data in as is so that they could fix it later. So all that wrong information? Got pushed straight into the database.

Like I said, this was real world mess that occurred from 1995 until 2012 when a new government dropped the requirement for non restricted firearms to be registered with little fanfare and only squeaking protests.

It's not to say that you shouldn't think about a 'natural key' persay. But the problem assuming that there is a 'natural key' requires that you or your subject matter expert is actually an expert that can identify a good enough model for that to exist.

But what happens when your SME is just plain wrong and it in turn introduces fundamental flaws in your model? Or outside influences forces garbage data in? How is a database designed to model only the real world supposed to cope with that?

Your post brings up a critical difference I’ve noticed when working with devs (I’m a DBRE): those who actually do rigorous data modeling, and those who view it as an annoyance impeding their project.

Spend time modeling your schema. Ask yourself, “does every attribute in this table directly relate to the primary key? And is every attribute reliant upon the primary key?” Those two alone will get you most of the way through normalization.

how will the surrogate key protect you? It will not.

Yes it will. Your changes will be confined to only the table(s) where the natural key is present, not spread across every table where there's a foreign key.

Of course you will still have to deal with the reality that the natural key is now not unique, and model reality, but your implementation work in doing so is far simpler.

In more years than I care to count I've regretted someone using natural keys as a primary key for a table many times, and surrogates never.

Of course they won’t save you from external data. The whole point is for your system to have a way to identify rows internally so you can deal with external systems getting wonky without corrupting your own data.

All of your concerns are easily solved by a unique index.

Using external keys as a forcing function to prevent people from representing data wrong is not great.

Model your data on the real world. Do not depend on spherical horses.

This took me years to realize, and once I did things became much, much simpler.

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.

In my experience, this won't end well. Some examples:

Belgium has the RNR/INSZ identifying each person. But it can change for a lot of reasons: It gets reused if someone dies. It encodes birth date, sex, asylum state, so if something changes (which happens about every day), you need to adapt your unique key.

Belgium also has a number identifying medical organizations. Until they ran out of numbers. Then someone decided to change the checksum algorithm, so the same number with a different checksum meant a different organizations. And of course they encode things in the number and reuse them, so the number isn't stable.

An internal COBOL system had a number as unique key, and this being COBOL, they also ran out of numbers. This being COBOL, it was more easy to put characters in the fixed width record than expand it. And this being French COBOL, that character can have accents. So everyone using the 'number' as unique key now had to change their datatype to text and add unicode normalization. Not fun.

In my experience: don't use anything as an ID that you didn't generate yourself. Make it an integer or UUID. Do not put any meaning in the ID. Then add a table (internal ID, registering entity, start date, end date or null, their key as text). You 'll still sometimes have duplicates and external ID updates as the real world is messy, but at least you have a chance to fix them. The overhead of that 1 lookup is negligable on any scale.

Adding to the list of comments damning this post to ensure none of my future colleagues follow this advice.

You think your surrogate key will save you? It will not.

It definitely will, say my 20+ years of experience.

Natural keys can change, and synthetic keys never have to. That alone is reason enough to use synthetic keys.

Performance is another major argument for synthetic keys, as they are can be made sequential, which is rarely the case for natural keys.

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.

Yes, it will. It is precisely because of a messy external reality that you need an unchanging internal ID that is unaffected by changes to the external ID. If the software designer in the article had followed your advice, changing the chassis number would have likely resulted in broken car ownership records.

Whoever is reading this in the future, please don't follow the parent comment's advice. Use surrogate/synthetic keys for your primary key.

Every time I used a natural key I have to come to regret it.

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

The natural key forces you to think about what makes the row unique. What identifies it.

When designing a table - you should always be clear about what the natural key is and make sure it has uniqueness constraint. Mindlessly having a surrogate key without thinking about what the natural key is, is an anti-pattern. So totally agree here.

That doesn't stop you also having a surrogate key though.

Another aspect of natural versus surrogate keys is joins as the key often ends up in both tables.

Using natural keys can mean in some circumstances you can avoid a join to get the information you want - as it's replicated between tables.

There is also the question of whether you surface the surrogate key in the application layer or not - some of the problems of surrogate keys can be avoided by keeping them contained within the app server logic and not part of the UI.

So via the UI - you'll search by car registration number, not surrogate key, but in terms of your database schema you don't join the tables on registration number - you use a surrogate key to make registration numbers easier to update.

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 < length; i++) {
            result += alphabet[crypto.randomInt(alphabet.length)];
        }
        return result;
    }

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 insertions tend to be on the same page. If you have a low frequency insertions then minute resolution is probably good enough. (Minutes since 2000 is an easy calculation).

To implement that here, I'd suggest looking at how base64 or 85 encoders work and use that instead of repeated mods. You can then dedicate the upper bits to a time component and the lower bits can remain random. [2]

[1] https://vladmihalcea.com/uuid-database-primary-key/

[2] https://github.com/mklemm/base-n-codec-java/blob/master/src/...

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.

Why is such a thing called a slug?

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 of sync with the code and will confuse every new developer.

Nice!

For the record: the valid chars string is 62 characters, so naively using a modulo on a random byte will technically introduce a bias (since dividing 256 values by 62 leaves a remainder). I don't expect it to really matter here, but since you're putting in the effort of using crypto.randomBytes I figured you might appreciate the nitpick ;).

Melissa E. O'Neill has a nice article explaining what the problem is, and includes a large number of ways to remove the bias as well:

https://www.pcg-random.org/posts/bounded-rands.html

(in this case adding two more characters to the validChars string would be the easiest and most efficient fix, but I'm not sure if that is a possibility here)

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 (<-a, <-e, <-i, <-o*, |-u). We now only need two consonants for the two series of 5 cardinals in our base ten, let’s say k and n.

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"

I'm not convinced that all Stripe IDs are wholly random strings. I just decoded the base62 part of four genuine "acct_" objects, which at 16 characters are just shy of representing a 12 byte value (log2 62^16 =~ 95.3), and they all have a leading byte of "00000011" and two of them even have a leading 32 bits that is very suspiciously close to an epoch timestamp of a couple of years ago.

There is a similarly suspicious pattern in some of their longer identifiers, invoices for example at 24 characters (ca.143 bits) all seem to have a first byte of "00000010".

Even in the article you've linked to, look closely at the IDs:

      pi_3LKQhvGUcADgqoEM3bh6pslE
      pm_1LaXpKGUcADgqoEMl0Cx0Ygg
     cus_1KrJdMGUcADgqoEM
    card_1LaRQ7GUcADgqoEMV11wEUxU

Based on these values it seems there's a metadata preamble in the upper bits of the supposedly "random" value, and it's quite possible that some have an embedded timestamp, possibly timeshifted, and a customer reference, as well as a random part, and who knows maybe there's a check digit as well.

It's possible - albeit this is not analytical but more of a guess - that the customer ID includes an epoch-ish timestamp followed by randomness or (worst case, left field) is actually a sequence ID that's been encrypted with a 64-bit block cipher and 32 bits of timestamp as the salt, or something similar (pro tip: don't try that at home).

My view is that either Stripe's engineering blog is being disingenuous with the truth of their ID format, or they're using a really broken random value generator. If the latter, I hope it's only in scope of their test/example data.

If I'm going to do that I think I'd use Bitcoins BASE58 which avoids letters that could be confused for each other. The number of times I see an O and 0 and wonder which is which, because the font does not make it clear really annoys me.

Edit:

Other honorable mentions: ObjectID's as used by MongoDB which contain the creation timestamp. Also Discord's snowflakes (inspired by Twitter's iirc), which also contain the creation timestamp.

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.

Except the Stripe ones are case sensitive which can be annoying with some databases

Please don't use % to generate integers from a range, it's not uniform, which can be disastrous if you rely on your numbers not being predictable. You can use crypto.randomInt instead.

I feel like https://github.com/jetify-com/typeid is the solution to this

I don’t recommend using random bytes for this. What I have done in my previous projects is take a uuid6, reserve a couple of bytes inside of it to replace with an object ID, and I convert that to obj_XXXXXXX.

This means you can store them in the db not as a string but as a uuid, which is a lot more performant. You also get time stamping for free.

Yes, and I like to combine two established concepts instead of rolling my own: URI and UUIDv7. So my IDs become `uri:customer_shortname:product_or_project_name:entity_type:uuid`. An example ID could be `uri:cust:super_duper_erp:invoice:018fe87b-b1fc-7b6f-a09c-74b9ef7f4196`. It's even possible to cascade such IDs, for example: `uri:cust:super_duper_erp:invoice:018fe87b-b1fc-7b6f-a09c-74b9ef7f4196:line_item:018fe882-43b2-77bb-8050-a1139303bb65`.

It's immediately clear, when I see an ID in a log somewhere or when a customer sends me an ID to debug something, to which customer, system and entity such an ID belongs.

UUIDv7 is monotonic, so it's nice for the database. Those IDs are not as 'human-readable' for the average Joe, but for me as an engineer it's a bliss.

Often I also encode ID's I retrieve from external systems this way: `uri:3rd_party_vendor:system_name:entity_type:external_id` (e.g. `uri:ycombinator:hackernews:item:40580549:comment:40582365` might refer to this comment).

Nice, but better to remove ambiguous / hard to read letters: ijlo, IJLO, and 01 (and maybe 7 as well?)

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.

How to uniquely identify an American citizen?

The US SSN is not guaranteed to be unique

The cases you listed do not mean SSNs are not unique, unless there are people who share the same SSN. You can still define a unique index for the SSN column. A column can be both nullable and unique as each null is different in SQL.

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 re-using is a possibility. They estimate the number of duplicates in 200,000 for a population of 50,000,000.

The point is that if you asume DNIs are unique and use them as PK your database is exposed to the bad design of the DNI database. There are some stores that use the DNI as the "unique" identifier for fidelity cards.

Pernicious assumptions!

Google Cloud projects have three attributes: user-friendly names, system numbers, and system names. System names are alphanumeric. They can be chosen by the user, derived from the friendly name if there's no collision.

But! There's some system names from the olden days that are actually all numbers - so not actually alpha-and-numeric. Thankfully we don't run into those often.

Yeah, these things are almost never as simple as they are supposed to be.

Poland has PESEL numbers since 70s. It was supposed to be unique, only apply to Polish citizens, never change, and have a checksum digit. Every Polish citizen gets one at 18 when they get their national ID document, and you can request it earlier if you want to.

Turns out there are duplicated PESEL numbers. A LOT of non-Polish citizens have them assigned (mostly Ukrainian refuges but not only). The checksums are sometimes wrong. And some people have several PESEL numbers.

If you used PESEL as database key you're fucked.

The system works perfectly, but it interfaces with external world through computer-human-paper-human-computer interface. And at some point the mistake propagates so far that it becomes the truth assumptions be damned.

> 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 a new CPR issuance.

But what if you have a bunch of records in some other table - billing information for example, and it's indexed by the CPR number (a foreign key). When they change the CPR number you can no longer query for their entire billing history based on CPR. None of your proposed extra complexity does anything about this problem. The only good way to solve it is to use a synthetic key with NO meaning. It would still be good to do as you say and track all the CPR number changes for a given person, but they will still need a unique key. So a sort of "identity table" used to figure out what unique key you're dealing with.

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.

It's not really fine. I work on a Healthcare system in the nordics.

Who you billed, who visited what doctor, who your primary care provider is, all the people a doctors office has as patients, refers to the SSN.

You don't want to lose that connection or to have to update everything for any change.

You store a unique identifier for the person in the system, and you can then pull the actual personal identification number when needed.

You do not keep individual private lists of people changing genders.

Take for example the Danish CPR number. That's perfectly fine as a natural key; its definition is the first CPR number assigned.

This is a horrible idea. You now have two different pieces of information that are identical in form and indistinguishable in the majority of cases: "first CPR number" and "current CPR number. Every place you enter, or use a CPR number, you now must track which piece of information you have. If you make a mistake doing this, it will be hard to catch. Now you've decided that first piece of information is a natural key and will be every single place the record is used, even if that spot doesn't do anything specific to the CPR number. Every single time a CPR number is ingested from an exterior source, you need to do a lookup to make sure it is the original CPR number and then track it. Ever place you don't do this lookup is a place where an error can creep in if something changes in your pipeline our source.

Even in an context where external sources are using something like a CPR number as an key, I would still use a different key internally since I see only downsides to using a "natural key".

URLs are another good natural key: they are defined to be unique (otherwise your webserver won't work), they make for very easy lookups when you're fetching from a web request, and if they change, they break the web

URLs are also horrible natural keys. They are not defined to be unique and provide no guarantee that the content has not changed or even that the same content is sent to different users.

If the location for content changes, you may or may not get a redirect. If you do get a redirect, you'd have to now go update every single place that uses the key to the new value. It is much better to map URLs to an artificial key and update that mapping in a single place.

I'm a fan of audit logs, but what you're describing is more event sourcing than audit logs. From what I understand what you're suggesting is that you don't change the key in the original table, you just record the change in a second table. Presumably, then, when you need to read a row you must also look up the list of diffs for that row to make sure that the natural key hasn't changed.

Two things strike me about this proposed model:

First, under your proposal the version of the model that you work with at the application layer must have two copies of the key field—one is the database key for when you need to make changes or look up more data and one is the meaningful business-model field that's actually up to date. That's exactly the same extra mental overhead that natural keys are supposed to have solved, only worse because the fields will have similar names and will contain the same content most of the time, making it easy to accidentally use one where the other was expected.

Second, if you're going to introduce effectively an event-sourced data model then you've introduced a ton of new database records already, so why not just give everything a proper unique key while you're at it? Once you've done that you can modify the original row after all (while retaining the audit logs!) or, if you're serious about event sourcing, cache the latest derived value and look it up by its database key instead of carrying around an out of date natural key that's just waiting to be used incorrectly.

You appear to be describing a classic type 2 or type 4 slowly changing dimension. (https://en.wikipedia.org/wiki/Slowly_changing_dimension )

Take for example the Danish CPR number. That's perfectly fine as a natural key; its definition is the first CPR number assigned.

The problem is that the new CPR number is now the one you want to use for all display purposes and future interactions with external systems. In other words, you can't use the "original CPR" field for anything except as key. It's no longer a CPR field, because it no longer has any relation to the person's CPR!

And at that point it'd be better to just use a GUID or something as key and avoid any potential confusion between the "real" CPR and "fake" CPR, because when the two are the same 99% of the time it is guaranteed to cause a shitton of bugs.

The only solution is to essentially rewrite all your records with the new CPR as key, and leave a redirect entry at the old CPR. That's pretty much what happens in Sweden when you change your gender: your old identity ceases to be and you're issued a completely new one.

URLs are another good natural key: they are defined to be unique

No, see more info here: http://localhost:8080/

URLs are only really uniform not necessarily unique. The most obvious case is something like `http://localhost/file.txt`, where the resource being located is almost sure to be different on every single "localhost," but this is true of any server. The pointer is neither unique nor has any true guarantee that the resource being pointed to can or should be considered unique for any given context. It is merely unique within its calling context, which, when dealing with URLs, is often anywhere on the web where DNS responds.

Even if you presume that the resource being located is in fact a unique resource in the general case, the "unique resource" may not be unique in the way that you presume. Some URLs at a given location are intended to be idempontent and cache-able, others are not, and many are time-limited forwarders. And there's no guarantee or even expectation that two identical forwarding URL's will resolve to the same location; it may well be network-topography dependent.

Last I checked, Steam still has me logging in with my two decade old hotmail address as my account name. At least it's not something that shows publicly, I think.

yeah the way he described it it's like... well who would ever do that. but foreign keying to emails or usernames is much easier to "accidentally" do and is a classic source of long-term headaches.

On the other hand, Discord used to allow arbitrary usernames and would add a suffix for when you needed to disambiguate (e.g. if you used the username JohnDoe, your "id" would be something like JohnDoe#12345) but over the past year or so forced everyone to pick a fully unique username. In this case, the decision seemed to potentially be financially motivated, given that people with subscriptions were given priority for claiming usernames, and historically there really hasn't been much reason to pay for it in the first place.

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.

This is one of those cases where examples of natural keys failing are so ubiquitous that they are almost redundant.

For the group who have forged a career with natural keys, and never regretted it, more power to you. Great.

However to the rest of us, myself included, where ill-considered natural keys have caused endless opinions and suffering, my commiserations.

If I could send back one piece of advice to junior-me it would be to avoid natural primary keys. (Ideally with the corollary to avoid sequences, but that's another thread for another day.)

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 reassigned to other vehicles.

e.g. 06-LK-12345 might be your car's plate if you move your foreign 2006 car here and register it while living in Limerick, but buying a new car might give you a plate like 242-L-12345 since the format of the first two fields changed since. If you leave and later return with that car, re-registering it gives you the exact same number.

https://en.wikipedia.org/wiki/Vehicle_registration_plates_of...

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 both a coordination number and a personal identification number in certain cases.

- They can be changed if the wrong birth date or gender registered at birth or during immigration, for protected identities, or for gender transitions.

I agree with your general point, but it might still be cheaper to redesign your system if a rare breaking change happens to usually reliably stable external registration systems, rather than to pay the cost of a thousand paper cuts by indirection, computational cost or human confusion.

One edge case and good indicator is if your system is testable by itself without third party cooperation and the ability to, for instance, create license plate numbers.

I was this recently. I moved to US in November and it was February before I had an SSN. A bunch of companies had to put in fake SSNs into their system, which they have a standard for.

Thank you for your comment. It spurred me to think about this issue more thoroughly in a way I hadn't, even though this comment may disagree -- to some extent -- with your perspective.

I have not seen clear guidelines about whether an organization's surrogate keys for persons are considered PII. (And this ambiguity has frustrated me for some time as I am unclear whether to take an aggressive or conservative view on labelling PII where I work.) When I have read the guidelines, it seems ambiguous but on balance I think it disagrees with your implied claim (that PII is not present in a table that uses a surrogate foreign key to a person/user.)

The definition of PII per NIST https://nvlpubs.nist.gov/nistpubs/legacy/sp/nistspecialpubli... is:

"Any information about an individual maintained by an agency, including (1) any information that can be used to distinguish or trace an individual‘s identity, such as name, social security number, date and place of birth, mother‘s maiden name, or biometric records; and (2) any other information that is linked or linkable to an individual, such as medical, educational, financial, and employment information."

A surrogate key associated with a person is arguably "any other information that is linked or linkable to an individual" meaning that all tables containing the surrogate key remain PII and remain "infected". It's true the surrogate key only allows linkability within the context of the data ecosystem in which it resides, but such distinctions (of "internal" to the system vs "external from the system") are not made in the language of the definition. Additionally, from a pure risk and PII disclosure impact, usually the whole database gets dumped, not just the "non-person" tables. If you have financial/medical transactions in table "A" and a personal numeric ID linking data to a person table "B", both tables contain PII, right?

From a privacy standpoint, if you can SQL JOIN the data to trace the person involved either within your system or even data reasonably obtainable outside your system, (or if an attacker can), it's PII.

Intranet IP addresses are called "linked PII" in section 3.2.2 of the above NIST guidelines for example, and NIST does define some related terms that would seem to apply to surrogate keys like: * Distinguishable Information: Information that can be used to identify an individual. * Linkable Information: Information about or related to an individual for which there is a possibility of logical association with other information about the individual. * Linked Information: Information about or related to an individual that is logically associated with other information about the individual.

As a data engineer, the above interpretation means PII is in a zillion tables and labeling a table with a boolean indicator yes/no isn't that helpful. But as a policy person, NIST seems to be recommending gauging PII more at the system (not table) level and with a PII Confidentiality Impact Level of low/medium/high that takes into account the context and overall risk and that seems sensible. From a data cataloging standpoint (gauging what's "infected" to use your term), I think it's probably helpful to identify particular transactional tables or personal table as having "high" PII disclosure impact vs "low"; the presence of "infection" from a virus ("PII") is mostly irrelevant in a sufficiently large system where viruses/some PII is inevitable but what matters is the severity/impact. A zillion tables will be "low" (e.g. if most tables have audit column saying which user last changed a particular record) but certain transactional or user tables may be "high" and should be recognized as such and the focus of any risk discussions with the business or legal or breach notifications to customers or what have you.

Going back to your original point, I don't think the choice of natural vs surrogate key impacts the PII risk of the system or even its individual tables. I would slightly concede that a surrogate key (which in general I am in favor of) would make it easier to reduce a particular individual's PII from a system by concentrating it in one or a small number of tables with names, etc. which might be helpful for enabling GDPR right to be forgotten or something. But the degree of that PII elimination from a system by blanking out or archiving a particular user/person record is not necessarily reducing the PII for them to 0 at least definitionally unless the transactional records themselves are also removed as the AOL 2006 search data scandal demonstrated (where a woman identified solely by a surrogate key was able to be identified from her search term transactions alone.) (Legally there would appear to be carveouts around transactional deletion for some financial transaction records and backups, but IANAL...)

Agreed, and also they tend to be more guessable. Make a page available with an email address as the id and just watch the hackers use it to discover users of your service and attempt to log in as them.

AFAIK, even using a UUID is still considered PII if it uniquely identifies the user.

Extremely good point.

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

You can use a UUID as a primary key and still enforce uniqueness on a combined index of city and name.

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 this is an excellent example of one of the pitfalls that the author is getting at.

(Disclaimer: not a Discord bot programmer.)

Suppose discord_user_id is an identifier like @Spivak#2024 that uniquely identifies the user. When Discord forces all users to change to unique ID's and eliminates the disambiguating numbers (or allows users to change them), then where does that leave you?

(For the unfamiliar: something like this happened. Not sure if those are really the "discord user id" used in bot service though, but suppose that they are for the sake of the argument.)

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.

Ideally, you should aim to sanitize/normalize strings on the write side rather than resanitizing on every read.

Side note about using email addresses.

It is pretty common to assume that email addresses aren't case sensitive since many email providers treat them that way. Email addresses absolutely are case sensitive so you need to preserve case when storing them and be careful about case when using them.

This makes email addresses particularly unsuited to being used as a natural key. Most people treat them as case insensitive and you need to work around that, but you can't safely just treat them as case insensitive yourself.

This sounds like it should probably be a workflow instead. Modelling the intent here is actually important.

Finding and eliminating duplicates is a very common software problem that is rarely solved in a reusable, user-friendly way that preserves history while eliminating redundant data. In fact, in 40 years of working with computers I can't think of a single UI that I'd want to emulate.

One of the most surprising things I learned about the US healthcare system is how often everything is mutating or being retroactively updated (assume it applies to other countries too).

Things you'd think would be constant after Step 1 often aren't, and processes are tolerant of their being corrected / re-entered after Step 52 has already been completed.

One reason to not do that is because you could then figure out other users IDs by their hiring date. This ended up being a problem for me once with the auxiliary systems that relied on that ID, namely when it was used in links in other applications.

Highlighted by whom? University professors?

in an era where status and success are highlighted by displaying what one buys and owns on social media.

I mean, not for everyone? These things, pretty much regardless of era, matter a lot to some people and not at all to others; for still others it may be a negative signal of sorts.

Anecdotally, I know a lot of very well-off people; well under half would have fancy cars.

in an era where status and success are highlighted by displaying what one buys and owns on social media

This is no different from the people who used to show off their rolex in the pub, or park their BMW on display thinking it was impressive.

The 'show' may have moved, but it's still the same people who gain the same amount of 'respect' that they ever did (not much).

Besides, I would guess that most professors are not so worried about their car (and in fact many may prefer a more sustainable mode of transport) as they are to their research and citations, and the advancement of their students.

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.

This is an educated person who shows higher-class tendencies by riding a bicycle, compared to those who seek mere automotive eminence.

Even if the bicycle is older than the car, perhaps even more so.

many databases store rows in the order of a table's primary key (sometimes called the clustered index [...])

Note for readers: Postgres doesn't do that

This is why UUID-based systems can suffer worse read + write performance; their rows will be stored in the order of its UUIDs (that is, randomly spread around), making read, insert, and update performance lower.

This isn't always the case anymore. UUID standards have been developed in that mind, and they are not completely random anymore. UUIDs can give a hint, for example, about the time when it was created, which gives them some order.

Even if this is true now (which it isn't entirely), that is no guarantee that it will be true in the future. Any time you use an external value as a key, you run the risk of a policy change. An internally generated artificial key is the only safe way to ensure immutability and lack of semantic content.

Not really assigned at birth, but at the time you first request the card. It's a very very very crappy database ID number:

-There's a significant amount of duplicates, enough that if your database is big enough you will find one sooner or later.

-Format is variable: There are older 7 digit numbers, modern 8 digit ones, some people pad the 7-digit with a 0, some don't, some consider the CRC letter part of the key, some don't, some just append the letter, some hyphen it. If you deal with manual data entry anywhere, you will suffer.

-Foreign people exist: You start using their passport number, the format of your key is now completely arbitrary, can't validate it. If you were using 8 digit NIFs as keys now some countries use 8 digit passports, increasing your chances of duplicates.

-Foreign people stay: They get a resident card and they use that. Format is almost like NIF but not really so you need to account for that. Someone you registered initially with a passport now has the card and registers for something else with it. Have fun cleaning their duplicated identity from the whole system.

-Foreign people become Spanish: Now they get a NIF. If you have dealt with them for a long enough time, have fun again fixing their records for the second time.

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

Well, UUIDs bring their own challenges. Dropping that one here:

Be Careful with UUID or GUID as Primary Keys https://news.ycombinator.com/item?id=14523523

It's pretty easy to migrate to UUIDs later if required, as long as you don't leak your keys.

My work made their own uuid-like scheme, similar to UUIDv1 which incorporates several elements like machine ID and timestamp. The mistake was twofold: first they exposed them (so people started using them) but, worse, they made them easily reversible, so people started decoding the information in them. People would naturally see one and think "oh this is a record from place X". Of course that might not be true following subsequent data corrections, but the key can't change.

UUIDs are only good when you don't care about ergonomics or performance.

Snowflake IDs are much more reasonable. 41 bits timestamp (ms) + 10 bit machine id + 12 bit serial.

But if you care about ergonomics and privacy the most then short and random IDs are really the best.

Likely no, there are currencies without an ISO 4217 code, i.e.: https://en.wikipedia.org/wiki/Faroese_kr%C3%B3na

I think long term, that is a bad idea.

In Brazil in the 80's and early 90's, the currency changed name several times. It was called "cruzado", then "cruzeiro", then "cruzado novo" (yep, "new" <old-name>, very creative) and then I think it went back to just "cruzado" :D before finally becoming Real (which I believe was the name of the currency also during Monarchy, 100 years earlier).

Suppose you take the advice of this article, and use, say, social security numbers to identify people.

You seen to have misunderstood the point of the article: the author is recommending NOT using the SSN (a natural key) for primary keys, and instead to use an artificial, automatically generated key, so that the SSN is decoupled from the record and can potentially be updated.

A synthetic key is adding an additional dataum - so any problem that could be solved with the original data can be solved by the new data too (worst case, ignore the primary key). That is one of the best reasons to add a proper index - there is almost literally no downside. We're talking maybe a few bytes/record and arguably a trivial amount of administrative overhead.

Bearing that in mind:

1. Add a unique constraint on the natural key.

2. If the accuracy of joining by natural key is acceptable, join based on it. If that doesn't work, then a table design without a synthetic key wouldn't be fit for purpose either.

Shows how the assumption that everyone has, or reveals, a known or at least approximate birth date cascades through the system.

TFA author cites many examples from human-oriented systems, which perhaps are more common and traditional domains for RDBMS design, where there often several layers of exceptions to the rules - his argument that synthetic keys work well for these domains lands.

I personally work far more with computer-oriented systems and their data, and natural keys work well for me. When well-chosen they allow me to do an initial load of the source data for analysis, and then aggregate such databases together later on for historical analysis without fear of conflict. The data are often immutable in these domains, too.

You'll have to modify the natural key if later it is decided to add rankCategory ("best overall", "best ambience", "best value" etc) via rankCategoryId foreign key to the RestaurantRank table because a given restaurant can have the same rank in the same year under multiple categories. A synthetic key avoids you having to mess with the key to handle such cases.

Ah, how I remember changing my name. People think it only happens now and only to 0.1% of people who decide to change their gender in a leftist Western society, but somehow they forget that people have been changing family names for centuries. And of course, my work email address also changed. Now THAT is fun.

If you use a surrogate key, you still need a unique constraint in the table (probably the same columns you would otherwise call your natural PK). If your unique constraint isn't sufficient to capture the difference you mention, you need to add more columns.

However, that's strictly better than the natural PK situation, where you would need to not only add new columns to the key, but also add those columns to all referencing tables.

I think it's a joke about natural keys, based on the date added to the comment: https://github.com/ploeh/ploeh.github.com/commit/6149bcdf0e2...

I think the date was chosen because that's when the blog originated after the author left microsoft.

That was my first thought too. But like TFA says, that still wouldn't prtect you from clerical errors.

seems like a problem, you should get that czeched out

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.

The first example is why `UPDATE CASCADE` was implemented. So it's possible to use natural keys as identity without the fear of children table. At least in most databases it works.

This is only true in a system using a single database, not replicating data in external services, and not offering APIs.

And while it might work today when the service/website is still fairly small and self contained, the requirements might change at any time. So the title (You will [future] regret it) still applies to this solution

Perhaps a better example would be a fingerprint or a retinal scan instead of social security numbers as natural keys for a person.

Mailing a check to transfer money

Real 1800 vibes here.

At the end, you can directly program whatever you want into the car : change the VIN, change the odometer etc.

And you can wind up with an undriveable car this way. Particularly the odometer if you roll it back and try and sell it, your state's DMV will probably reject the title transfer.

That combination of columns being a candidate key depends on the world or domain that your dataset is modeling. In this example, I think it was a list of the top 50 restaurants in the world and so it would make a fine key. But if that database was ever expanded then definitely you would run into collision issues.