In principle, I agree with the statements made in this article.
Natural keys (social security numbers/BSN/CPR numbers for people, chassis numbers for cars, names+locations for restaurants etc.) sound like perfect candidates for database keys. But have one person change their name/gender, or a car getting a new chassis number because of a data entry mistake, or a restaurant changing name, and everything falls apart. Not to mention the uniqueness problem - can we always be able to prove that a key always identifies one and only one record, and the other way around?
That's why synthetic keys come to the rescue.
But, as a seasoned developer who has spent two decades working with databases, I've seen too many things go wrong with synthetic keys too.
Auto-increment integers and random UUIDs are great only as long as you are in a single-threaded environment where you don't have the risk of duplicate records concurrently processed by separate services/processes/threads, where the database is small enough that you don't have to worry about sharding by key, and where performance constraints are lax enough that you don't care of the cost of doing an insert-or-update-and-select in order to retrieve the ID of the record you've just inserted.
In my experience, the best of the two worlds can be achieved when you have deterministic synthetic keys generated on an immutable subset of the attributes.
If you can prove that a subset of columns is really unique and immutable, there's nothing wrong with generating a UUID/hash out of them, and using that as the primary key. That solves the insert-and-fetch and the consistent upsert problems basically for free. It's an approach that I've been following for the past few years in my jobs, and that allowed me to write software that can work on huge shard-friendly databases with nearly no data pollution and high read+write efficiency.
If anything actually changes on those columns, it's ok to create a new record. Just have a procedure in place to explicitly deal with those cases if needed, so anything wired to the old record can be rewired to the new one. But, in my experience, cases where you have to modify what's supposed to be an immutable set of attributes are a (very uncommon) exception, not the norm.
Your database should be optimized for the norm, not for the exception.
https://blog.ploeh.dk/2024/06/03/youll-regret-using-natural-keys/
Fabio Manganiello /
npub1s9…jdnmu
2024-07-11 10:17:40
Author Public Key
npub1s9uc08n58mxqk5umvapqulwzng0sja635q86r36d8n4rr9r9ygaskjdnmuPublished at
2024-07-11 10:17:40Event JSON
{
"id": "057e2fd4a9454f33a0a030061c2e4eac40751985beaad793943bb492e1ca60c8",
"pubkey": "8179879e743ecc0b539b67420e7dc29a1f097751a00fa1c74d3cea319465223b",
"created_at": 1720693060,
"kind": 1,
"tags": [
[
"proxy",
"https://manganiello.social/objects/0a211a82-7188-40c7-9611-11124b19b865",
"activitypub"
],
[
"L",
"pink.momostr"
],
[
"l",
"pink.momostr.activitypub:https://manganiello.social/objects/0a211a82-7188-40c7-9611-11124b19b865",
"pink.momostr"
],
[
"-"
]
],
"content": "In principle, I agree with the statements made in this article.\n\nNatural keys (social security numbers/BSN/CPR numbers for people, chassis numbers for cars, names+locations for restaurants etc.) sound like perfect candidates for database keys. But have one person change their name/gender, or a car getting a new chassis number because of a data entry mistake, or a restaurant changing name, and everything falls apart. Not to mention the uniqueness problem - can we always be able to prove that a key always identifies one and only one record, and the other way around?\n\nThat's why synthetic keys come to the rescue.\n\nBut, as a seasoned developer who has spent two decades working with databases, I've seen too many things go wrong with synthetic keys too.\n\nAuto-increment integers and random UUIDs are great only as long as you are in a single-threaded environment where you don't have the risk of duplicate records concurrently processed by separate services/processes/threads, where the database is small enough that you don't have to worry about sharding by key, and where performance constraints are lax enough that you don't care of the cost of doing an insert-or-update-and-select in order to retrieve the ID of the record you've just inserted.\n\nIn my experience, the best of the two worlds can be achieved when you have deterministic synthetic keys generated on an immutable subset of the attributes.\n\nIf you can prove that a subset of columns is really unique and immutable, there's nothing wrong with generating a UUID/hash out of them, and using that as the primary key. That solves the insert-and-fetch and the consistent upsert problems basically for free. It's an approach that I've been following for the past few years in my jobs, and that allowed me to write software that can work on huge shard-friendly databases with nearly no data pollution and high read+write efficiency.\n\nIf anything actually changes on those columns, it's ok to create a new record. Just have a procedure in place to explicitly deal with those cases if needed, so anything wired to the old record can be rewired to the new one. But, in my experience, cases where you have to modify what's supposed to be an immutable set of attributes are a (very uncommon) exception, not the norm.\n\nYour database should be optimized for the norm, not for the exception.\n\nhttps://blog.ploeh.dk/2024/06/03/youll-regret-using-natural-keys/",
"sig": "f645d6464130ab9a0cb3ab2b1ac338609859f7db70656de5d18fa7aadf0d04c0823e8376f57c21001fdbaab55e8b70eda5d278a35d450a671367a0cec37e1fce"
}