You'll regret using natural keys
blog.ploeh.dk
blog.ploeh.dk
If you use JavaScript/TypeScript, you can make them like this:
function makeSlug(length: number): string {
const validChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789";
const randomBytes = crypto.randomBytes(length);
let result = "";
for (let i = 0; i < length; i++) {
result += validChars[randomBytes[i] % validChars.length];
}
return result;
}
function makeId(tableName: string): string {
const idSpec = TABLE_NAME_TO_ID_SPEC[tableName];
const prefix = idSpec.prefix;
const slugLength = idSpec.length - prefix.length - 1;
return `${prefix}_${makeSlug(slugLength)}`;
}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)
Indeed, there's no reason you couldn't just add "_" and "-" or "." as well to complete the set. Your identifier will still be URL-safe. I've been using this type of encoding for years [1] for these kinds of ids to use in URLs, and encoding/decoding is super-fast with some bit shifts. And unlike Base64, you don't need padding characters.
[1] https://sourceforge.net/p/sasa/code/ci/default/tree/Sasa.Web...
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/...
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 should be the only key you expose to end-users, to prevent leaking data via the German Tank Problem: https://en.wikipedia.org/wiki/German_tank_problem
Only create the second key if this is data you're exposing to users, of course — for data that's only used internally, just use the 64-bit auto-incremented PK and skip the added index bloat entirely.
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.
I got some great DB cache/index performance from ULIDs with a bit of work to order the ULID timestamp bits in the way the DB's 128-bit column "uuid" sort best supported.
Now that UUIDv7 is standardized we should hopefully see good out-of-the-box collation for UUIDv7 in databases sooner rather than later.
Whether or not that's bad fully depends on your platform and the number of writes you do. If you're using a massively distributed database like Datastore, Spanner etc, you want random keys as to avoid hot spots for writes. They produce contention.
One solution to that is having more complex keys. For example, in one of our more contentious tables the index includes an account id (32bit int) and then the id of the entity being inserted. This causes inserts for a given account to still be contiguous (resulting in less fragmentation) while not creating a writing hotspot since those writes are distributed across various clients.
* not the same as your internal DB row primary keys, which in Postgres should usually be bigserial
Yes, in that there are DB technologies not built in a fashion where records are stored in a sorted order of some fashion. No, in that they are very much not common technologies. Most databases, relational, non-relational, etc have some form of a B-Tree at their core somewhere.
Another hack for advanced active-active situations where you may need to route events before replication completes: encoding the author shard / region in the lower order bytes.
There are lots of interesting primary key hacks for dealing with physical or algorithmic complications.
Because of the random portion of the key, that means you'll get good distribution so long as the distribution algorithm isn't something stupid like relying solely on the highest order bits.
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).
Let me guess - you're a Java developer, right?
If all you need is unpredictability, a minor bias is sub-optimal but not disastrous. The 256 % 62 bias used here should reduce the min-entropy per character by 5%. And you can easily minimize the bias by using larger integer than a single 8-bit byte. There few algorithms where minor biases cause a disaster, like DSA.
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;
}Multiple calls to a randomness generator can be expensive, and a waste of entropy; production-scale random string generators should still respect this and ask for a block of bytes, then encode them, but with bias correction. You're off the hook in this case, I think node's implementation of randomInt is doing exactly that for you and conserving remaining entropy in a cache.
"It's one-three-oh-dee-ee-el. Yes, I'm sure, EL as in elephant."
https://github.com/kstenerud/safe-encoding/blob/master/safe3...
Notably, confusable characters are interchangeable when being ingested (although a machine encoder MUST always produce canonical output). https://github.com/kstenerud/safe-encoding/blob/master/safe3...
So a user can confuse 1 for l, 0 for o, I for l, u for v, uppercase, lowercase etc, or the agent can say any of those over the phone, and it won't matter.
I have a note from a few years ago that 367CDFGHJKMNPRTWX may be a sufficiently unambiguous alphabet. Drop the one you like the least (probably N) to obtain a faux hex encoding.
This can be solved in user space by regenerating if the character sequences are detected, but this a) skews the distribution, and b) potentially takes time, especially when the ID generator is made to not be “too fast”. I want to generate a single ID that passes the blocklist in a timeframe that is not too fast, if that makes sense.
Is there an ID generator that takes this into consideration?
It's generally a good idea to drop vowels for this reason.
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.
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.
Yes you can.
You can support the old cus_<ID> prefix as well as the new org_<ID> prefix, but always return org_<ID> from now on
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
And in most cases I think you're also better off just using a uuid and encoding its bytes as base 32, in which case you're basically doing type id's [1]. If the "slug" portion of the id actually encodes a uuid, then it gives you the option to store it in your database using an appropriate uuid type. This will make your database much happier than using long string PK's.
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.
Case in point, the parent poster's base64 ID is 14 characters long. When encoded as base32 that's still only 17 characters (or 19 in base16), and now you have completely gotten rid of all notion of casing, which is annoying to communicate verbally.
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"
def numerize(euphonism) = euphonism.split(?-).map{$digits.find_index(it)}.map{it.to_s}.join.to_iFor numeric values, making them all a multiple of 11 is a simple way to catch all single digit errors or single transpositions.
In my databases, I often prefer integer primary keys for performance reasons. On the other hand, I don't want to expose my primary keys because they are easy to guess.
Recently I've been playing with Rust, and ended up publishing a library to encrypt IDs in they way I like:
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.
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.
https://dev.to/stripe/designing-apis-for-humans-object-ids-3...
Comes from the ye olde paper-based blogs they call newspapers. When an article is being put together it’s given a short name, sort of like a project name. This name would remain the same throughout the article’s life - from reporter through to editor - it left its trail through the process. Like a slug.
>The origin of the term slug derives from the days of hot-metal printing, when printers set type by hand in a small form called a stick. Later huge Linotype machines turned molten lead into casts of letters, lines, sentences and paragraphs. A line of lead in both eras was known as a slug.
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 need one unless you do something that requires having one. There are also things called ITINs and ATINs that look like an SSN but are not, yet can be used in place of an SSN in a huge range of SSN-required situations!
(Please don’t use the SSN as a database 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.
- 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, so a temporary one is assigned for use in patient records. Such temporary SSN:s are not coordinated nation-wide so multiple patients may have the same SSN. In some hospitals they don't even have a local standard for ID:s. The staff just makes something up on the spot. It happens that the SSN they made up collides with a valid SSN for another person.
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.
We don't do that. instead, we shove that extra bit of information into the digit following the last two digits through a table: https://da.wikipedia.org/wiki/CPR-nummer#Under_eller_over_10...
> SSN from different ID space gets assigned to immigrants; when they become citizens they are assigned a new permanent SSN
Having gone through that, my personummer didn’t change. Maybe that doesn’t happen anymore?
While this was abolished in the 90s, _some people still have these 'W' numbers_, ticking timebombs for anyone relying on them as a key.
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.
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.
I used to work for one state's Department of Motor Vehicles.
Part of why REAL ID is such a charley foxtrot is because there exists no such identifier and since every state counts approximately as a sort-of-almost sovereign nation (this is basically what the 10th Amendment guarantees), there is and can be no possible nationwide citizen identifier.
There are about 3500 different counties in the US. All of which may or may not issue their own birth certificates. States are supposed to do that now.
That's why everybody use SSN.
(People who don't drive have to get state-issued IDs that have the same number. At least, they have to if they want to buy do anything that requires proof of identification.)
a slightly more serious answer: you largely don’t. The US doesn’t have a national ID, proof of birth is not even close to standardized, etc.
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.
https://www.pcworld.com/article/424392/a-tale-of-two-women-s...
I’ll add that there is a huge difference between the SSN database that the Social Security Administration maintains, and the list of SSNs that have been associated with a person. Especially because it is very common to change a single digit of your SSN when performing credit fraud - because they’ve already burned their real one. Some people will have dozens of SSNs attached to them.
IDA was very good at determining who a person is through the graphs that represent identities in our world (names, DOBs, phone numbers, addresses, SSNs, etc.)
Note that in less than 100 years, more than half of all possible SSNs have already been used…
That happens all the time. First of all people steal SSN's and use them (and you are not the police, so it's not your responsibility to do anything about that). Second people make up fake SSN's because they don't want to give you their SSN.
People also make typo's, and you can end up with the same SSN.
An SSN is not unique in the real world.
> The wallet was sold by Woolworth stores and other department stores all over the country. Even though the card was only half the size of a real card, was printed all in red, and had the word "specimen" written across the face, many purchasers of the wallet adopted the SSN as their own. In the peak year of 1943, 5,755 people were using Hilda's number. SSA acted to eliminate the problem by voiding the number and publicizing that it was incorrect to use it. (Mrs. Whitcher was given a new number.) However, the number continued to be used for many years. In all, over 40,000 people reported this as their SSN. As late as 1977, 12 people were found to still be using the SSN "issued by Woolworth."
https://www.ssa.gov/history/ssn/misused.html
As late as the 1970s, the first 3 digits of your SSN told what office issued your number to you. And the next 2 digits told workers at that office what filing cabinet held your application.
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.
To use our professor’s car’s example, we actually do not know the database design. It could well be that the original identifier remained the primary key, and the "new id" is entered as an alias. The ID is unique in the Car table, identifying the vehicle, and is not in the CarAlias table, where the aliases are unique.
Oh, you say, but what if the bad old ID gets reused? Good question. Better question: how will the surrogate key protect you? It will not. The reused ID will be used to query the system. Without some distinguishing feature, perhaps date, it will serve up duplicates. The problem has to be handled, and the surrogate key is no defense.
Model your data on the real world. Do not depend on spherical horses.
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.
Even worse than the verbose, repetitive, and error-prone conditions/joins was the few times when something big in the schema changed, requiring a new column be added to the compound key. We'd have to trawl through the codebase and add the new column to every query condition/join that used the compound key. It sucked.
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.
Generate your own unique keys for everything; add a few more unique constraints if needed. A bit more work but never a regret.
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.
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.
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.
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.
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.
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.
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 :-)
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.
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.
> You think your surrogate key will save you? It will not.
It definitely will, say my 20+ years of experience.
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.
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.
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.
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.
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.
Performance is another major argument for synthetic keys, as they are can be made sequential, which is rarely the case for natural keys.
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.
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.
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?
This took me years to realize, and once I did things became much, much simpler.
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...)
Take for example the Danish CPR number. That's perfectly fine as a natural key; its definition is the first CPR number assigned. 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.
Or the chassis number. Also fine as a natural key. If it's changed because of a data-entry error, you also want to record a.) the date of change b.) who changed it. This opens up a whole host of auditing, monitoring, and reporting functionality that eg. lets you catch fraud, determines if a single person is being sloppy, identify mass changes in policy, notify and update external records of owners, etc.
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. Except that they do change. But when a URL changes, you don't want to just update them in the database everywhere, because again, that will break the web. You want to leave a redirect from the old to the new one. So you create a redirects table of all the other aliases that point to a given page, use it to generate server redirects, and you can throw in other data like the time of change or hit counts on each individual alias.
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.
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.
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.
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.
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.
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.
No, see more info here: http://localhost:8080/
I don't know about CPR's, but in the US SSN's get recycled. So two people can have the same "first SSN" assigned.
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.
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.)
That's the precondition for any sane UI, and sometimes it's not even obvious because the "duplication" has been transformed, reified into its own concept.
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.
Even if just for documenting issues it makes life easier. “Table whatever id 12345 is the record in question.”
I’ve just seen data / relationships change too much too often in new and interesting ways to believe in using a natural key.
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 again you don't control them. So if the license plate numbering scheme changes in some way that breaks your system, too bad. Or people without an SSN. Or people in transition because an SSN needs to be changed in a government system somewhere. Or any other number of things that happen in a government office that affect you, yet you have no control over.
Phone numbers? Well, we've already seen that mess with many messenger platforms.
Fingerprints? Guess what? They evolve over time, and your system will eventually break.
Retrofitting a system that relies on "natural" keys that have broken SUCKS.
Use a generated unique key system that YOU ALONE control.
The first rule of software design is: Don't try to be clever. You're not clever enough to see all of the edge cases that will eventually bite you.
- 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.
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.
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...
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.
And this is ALWAYS the case with identifiers that you don't control. You don't make policy decisions about them, but SOMEBODY does. And that somebody isn't aware of - and wouldn't even care - about the invariants that you ASSUMED they followed (and maybe they really did follow them, but they sure don't anymore! Oops...)
Fixing broken invariants after the fact is always a nightmare, because it only comes up once you get stuck - either you can't enter something into the database that you absolutely MUST by the weekend, or you can't change something in the database that you absolutely MUST by the weekend. So you do some last minute hacks to get things kind of working, and then it works for awhile until the next problem (usually involving your hack).
It's hardly any extra work or complexity to just use an ID generator for the primary key. You'll still have the same indices, the same foreign key linkages etc. You have no reason not to do this.
And yet somehow people always seem to fall for the "Oh cool! This existing ID does everything we want! Let's just use that instead of adding one more field to the table! I'm so clever!"
If you were using some sort of string like an email address or username, you have to think about case sensitivity and trimming white space and all sorts of preprocessing and then make sure you do it consistently EVERYWHERE
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.
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 and just lived with the fact.
-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.
Oh my, I can feel that pain. Here’s what happened last week, caused by a change in… SSN? no - in our home address.
My wife earned some unemployment benefits three years ago, which were put on a plastic card issued by The Bank. She finally found time to access the funds (she’s a middle school teacher), but when she went to The Bank, they said they didn’t have the money anymore—they’d sent it back to California. So, she called California. They were like, “No problem, we’ll send you a check. Oh, you have a new address? Let’s change it. Wait, what is happening… oh, now your account is locked, it says: potential fraud”… They needed a supervisor to unlock it, which took 20 minutes. The supervisor unlocked the account, but because it was marked as potential fraud, they couldn’t mail a check anymore. Instead, they linked the account back to The Bank (20 more minutes), and she had to go there in person with her ID to get it checked.
So, she went to The Bank. But you can’t just walk in The Bank and show your ID to get it checked; you need an appointment. And to get an appointment, you need an account with The Bank. But her California benefits account? Oh, it is marked as potential fraud - it didn’t count. So, they spent 20 minutes to open a new account for her. She got an appointment for later that day, in 4 hours, went back to The Bank, and had her ID checked (yes, the second time in one day - they need to check your ID to open an account too).
Did she get the money then? Of course not—the account is still flagged as potential fraud. No cash possible. Call California again. California agreed to mail the check to the new address, probably by some oversight.
So my question is: do these “potential fraud” flags in databases ever die a natural death?
With some hope, sincerely, a Husband of a Potential Fraudster.
Real 1800 vibes here.
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.
The drawback of enumeration is real, so if you expose this key you'll need some authentication/ authorization mecanism.
Another good thing in natural keys is that you can eliminate part of the joins. You don't have to join the father table because the key in child table is known.
I think the biggest challenge is how to map logins to people. It's very common to interpret both as the same, but they are not.
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
In the author's example, if the first column in the natural index was the city name (or city ID!), and locations are often pulled from the database by city, you'll see a read time performance benefit because each cityName's restaurants will be stored together.
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.
What to do? I favour a mixed approach: have a unique integer ID column used internally, expose a unique UUID to the public where necessary and - with a BIG DO NOT OPTIMIZE PREMATURELY warning - really think about how the data is going to be queried, updated, and inserted. Ask - does it makes sense to create a clustered index based on the table data? Is there enough data to make it worthwhile? Where can the index fail if changes need to be made? Under some circumstances, it might even make sense to use a natural key with the integer column included right at the end!
The only hard rule I have is using UUIDs for clustered indexes. Unless the tables are teeny-tiny, the system is most likely suffering without anyone being aware of it.
Note for readers: Postgres doesn't do that
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.
This was the dumbest technical decision I have ever been asked to be a part of.
People change names, their phone numbers, passport numbers. Governments change their numbering schemes all the time. Corporations merge and split. Heck, even governments merge and split, surprisingly often, at the municipal level. All without your knowledge or consent. And you're left wondering why you suddenly have duplicate key errors in your database.
The only key that you can trust is one that you, and only you, control. That's the point of the surrogate key. Whether it's BIGINT or UUID v7 is beside the point.
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
(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.)
An example is using a car's chassis number as the key for the record describing that vehicle.
Since many refugees don’t know (or can’t prove) their birthdate, they are given first of January. And enough first of Januaries are handed out that for some years there aren’t enough valid numbers, so numbers that fail the checksum are handed out too.
1. When duplication occurs and goes unnoticed because the natural key isn't being used, and then perhaps the problem is "corrected" by an administrator in a way that doesn't make sense.
For example, someone sets up an account with an street address A, then forgets they had it and sets up an account with street address B. They call and complain that they can't find an old order, or whatever, and the duplication is discovered. The administrator later clobbers one of the addresses but both are in use. A natural key (behaviorally speaking) may have presented the duplication, assuming address isn't part of the natural key. This can be satisfied by uniqueness constraints, etc.
2. When you are browsing the database, you see a bunch of synthetic keys and have to perform various joins to be able to see the relevant data. The synthetic keys make joins easier to write, but make may make ad-hoc queries more time-consuming and difficult.
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.
People change.
Unrelated - if you have a list of emails or SSNs or license plates or VINs - we can think of these as foreign keys to a database we don't control.
Most entities are tied to a version record (more or less a God object). But some span across versions. I’m able to have the entities that aren’t tied to a version float around because they reference the natural key of the versioned entities, minus the version’s synthetic key.
It's easy to just use an auto-generated sequence... but then you start having to export/import or otherwise merge data, and other manipulations that often use the primary key and find there are collisions everywhere. There can also be problems when needing to support multiple databases, or update versions.
UUIDs (or equivalent synthetic keys that are independent of the database itself) are often the best answer for this reason.
Having been bitten by sequences so many times in the past, I find them to often be more trouble than natural keys in the first place - just a lazy approach.
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.
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.
Be Careful with UUID or GUID as Primary Keys https://news.ycombinator.com/item?id=14523523
For example, you have a company, and every employee has a unique employee number generated by HR... until the company merges with another, that also has unique employee numbers, and suddenly the identifier becomes the tuple (organization, employee number) that becomes unique. If you've used the employee number as a foreign key in other tables, you have to change those too.
This is a somewhat contrived example, but I've had enough real, annoying examples happen to me in my career that I avoid natural keys.
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.
I didn't like the UUIDs at first, but it ended up being an unexpected boon for generic code to use the same key type for different entity kinds. What was less of a boon was that the string identifiers also have to be unique, but can change at any time, and depend on three-level namespacing (yes really) so there's far more that has to be tracked and enforced. The names are very important for UI use cases, but can never be the way that records reference other records, because that would just make them much harder to change with confidence.
The essay seems to assume you'll have either unique natural keys or unique synthetic keys, but having now worked on a project that does both at the same time for many entities, I think it's a third option worthy of its own analysis. My experience was negative but I can't deny that the end result ticks a lot of functional boxes.
From the outside you won’t see it, but internally it saves a lot of headaches.
Space, speed, migrations, I have just never seen an actual downside to using a surrogate.
And if you are absolutely sure, add some unique constraints. Easier to change when you inevitably have to.
Mainly; I’ve learned that my initial assumptions are never correct.
Notice that even if you believe that you should 'never use natural keys', that is NOT the same thing as 'always add a generated synthetic key to every table as the primary key'. You should NOT always add a generated key to every table, even if you use a trash ORM that really wants you to do this.
Correct. Infamously correct
Every "natural key" will have some way of fumbling things down the line.
Nothing is unique, not even joining your natural key with other deduplication info. Just save yourself the trouble.
https://www.gao.gov/products/gao-05-1016t
In plain text form:
https://www.gao.gov/assets/a112177.html
I remember when the school I went to changed from using SSNs for all student records to using no SSNs at all. They had to notify everyone about the changes, repeatedly. It was obviously very expensive. Don't use SSNs as keys.
In other words - using surrogate key is an attempt (and the wrong one!) to fix the problem of missing important information in the database.
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.
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.
I'd just say this is not so right: As it turned out, though, whoever made that piece of software knew what they were doing, because the mechanic just changed the chassis number, and that was that.
Because, yes, everybody can just reprogram your car and change its VIN and stuff.
The manufacturer software have protection against that. But nothing prevents you from using another software. 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.
Most of the times using an auto incrementing integer or UUID is just fine.
In NOSQL there's usually a synthetic ID used to uniquely identify a document, I've never seen people using natural keys or compound keys.
The student made a structure that can be seen as a unit or table in a database. Is the database not clever enough to synthesize a unique id? Why does the programmer need to care about keys?
It is clear that the end user will not know anything about a key, it might be a byte offset in a file or a memory pointer. Why does the programmer need a key?
Identifiers like these aren't always available, but within many domains will be sufficient.
The idea here is not that these keys can't be somehow "invalid," but rather that it isn't our system's problem -- it belongs to some other authority.
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.
While I too was taught natural keys, or combined keys, were the "intended" way to identify data, I was corrected very quickly that artificial primary keys were much more reliable and more convenient due to all the auto increment features etc. I am surprised this is even talked about anymore.
Ok sure, but then you have 2 restaurants which are indistinguishable from one another in your database. It doesn't matter that the thing has a unique id next to it. You can't know which is which. That's not useful.
One woman got married and changed her name; se became really upset when she found out that we couldn’t change her login.
That's a strange assumption.
Arguably not a natural key, or at least a contrived example, but: any downsides?
My current company uses natural keys all over the place and its a total clusterfuck trying to associate data at times.
With surrogate keys you end up with duplicate entities whenever a partition heals (supposing the entity appeared to both partitions while they were still separate).
Quick question for database gurus here. Is it ok to have a currency table using the currency code as primary key?
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).
An an footnote, I'll add this doesn't mean that you need to have complexities like record versioning, record history, or anything. But couched in those conceptual terms where those things are possible, is a happy and safe space to be. In this space, a database records entries, as if they were each a single paper form with boxes where you fill out, in pencil, to erase if you like or not, the particulars of the thing you're recording. This form comes pre-stamped with a number: the record key.
In this cozy little world, you can be imperfect, and mistakenly (or deliberately, as your use case requires), file multiple slips that refer to the same logical thing, and yet all have different file ("record") numbers - or keys.
Say a customer used a different government-issued ID to re-register with your bank. A year down the line you notice that you have two identities for the same person. It might be a meh for an online game but if you are a bank this can make you run afoul of regulations. Can you handle the merge of all the relevant data? And merging is usually the easier of the two glitches - can you handle a split?
The point is that identity just like security requires thought from the start of the design.
For a domain where identity is really hairy (although admittedly with less consequences for screwing up) see https://news.ycombinator.com/item?id=4493959 "The music classifying nightmare". Also https://en.wikipedia.org/wiki/Identity_(philosophy)#Metaphys... for some philosophical perspective.
I think the date was chosen because that's when the blog originated after the author left microsoft.
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.
Even if the bicycle is older than the car, perhaps even more so.
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.
Suppose you take the advice of this article, and use, say, social security numbers to identify people. (Let's ignore the fact that this only works for the U.S.) Suppose one fine day, somebody tries to enter in a record about a new person--but some typo has happened somewhere, and the new person's SSN clashes with somebody's SSN who is already in the system.
Sure, the database will notify you that something went wrong. But how do you know which social security number is correct, and which is incorrect? You will have to find a set of fields which uniquely identifies each person ANYWAYS. I.e. you'll have to differentiate them by name, birthday, place of birth, etc etc.
Now even worse!!! What if somebody attempts to add the same person TWICE to your database, but mistypes their social security number. Now your database can't even tell you that something has gone wrong. It will happily record duplicate or contradictory information about the same person--and in order to resolve the mess, again, you have to find out what uniquely identifies the people ANYWAYS.
Now, even worser than worse--what if you have taken the advice of this article, and you haven't bothered to identify a set of fields which are genuinely unique to each person. You just have a social security number and a name. How are you going to even going to correct the fact that John Smith is in your system twice, when you have ten John Smiths? How can you possibly tell which two John Smiths are the same John Smith?
Yeah, it take some time and careful thinking to properly come up with natural keys for your entities. But unless you do, you haven't actually specified your entities at all. This is one area where long years of experience really pay off. Expert data modelers spend years and decades honing their craft, observing the work of others, etc etc. Eventually they acquire the wisdom needed to be able to know what kind of information is really needed to nail down what kind of entity the database needs to know about.
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.
For relational setups this is the way to go though. I prefer the combo approach though - autoincrementing numbers plus a UUID in another column.
Even the example for the restaurant and "time based id number" are bad because they all indicate that you just badly identified the entities (in DDD terms) and their identity.
So being bad at DDD doesn't mean that you can't use natural keys (although I myself have arguments against them)
Keys are about the integrity of your application(s) and preventing corner cases by making them impossible.
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.