You would think this (using surrogate keys) would be such well worn wisdom by now that discussions about it wouldn't be a thing. But somehow new generations of developers, weaned on nosql and on not using the god-given gifts of databases like integrity constraints, seem to love bike shedding key design and arguing vociferously that natural keys can be used as primary (and foreign) keys.
But the default is still a number natural number sequence. As if it matters for 99% of all cases that stuff is "ordered" and "easily identifiable" by a natural number.
But then you want to merge something or make double use and suddenly you have a huge problem that it isn't unique anymore and you need more information to identify.
Guess what, an UUID does that job for you, across multiple databases and distributed systems, a UUID is still unique with 99.9999% probability.
The one counter-example every 10 years can be cared for manually.
And in any case the German tank problem means that you often can't use incrementing numbers as surrogate keys if they are ever exposed to the public e.g in urls.
I think the positive still outweighs the negative here: you can search your whole company's logs for a UUID and you won't get false positives like you would with serial integers.
We ended up creating our own integer table tied to the GUIDs and replaced all the GUIDs with integers in our reporting database. Our memory footprint dropped by 10X and we were able to report on years worth of data, rather than months.
How? A UUID is only 4x the size of a 32-bit integer.
Every material about relational data I've seen either just explains them, says they can be either natural or artificial, and leaves at that, or says there's some disagreement over what is better.
The unanimity exists only within field experts that apply the stuff. The theoreticians all disagree.
Even ISO country codes can be problematic, for example we currently use XK for Kosovo, but it might get an official code at some point.
Over the years just about every natural key I've seen ends up being either not-universal (-everyone- has a phone number right? Even that new-born baby?) Or not unique (phone numbers can be shared) or mutable (like people's names changing).
It's just so much easier to add a column and use a surrogate key. I prefer uuids because they are unique And immutable - properties I really appreciate in primary keys.
This is in contrast to my how my younger-self would instead focus on making code that "extensible" or "flexible" or "configurable". (With some overtones of impressing people, leaving a long-term mark upon the project, etc.)
Nope! Go for "delete-able."
If you design thinking "X years from now business-requirements or staff will have changed enough that this will need to be ripped out and rewritten, and I am not good enough at predicting the future to solve it cleverly", then it sets up a different expectation of how you invest your time.
One might focus on code where it's easy to trace dependencies, even if that means a bit more boilerplate and avoiding layers of indirection that aren't (yet/ever) needed. "Greppability" of code also becomes important, especially if your language/tools aren't very good at tracing usages and caller/callee relationships.
Often the problem is exactly this magic "decoupling framework", which ends up being exactly the problematic part that that one eventually wants to swap out.
Long ago somebody made a Thingy.find(...) method where you can pass all sorts of optional arguments to filter which Thingy comes back, like Thingy.find(primary_key=1) or Thingy.find(owner=123, active=true) etc. That complexity made it flexible for people writing caller-code, because they wouldn't need to alter the method to satisfy their future arbitrary needs, they can just pass whatever key-values they want to match.
However now some original invariants have changed, and now I need to go find all the places it's being used ("flexibly") and reconstruct the caller's intent, because in some scenarios the correct return value is going to be a list of multiple results instead of 0-or-1.
In contrast, the task would be easier if there was a named method that captured the intent for the 3-4 distinct use-cases.
Along with its complimentary statement:
Beware of "never", especially in regards to client requirements.
Been on too many projects where it turned out "never" meant once or twice per year once we'd gone into production...
Thus as you say we can very often immediately figure out when something is missing from the requirements, or when we feel things might be changing soon down the line.
We can also detect when the customer is requesting something suboptimal or similar. We've received a lot of positive feedback from customers due to us devs pushing back, helping them to improve their processes and workflow. But it also leads to less work for us, or more generic code which can be reused by more customers.
Our support team also has extensive domain knowledge, many have been hired from our customers. It makes our support excellent, often the customer's issue is a combination of how to do X and how to do X in our program, and for new devs this is a great resource to tap into.
Together we're punching well above our weight, dominating our niche with a quite small team. And domain knowledge has helped a lot in that.
Why I fully support surrogate keys and versioning, I do not support UUIDs as primary keys - they are trivial to generate, not so much to place in B-trees. It's very common to need/process recently added data together, UUID make that much harder as they inherent placement is all over the place w/o any locality.
UUIDs are ok/fine as external references, of course.
Not sure about this one. I’ve seen teams that are too noncommittal in their architecture decisions because “what if [insert unlikely thing that’d not actually be the end of the world] changes?” Then the project ends up only using surface level features of their chosen tooling because people want to keep too many options open and are afraid to commit to something where it matters.
Generally speaking there are lots of well-established best-practices, that are best in most contexts, but which fall over in other contexts. Knowing what to do where is part of the intangibleness that separates the great from the good from the competent.
So some things are good to defer. -if- this product works, we -may- need to scale, so let's choose a database, and design, that allows for multiple servers. Picking say Postgres over SQLite. Other times saying "this is just a proof of concept, do it quick in SQLite".
Is our API gonna be XML or JSON? Not sure, could go either way. Let's design so that part can be trivially replaced.
With data design especially, IMO, it's worth -planning- for success. Starting with uuid will end up fine for 99% of the tables which will have "not many records". When we identify that 1%, which are growing by millions of records a minute, we can adjust for that table as desired.
[On a side note, regarding clustering, it seems some people aren't aware that the primary key and clustered key don't have to be the same.]
On the other hand, I have systems being worked on and still shipping 20 years old because of fortuitous decisions in the 90s. Most of those taken because of bad experiences with earlier systems.
For example, I'm a big fan of surrogate keys and UUIDs, in database design. Mostly because of experience where they weren't used which caused problems later. (By contrast, performance costs of using them become lower every year.)
Conversely deciding to build code libraries parallel to application development means long-term gains in performance and reliability.
It's easy to view all this in hindsight, -much- harder with foresight.
Today I try and see everything through 2 rules.
1) beware of "always" statements, especially even it votes to client requirements. IME "always" is used as a synonym for "mostly" and "at the moment".
2) choose paths now that keep options open later. Will this system ever use distributed data? Probably not, but choose a primary key that leaves that option open.
Lastly, and this applies to data design, remember "nothing is universal". Using Social Security number as an ID is great, until they outsource. Phone number is always 8 digits? Yeah right. Nobody works night shift... you get the idea.
If in doubt, err on the side of "variant". You'll be right more often than not.