> A relation should be identified by a natural key that reflects the entity’s essential, domain-defined identity — not by arbitrary or surrogate values.
I fairly strongly disagree with this. Database identifiers have to serve a lot of purposes, and natural key almost certainly isn’t ideal. Off the top my head, IDs can be used for:
- Joins, lookups, indexes. Here data type can matter regarding performance and resource use.
- Idempotency. Allowing a client to generate IDs can be a big help here (ie UUIDs)
- Sharing. You may want to share a URL to something that requires the key, but not expose domain data (a URL to a user’s profile image shouldn’t expose their national ID).
There is not one solution that handles all of these well. But using natural keys is one of the least good options.
Also, we all know that stakeholders will absolutely swear that there will never be two people with the same national ID. Oh, except unless someone died, then we may reuse their ID. Oh, and sometimes this remote territory has duplicate IDs with the mainland. Oh, and for people born during that revolution 50 years ago, we just kinda had to make stuff up for them.
So ideally I’d put a unique index on the national ID column. But realistically, it would be no unique constraint and instead form validation + a warning on anytime someone opened a screen for a user with a non-unique ID.
Then maybe a BIGINT for database ID, and a UUID4/7 for exposing to the world.
EDIT: Actually, the article is proposing a new principle. And so perhaps this could indeed be a viable one. And my comment above would describe situations where it is valid to break the principle. But I also suspect that this is so rarely a good idea that it shouldn’t be the default choice.
sitharus · 5h ago
This isn’t a new principle, it was part of database design courses in the early 2000s at least. However from a couple of decades of bitter experience I say external keys should never be your primary keys. They’ll always change for some reason.
Yes you can create your tables with ON UPDATE CASCADE foreign keys, but are those really the only places the ID is used?
Sometimes your own data does present a natural key though, so if it’s fully within your control then it’s a decent idea to use.
whstl · 3h ago
Advice used to be that "natural keys can only be primary if they don't change" but there was always an exception to the rule after a few months.
On the other hand, I remember a CEO wanting to be #1 in the database, so even non-natural primary keys can change... haha.
jandrewrogers · 6h ago
> Allowing a client to generate IDs can be a big help here (ie UUIDs)
Trusting the client to generate a high-quality ID has a long history of being a bad idea in practice. It requires the client to not be misconfigured, to not be hacked, to not be malicious, to not have hardware bugs, etc. A single server can generate hundreds of millions of IDs per second and provides a single point of monitoring and control.
treyd · 5h ago
In context I read that as database client, meaning the application server (which is a client to the database) providing the service to the user. Having that be able to generate IDs could be useful when needing to refer to the same entity, even if there is data that has to exist in some separate database for some reason.
adamcharnock · 5h ago
That is indeed what I had in mind, although I did leave it intentionally vague as everyone can asses what’s best for their own situation
qazxcvbnm · 5h ago
I’ve once attempted to implement a solution where ids are generated by UUIDv5 from a certain owner and the relationship of the new item to the owner; that way, users cannot generate arbitrary ids but can still predict ahead of time their new ids to ease optimistic behaviour.
adrianmsmith · 2h ago
I think if you have (tenant, uuid) or (user, uuid) or whatever as your primary key, that's fine. If that tenant or user or whatever generates rubbish keys, that's their problem.
friendzis · 2h ago
> Joins, lookups, indexes.
You both want to control these values within your database engine, at least so that they are actually unique within the domain, and there is no real reason for it to be user-controlled anyway, as they are used referentially.
> Idempotency.
User supplied tokens for idempotency are mostly useful within the broader context of application sitting on top of database, otherwise they become subject to the same requirements internally generated ones are, without control, which is a recipe for disaster.
> Sharing
Those are the same idempotency tokens from previous points with you as the supplier. In some cases you want to share them across prod/stage/dev environments, in some cases you may want to explicitly avoid duplicates, in some cases you don't care.
All these use cases are solved with mapping tables / classifiers.
Example: in an asset management system you need to expose and identifier of a user-registered computer, that is built using components procured from different suppliers, with their own identification schemas, e.g. exposing keyboard/mouse combo as one component with two subcomponents or two (possibly linked) components.
This requires you to use all those listed identifier types in different parts of the system. You can bake those in database/schema design, or employ some normalization and use "native" identifier and mapping tables.
Akronymus · 2h ago
As someone who currently has to deal with a database that has NO surrogate keys, 100% agree with having surrogate keys always. Compound natural keys are just plain awful to deal with.
Altough, it doesn't help that we have to do string manipulation to extract part of those natural keys from other fields that contain a bunch of data as one long string.
jiggawatts · 6h ago
> will absolutely swear that there will never be two people with the same national ID...
I suddenly got flash-backs.
There are duplicate ISBN numbers for books, despite the system being carefully designed to avoid this.
There are ISBN numbers that have invalid checksums, but are valid ISBNs with the invalid number in the barcode and everything. Either the calculation was incorrectly done, or it was simply a mis-print.
The same book can have hundreds of ISBNs.
There is no sane way to determine if two such ISBNs are truly the same (page numbers and everything), or a reprint that has renumbered pages or even subtly different content with corrected typos, missing or added illustrations, etc...
Our federal government publishes a master database of "job id" numbers for each profession one could have. This is critical for legislation related to skilled migrants, collective workplace agreements, etc...
The states decided to add one digit to these numbers to further subdivide them. They did it differently, of course, and some didn't subdivide at all. Some of them have typos with "O" in place of "0" in a few places. Some states dropped the leading zeroes, and then added a suffix digit, which is fun.
On and on and on...
The real world is messy.
Any ID you don't generate yourself is fraught with risk. Even then there are issues such as what happens if the database is rolled back to a backup and then IDs are generated again for the missed data!
bruce511 · 6h ago
I'm with you. I've used natural keys in the past, and they've always been a problem eventually.
On the other hand I've used surrogate keys for 20 years, and never encountered an issue that wasn't simple to resolve.
I get there are different camps here, and yes your context matters, but "I'm not really interested in why natural keys worked for you." They don't work for me. So arguments for natural keys are kinda meh.
I guess they work for some folk (shrug).
Jarwain · 6h ago
Why have both a database ID and UUIDv7, versus just a UUIDv7?
jandrewrogers · 6h ago
Actually, it should be a database ID and an encrypted database ID, which doesn’t require storing a second ID. Even better, you can make that key unique per session so that users can’t share keys. For security reasons, it is a bad idea to leak private state, which UUIDv7 does.
A single AES encryption block is the same size as a UUID and cheap to compute.
Jarwain · 5h ago
So you've got a database ID, either serial or uuid? And you encrypt it when you send it to the user, maybe encrypted against their JWT or something to maintain stateless sessions?
And I guess if the user references specific resources by ID you'd have to translate back? Assuming the session has been maintained correctly,which I guess is a plus for stateful sessions. And it doesn't really matter if you get a collision on the encrypted output.
I spent enough time in the nibling comment talking about my doubts about that advice not to publicly share the identifying key. But I'll add one more point; it feels like a bunch of added complexity for marginal benefit.
lukevp · 5h ago
Can you explain this a bit more or link to something? I don’t really understand. What’s encrypted? A guid? A monotonic integer ID? Is the encrypted ID only used for user facing stuff? How is it decrypted? What do you gain by this?
adamcharnock · 5h ago
> A single AES encryption block is the same size as a UUID and cheap to compute.
I didn’t realise this! The UUID spec mandates some values for specific digits, so I assume this would not be strictly valid UUIDs?
jandrewrogers · 5h ago
They would not be valid UUIDs, it is an opaque 128-bit value.
To be honest many companies are not using strictly standard conforming UUIDs everywhere, and UUID has become a synonym for opaque 128-bit identifier. Consumers of the value just need them to be unique. All standard UUID versions are sometimes prohibited in environments, except maybe UUIDv8, so this semantic ambiguity is helpful.
Technically, you could make it present as a standard UUIDv4 or UUIDv8 by setting a few bits, as long as you remember to add them back if you ever need to decrypt it. The entropy might be a bit off for a UUIDv4 if someone actually checks but you can guarantee the uniqueness.
Using AES to produce a UUID-like key is an old but useful trick. Both ARM and x86 do that encryption in hardware — it is cheaper to generate than the standardized UUID versions in most cases.
sroussey · 6h ago
There is a security principle to not expose real identifiers to the outside world. It makes a crack in your system easier to open.
Jarwain · 5h ago
Idk that reeks of security through obscurity to me. Your authorization/permission scheme has got to be fubar'd if you're relying on obscuring IDs to prevent someone from accessing a resource they shouldn't.
I'm sure I'm missing something obvious, but I'm not sure what other threat vectors there are, assuming it's in conjunction with other layers of security like a solid authorization/access control scheme.
I guess I'm not the biggest fan for a few reasons. I'd rather try and design a system such that it's secure even in the case of database leak/compromise or some other form of high transparency. I don't want to encourage a culture of getting used to obscurity and potentially depending on it instead of making sure that transparency can't be abused.
Also, it just feels wasteful. If you have two distinct IDs for a given resource, what are you building your foreign keys against? If you build it against the hidden one, and want to query the foreign table based on user input, you've gotta either do a join or do a query to Get the hidden key just to do another query. It just feels wasteful.
EDIT: apparently RFC 4122 even says not to assume UUIDs are hard to guess and shouldn't be used for security capabilities. So if it shouldn't be depended on for security, why add all this complexity to keep it secure?
jandrewrogers · 5h ago
The point you may be missing is that the key itself contains information about records in the database that you don’t have access to. There are many famous examples in literature (e.g. the German Tank Problem [0]) of useful attacks on known unique serials to infer hidden information without access. In a database context, the keys for the records you are authorized to see can tell you much about the parts of the database to which you have no access.
Strong randomization of record serials mitigates these attacks.
I thought we were talking about UUIDv7, which is random enough to make this not a problem right?
jandrewrogers · 6h ago
This takes an overly simple view of what domains can look like. There are data models that necessarily violate these principles, and they aren’t all that rare.
Some examples:
> A relation should be identified by a natural key that reflects the entity’s essential, domain-defined identity
In some domains there is no natural key because the identity is literally an inference problem and relations are probabilistic. The objective of the data model is to aggregate enough records to discover and attribute natural keys with some level of confidence. A common class of data models with this property are entity resolution data models.
> All information in the database is represented explicitly and in exactly one way
Some data models have famously dual natures. Cartographic data models, for example, must be represented both as a graph models (for routing and reachability relationships) and as geometric models (for spatial relationships). The “one true representation” has been a perennial argument in mapping for my entire life and both sides are demonstrably correct.
> Every base relation should be in its highest normal form (3, 5 or 6th normal form).
This is one of those things that sounds attractive because it ignores that it requires no ambiguities about domain boundaries or semantics, which doesn’t exist in practice. I bought into this idea too when I was a young and naive data modeler. Trying to tamp out these ambiguities adds an unbounded number of data model epicycles that add a lot of complexity and performance loss. At some point, strict normalization is not worth the cost in several aspects.
In almost all cases, it is far more important that the data model be efficient to work with than it be the abstract platonic ideal of a domain model. All of these principles have to work on real hardware in real operational environments with all of the messy limitations that implies.
Akronymus · 2h ago
I strive to keep most things in at least the 3rd NF. Except stuff like addresses or names, those I intentionally don't push to be normalized, as they make up a single datum anyways, and normalizing creates more problems than it solves IME.
friendzis · 55m ago
Addresses and names are nice, well-known examples for cross-domain data. It's not that attempts at normalizing these structural datums create problems per se, but rather there is no single true normalization, therefore wrong normalizations start causing problems.
Akronymus · 47m ago
Yeah, normalizing inherently introduces constraints on the data. For example, normalizing to first and last name implies no middle names/having a first and last in the first place.
Also, first and last names depend on the culture. Oh and people can have more than 1 name (as in distinct names, rather than multi part names. Some cultures use different names with different social circles).
Easier to just let them put their preferred name into a freeform text field.
mrkeen · 7h ago
> Principle of Essential Denotation (PED): A relation should be identified by a natural key that reflects the entity’s essential, domain-defined identity — not by arbitrary or surrogate values.
Is national_id really a natural key, or is it someone else's synthetic key? If so, should the owner of that database have opted for a natural key rather than a synthetic key?
I was going to comment on this. Natural keys sound like a good idea and they should enforced maybe by using a unique constraint.
Natural keys are important. But the real world and the databases that represent them are messy. People’s identities get stolen. Data entry mistakes and integration between systems fail and leave the data in a schizophrenic state.
In my experience I find arguments about natural keys unproductive. I usually try to steer the conversation to the scenarios I mentioned above. Those who listen to me will have a combination of synthetic and natural keys. The first is used to represent system state. The second is used to represent business processes.
atomicnumber3 · 7h ago
Natural keys are also all too often PII. A surrogate key that's just pure entropy is much safer to blast all over the place in logs and error messages and so on.
rawgabbit · 7h ago
I usually encourage people to place all PII in a separate table. Only those who engage with customers e.g., verifying customers identities should have access. Furthermore images of customer identity cards are strictly forbidden. You can enter their passport number, name, address, birthdate etc. but copies of identity documents will make you a target of hackers and angry customers. The rep can ask the customer to show the document or in the worst case present a copy but the copy should immediately be deleted.
sroussey · 6h ago
PII in a separate db. Encrypted like you would a credit card card number.
BTW: email+password should be separated too. An early draft of GDPR specifically mentioned that, though the final version got less into the weeds.
I’m sure if you vibe code any of this, it will all be plaintext, lol.
izacus · 5h ago
Programming software that accepts a national ID in our woods is usually the trial by fire for junior developers when they find out that "unique national ID" isn't actually unique for many historic reasons :)
tuatoru · 7h ago
The "natural key" for a (natural) person is compound: full name and mother's full name, plus date, time and place of birth. Your birth certificate is your primary identification document.
However that still runs into problems of nondurability of the key in cultures that delay naming for a few years. To name one problem.
So yeah, use a big enough integer as your key, and have appropriate checks on groups of attributes like this.
However, if you are only interested in citizens, then a "natural" key is the citizen id issued by the government department responsible for doing that. (Citizen is a role played by a natural person so probably doesn't have too many attributes.) I still wouldn't use that as a primary key on the citizen table, though.
stevoski · 5h ago
I know someone who doesn’t know when she was born, nor who her mother is.
She doesn’t have a birth certificate.
She was born in a country that was enduring several years of brutal war.
I know another person whose national ID was changed. Systems that use national ID as primary key failed to accept this change.
> The "natural key" for a (natural) person is compound: full name and mother's full name, plus date, time and place of birth.
All it would take for a Bobby Tables[0] moment is a mother to have twins delivered by caesarean and given the same name... The same name thing has already been done[1].
I’m going to have to disagree. What happened what national ids are expanded to include letters? Or extra digits? I’ve lived through this mess a few times in the migrations are painful and take a ton of time away from solving actual business problems. For example: us telephone numbers. The exchange (middle 3 digits), used to physically Geolocate a consumer, because everybody had land lines. I worked on a project and an investment firm that was using it as a primary key to identify a users’s location. Holy old macro was that an unbelievably expensive and painful migration. Don’t do this.
Instead, follow this principle: never ever use an externally assigned identifier as a primary key in your database.
Instead: link tables with simple integers. Do not use these integers for any other purpose and assume unordered. Never let these integers leave the app. When exchanging information between systems or third parties generate a suitable external identifier with a alphabetical prefix.
xlii · 7h ago
I have a joke in context that I often like to tell:
Devil captured Physicist, Engineer and Mathematician. He gave each of them big can of spam and locked them in the empty room saying „you will be here for 2 weeks - open the can and survive or die to starvation”. After 2 weeks Devil opens Physicist cell. It’s covered floor to the ceiling in complex scribbles. One piece of wall is clean of etching but small dent is visible. Can of span is opened and eaten clean, Physicist sits in corner visibly annoyed. Next one is Engineer. Cell walls are covered in multiple dents and pieces of spam. Engineer is bruised almost as much as the can, but it is ultimately opened and engineer is alive.
Finally the Devil opens Mathematician cell and find him dead. Only „given the cylinder” is etched on the wall.
—-
Puent isn’t about engineering but it always helped me to set limits between software engineering and computer science.
pretoriusdre · 6h ago
I really don't like using natural keys as primary keys.
Natural keys sometimes need to change for unforeseen reasons, such as identity theft, and this is really tricky to manage if those keys are cascaded into many tables as foreign keys.
So, don't use natural keys as primary keys. Put them in as surrogate keys, ideally with a unique constraint.
AnonHP · 8h ago
Seems like this article places too much emphasis on normalization, which is appropriate for many cases, but may be a huge cost and performance issue for requirements like reporting. You may probably need different kinds of schema and data storage structures for different requirements in the same application, which in turn may result in duplicated data, but results in acceptable trade offs.
weinzierl · 7h ago
" Every base relation should be in its highest normal form (3, 5 or 6th normal form). "
If I remember my database lessons correctly there is no strictly highest normal form. It progresses from 1NF to BCNF, but above that it is more choosing different trade-offs.
Even below it is always a trade-off with performance and that is why we most of the time aim for 3NF, and sometimes BCNF.
plank · 4h ago
There are big disadvantages from choosing e g. 5th normal form: any changing in business requirements leads to a big rewrite and data conversion.
Never seen successful projects choosing beyond 3rd/BCNF.
moi2388 · 7h ago
That’s what I was taught as well. And even then I use it more as a rule of thumb
zeroCalories · 6h ago
Putting aside performance implications, I get kinda irritated by having to do joins for basic queries all the time.
photios · 3h ago
"Databases are representations of reality"
The national ID example is funny. Let me give you a dose of reality concerning national IDs.
- I've seen cases with my country's national ID numbers containing duplicates due to human error.
- National ID's for people can change. In my country, children being adopted, get a different ID after adoption.
- There exist people without a national ID. Or people that don't want you to know their national ID.
peanut-walrus · 5h ago
No. Real life rarely has natural keys that are unique and do not change. For example the national id number in several countries can change in some circumstances...and that is already a synthetic key.
stevoski · 5h ago
Bad luck if you don’t yet have (or know) your national ID.
National id is not something issued at birth in the country I live in. It’s something applied for at a certain age.
sitharus · 5h ago
Where I live there’s no such thing as national ID. There’s a few documents that can be used as such depending on the purpose, and some of those change the number on every update!
Never trust something outside your system to be stable.
RedShift1 · 2h ago
Article sounds like it was written from a purely theoretical perspective and not from what happens and the requirements in real life.
madduci · 7h ago
Many of the principles and also the example provided for PED cannot be mapped easily through an ORM library and AFAIK Java JPA doesn't handle it too.
Why does it matter? I have seen that many developers rely totally only on the code to manage entities on the database, instead of relying on prepared statements and pure SQL queries. This obviously opens a door for poor optimisation, since these Entity Management libraries don't support certain SQL capabilities.
jbverschoor · 7h ago
That’s non argument. Just use a better ORM. Hibernate is able to do that for about 20 years.
That said, I’m not a fan of natural keys as primary keys. Especially composite keys. This just takes everybody back to the 80s/early 90s.
It only makes sense when there’s a huge storage benefit
weinzierl · 7h ago
" Every base relation should be in its highest normal form (3, 5 or 6th normal form). "
If I remember my database lessons correctly there is no strictly highest normal form. It progresses from 1NF to BCNF, but above that it is more choosing different trade-offs.
Even below it is always a trade-off with performance and that is why we most of the time aim for 3NF, and sometimes BCNF.
traches · 2h ago
Ohhhhh absolutely not, thank you. I want my IDs to have absolutely no meaning whatsoever.
moi2388 · 7h ago
“ Principle of Full Normalization (POFN) : Every base relation should be in its highest normal form (3, 5 or 6th normal form)”
No it shouldnt.
JSR_FDED · 5h ago
Please, feel free to elaborate so we can all learn
threeseed · 5h ago
It's ideological purity which has no place in the real world.
Fully normalised structures are slow, dangerous and place expensive and frustrating burdens on upstream users reducing the amount of value that can be extracted from the data.
I've been working around data for 20+ years and not once seen a dataset where tables could be blindly joined without some conditions. In these situations it can be better to just pre-join them so the business logic is captured.
You want to be pragmatic and have a healthy and constructive mix.
jiggawatts · 6h ago
The "natural ID" for people design reminds me of a story from a state department of education: They had two students, both named John Smith Jr. They were identical twins and attending the same class.
They had the same birth date, school, parents, phone number, street address, first name, last name, school, teachers, everything...
The story was that their dad was John Smith Sr in a long line of John Smiths going back a dozen generations. It was "a thing" for the family line, and there was no way he was going to break centuries of tradition just because he happened to have twins.
Note: In very junior grades the kids aren't expected to memorise and use a student ID because they haven't (officially) learned to read and write yet! (I didn't use one until University.)
Pamar · 2h ago
Same first name? For twins?
I find it very difficult to believe this is not prevented by some law.
msla · 5h ago
The example works if and only if there's one National ID per person.
That's not true for SSNs. It's not true in that it is false. My statement that it is false is, in point of fact, true, and therefore not up for debate.
> Most persons have only one SSN. In certain limited situations, SSA can assign you a new number. If you receive a new SSN, you should use the new number. However, your old and new number will remain linked in our records to ensure that your earnings are credited properly. This could affect your benefits.
Maybe there are countries where it is the case that nobody ever gets multiple National IDs. Maybe there are countries without fraud and where everyone can and will update their records when the government does. Maybe there is a veritable Utopia on Earth, a Cockaigne of validated data and reasonable deadlines.
cess11 · 5h ago
"Databases are representations of reality"
"tell the truth that is out there"
Both truth and representation are very slippery, many-faceted concepts, encumbered with millennia of use and philosophy. Using them in this way is deceptive to the junior and useless to the senior.
I fairly strongly disagree with this. Database identifiers have to serve a lot of purposes, and natural key almost certainly isn’t ideal. Off the top my head, IDs can be used for:
- Joins, lookups, indexes. Here data type can matter regarding performance and resource use.
- Idempotency. Allowing a client to generate IDs can be a big help here (ie UUIDs)
- Sharing. You may want to share a URL to something that requires the key, but not expose domain data (a URL to a user’s profile image shouldn’t expose their national ID).
There is not one solution that handles all of these well. But using natural keys is one of the least good options.
Also, we all know that stakeholders will absolutely swear that there will never be two people with the same national ID. Oh, except unless someone died, then we may reuse their ID. Oh, and sometimes this remote territory has duplicate IDs with the mainland. Oh, and for people born during that revolution 50 years ago, we just kinda had to make stuff up for them.
So ideally I’d put a unique index on the national ID column. But realistically, it would be no unique constraint and instead form validation + a warning on anytime someone opened a screen for a user with a non-unique ID.
Then maybe a BIGINT for database ID, and a UUID4/7 for exposing to the world.
EDIT: Actually, the article is proposing a new principle. And so perhaps this could indeed be a viable one. And my comment above would describe situations where it is valid to break the principle. But I also suspect that this is so rarely a good idea that it shouldn’t be the default choice.
Yes you can create your tables with ON UPDATE CASCADE foreign keys, but are those really the only places the ID is used?
Sometimes your own data does present a natural key though, so if it’s fully within your control then it’s a decent idea to use.
On the other hand, I remember a CEO wanting to be #1 in the database, so even non-natural primary keys can change... haha.
Trusting the client to generate a high-quality ID has a long history of being a bad idea in practice. It requires the client to not be misconfigured, to not be hacked, to not be malicious, to not have hardware bugs, etc. A single server can generate hundreds of millions of IDs per second and provides a single point of monitoring and control.
You both want to control these values within your database engine, at least so that they are actually unique within the domain, and there is no real reason for it to be user-controlled anyway, as they are used referentially.
> Idempotency.
User supplied tokens for idempotency are mostly useful within the broader context of application sitting on top of database, otherwise they become subject to the same requirements internally generated ones are, without control, which is a recipe for disaster.
> Sharing
Those are the same idempotency tokens from previous points with you as the supplier. In some cases you want to share them across prod/stage/dev environments, in some cases you may want to explicitly avoid duplicates, in some cases you don't care.
All these use cases are solved with mapping tables / classifiers.
Example: in an asset management system you need to expose and identifier of a user-registered computer, that is built using components procured from different suppliers, with their own identification schemas, e.g. exposing keyboard/mouse combo as one component with two subcomponents or two (possibly linked) components.
This requires you to use all those listed identifier types in different parts of the system. You can bake those in database/schema design, or employ some normalization and use "native" identifier and mapping tables.
Altough, it doesn't help that we have to do string manipulation to extract part of those natural keys from other fields that contain a bunch of data as one long string.
I suddenly got flash-backs.
There are duplicate ISBN numbers for books, despite the system being carefully designed to avoid this.
There are ISBN numbers that have invalid checksums, but are valid ISBNs with the invalid number in the barcode and everything. Either the calculation was incorrectly done, or it was simply a mis-print.
The same book can have hundreds of ISBNs.
There is no sane way to determine if two such ISBNs are truly the same (page numbers and everything), or a reprint that has renumbered pages or even subtly different content with corrected typos, missing or added illustrations, etc...
Our federal government publishes a master database of "job id" numbers for each profession one could have. This is critical for legislation related to skilled migrants, collective workplace agreements, etc...
The states decided to add one digit to these numbers to further subdivide them. They did it differently, of course, and some didn't subdivide at all. Some of them have typos with "O" in place of "0" in a few places. Some states dropped the leading zeroes, and then added a suffix digit, which is fun.
On and on and on...
The real world is messy.
Any ID you don't generate yourself is fraught with risk. Even then there are issues such as what happens if the database is rolled back to a backup and then IDs are generated again for the missed data!
On the other hand I've used surrogate keys for 20 years, and never encountered an issue that wasn't simple to resolve.
I get there are different camps here, and yes your context matters, but "I'm not really interested in why natural keys worked for you." They don't work for me. So arguments for natural keys are kinda meh.
I guess they work for some folk (shrug).
A single AES encryption block is the same size as a UUID and cheap to compute.
And I guess if the user references specific resources by ID you'd have to translate back? Assuming the session has been maintained correctly,which I guess is a plus for stateful sessions. And it doesn't really matter if you get a collision on the encrypted output.
I spent enough time in the nibling comment talking about my doubts about that advice not to publicly share the identifying key. But I'll add one more point; it feels like a bunch of added complexity for marginal benefit.
I didn’t realise this! The UUID spec mandates some values for specific digits, so I assume this would not be strictly valid UUIDs?
To be honest many companies are not using strictly standard conforming UUIDs everywhere, and UUID has become a synonym for opaque 128-bit identifier. Consumers of the value just need them to be unique. All standard UUID versions are sometimes prohibited in environments, except maybe UUIDv8, so this semantic ambiguity is helpful.
Technically, you could make it present as a standard UUIDv4 or UUIDv8 by setting a few bits, as long as you remember to add them back if you ever need to decrypt it. The entropy might be a bit off for a UUIDv4 if someone actually checks but you can guarantee the uniqueness.
Using AES to produce a UUID-like key is an old but useful trick. Both ARM and x86 do that encryption in hardware — it is cheaper to generate than the standardized UUID versions in most cases.
I'm sure I'm missing something obvious, but I'm not sure what other threat vectors there are, assuming it's in conjunction with other layers of security like a solid authorization/access control scheme.
I guess I'm not the biggest fan for a few reasons. I'd rather try and design a system such that it's secure even in the case of database leak/compromise or some other form of high transparency. I don't want to encourage a culture of getting used to obscurity and potentially depending on it instead of making sure that transparency can't be abused.
Also, it just feels wasteful. If you have two distinct IDs for a given resource, what are you building your foreign keys against? If you build it against the hidden one, and want to query the foreign table based on user input, you've gotta either do a join or do a query to Get the hidden key just to do another query. It just feels wasteful.
EDIT: apparently RFC 4122 even says not to assume UUIDs are hard to guess and shouldn't be used for security capabilities. So if it shouldn't be depended on for security, why add all this complexity to keep it secure?
Strong randomization of record serials mitigates these attacks.
[0] https://en.wikipedia.org/wiki/German_tank_problem
Some examples:
> A relation should be identified by a natural key that reflects the entity’s essential, domain-defined identity
In some domains there is no natural key because the identity is literally an inference problem and relations are probabilistic. The objective of the data model is to aggregate enough records to discover and attribute natural keys with some level of confidence. A common class of data models with this property are entity resolution data models.
> All information in the database is represented explicitly and in exactly one way
Some data models have famously dual natures. Cartographic data models, for example, must be represented both as a graph models (for routing and reachability relationships) and as geometric models (for spatial relationships). The “one true representation” has been a perennial argument in mapping for my entire life and both sides are demonstrably correct.
> Every base relation should be in its highest normal form (3, 5 or 6th normal form).
This is one of those things that sounds attractive because it ignores that it requires no ambiguities about domain boundaries or semantics, which doesn’t exist in practice. I bought into this idea too when I was a young and naive data modeler. Trying to tamp out these ambiguities adds an unbounded number of data model epicycles that add a lot of complexity and performance loss. At some point, strict normalization is not worth the cost in several aspects.
In almost all cases, it is far more important that the data model be efficient to work with than it be the abstract platonic ideal of a domain model. All of these principles have to work on real hardware in real operational environments with all of the messy limitations that implies.
Also, first and last names depend on the culture. Oh and people can have more than 1 name (as in distinct names, rather than multi part names. Some cultures use different names with different social circles).
Easier to just let them put their preferred name into a freeform text field.
More arguments for synthetic over natural keys: https://blog.ploeh.dk/2024/06/03/youll-regret-using-natural-...
Natural keys are important. But the real world and the databases that represent them are messy. People’s identities get stolen. Data entry mistakes and integration between systems fail and leave the data in a schizophrenic state.
In my experience I find arguments about natural keys unproductive. I usually try to steer the conversation to the scenarios I mentioned above. Those who listen to me will have a combination of synthetic and natural keys. The first is used to represent system state. The second is used to represent business processes.
BTW: email+password should be separated too. An early draft of GDPR specifically mentioned that, though the final version got less into the weeds.
I’m sure if you vibe code any of this, it will all be plaintext, lol.
However that still runs into problems of nondurability of the key in cultures that delay naming for a few years. To name one problem.
So yeah, use a big enough integer as your key, and have appropriate checks on groups of attributes like this.
However, if you are only interested in citizens, then a "natural" key is the citizen id issued by the government department responsible for doing that. (Citizen is a role played by a natural person so probably doesn't have too many attributes.) I still wouldn't use that as a primary key on the citizen table, though.
She doesn’t have a birth certificate.
She was born in a country that was enduring several years of brutal war.
I know another person whose national ID was changed. Systems that use national ID as primary key failed to accept this change.
All it would take for a Bobby Tables[0] moment is a mother to have twins delivered by caesarean and given the same name... The same name thing has already been done[1].
[0] https://xkcd.com/327/
[1] https://www.walesonline.co.uk/news/real-life/mum-gives-twin-...
Instead, follow this principle: never ever use an externally assigned identifier as a primary key in your database.
Instead: link tables with simple integers. Do not use these integers for any other purpose and assume unordered. Never let these integers leave the app. When exchanging information between systems or third parties generate a suitable external identifier with a alphabetical prefix.
Devil captured Physicist, Engineer and Mathematician. He gave each of them big can of spam and locked them in the empty room saying „you will be here for 2 weeks - open the can and survive or die to starvation”. After 2 weeks Devil opens Physicist cell. It’s covered floor to the ceiling in complex scribbles. One piece of wall is clean of etching but small dent is visible. Can of span is opened and eaten clean, Physicist sits in corner visibly annoyed. Next one is Engineer. Cell walls are covered in multiple dents and pieces of spam. Engineer is bruised almost as much as the can, but it is ultimately opened and engineer is alive.
Finally the Devil opens Mathematician cell and find him dead. Only „given the cylinder” is etched on the wall.
—-
Puent isn’t about engineering but it always helped me to set limits between software engineering and computer science.
Natural keys sometimes need to change for unforeseen reasons, such as identity theft, and this is really tricky to manage if those keys are cascaded into many tables as foreign keys.
Natural keys are often not unique either. Using the national ID example, there are millions of duplicate SSNs issued within USA. https://www.computerworld.com/article/1687803/not-so-unique....
So, don't use natural keys as primary keys. Put them in as surrogate keys, ideally with a unique constraint.
If I remember my database lessons correctly there is no strictly highest normal form. It progresses from 1NF to BCNF, but above that it is more choosing different trade-offs.
Even below it is always a trade-off with performance and that is why we most of the time aim for 3NF, and sometimes BCNF.
The national ID example is funny. Let me give you a dose of reality concerning national IDs.
- I've seen cases with my country's national ID numbers containing duplicates due to human error.
- National ID's for people can change. In my country, children being adopted, get a different ID after adoption.
- There exist people without a national ID. Or people that don't want you to know their national ID.
National id is not something issued at birth in the country I live in. It’s something applied for at a certain age.
Never trust something outside your system to be stable.
Why does it matter? I have seen that many developers rely totally only on the code to manage entities on the database, instead of relying on prepared statements and pure SQL queries. This obviously opens a door for poor optimisation, since these Entity Management libraries don't support certain SQL capabilities.
That said, I’m not a fan of natural keys as primary keys. Especially composite keys. This just takes everybody back to the 80s/early 90s.
It only makes sense when there’s a huge storage benefit
If I remember my database lessons correctly there is no strictly highest normal form. It progresses from 1NF to BCNF, but above that it is more choosing different trade-offs.
Even below it is always a trade-off with performance and that is why we most of the time aim for 3NF, and sometimes BCNF.
No it shouldnt.
Fully normalised structures are slow, dangerous and place expensive and frustrating burdens on upstream users reducing the amount of value that can be extracted from the data.
I've been working around data for 20+ years and not once seen a dataset where tables could be blindly joined without some conditions. In these situations it can be better to just pre-join them so the business logic is captured.
You want to be pragmatic and have a healthy and constructive mix.
They had the same birth date, school, parents, phone number, street address, first name, last name, school, teachers, everything...
The story was that their dad was John Smith Sr in a long line of John Smiths going back a dozen generations. It was "a thing" for the family line, and there was no way he was going to break centuries of tradition just because he happened to have twins.
Note: In very junior grades the kids aren't expected to memorise and use a student ID because they haven't (officially) learned to read and write yet! (I didn't use one until University.)
I find it very difficult to believe this is not prevented by some law.
That's not true for SSNs. It's not true in that it is false. My statement that it is false is, in point of fact, true, and therefore not up for debate.
The government even acknowledges this:
https://www.ssa.gov/OP_Home/handbook/handbook.14/handbook-14...
> 1401.7 Can a person have more than one SSN?
> Most persons have only one SSN. In certain limited situations, SSA can assign you a new number. If you receive a new SSN, you should use the new number. However, your old and new number will remain linked in our records to ensure that your earnings are credited properly. This could affect your benefits.
Maybe there are countries where it is the case that nobody ever gets multiple National IDs. Maybe there are countries without fraud and where everyone can and will update their records when the government does. Maybe there is a veritable Utopia on Earth, a Cockaigne of validated data and reasonable deadlines.
"tell the truth that is out there"
Both truth and representation are very slippery, many-faceted concepts, encumbered with millennia of use and philosophy. Using them in this way is deceptive to the junior and useless to the senior.