News for hackers. Stuff that matters.
Re:The problem with invariants is that they change over time by abraae on Tuesday April 23, 2024 @12:58AM (#40128660)
> For example, I'm a big fan of surrogate keys and UUIDs, in database design.

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.

Re:The problem with invariants is that they change over time by ThereIsNoWorry on Tuesday April 23, 2024 @02:12AM (#40129014)
You wouldn't believe how often I have to fight for UUIDs instead of sequencing. UUIDs are great. For all practical purposes 0 possibility of collision; you can use it as an ID in a global system, it just makes so much fucking sense.

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.

Re:The problem with invariants is that they change over time by akira2501 on Tuesday April 23, 2024 @04:14AM (#40129678)
I've been using UUIDv7 a lot lately and I've been quite happy with the results.
Re:The problem with invariants is that they change over time by abraae on Tuesday April 23, 2024 @02:24AM (#40129087)
As far as I know the only practical downside with UUIDs in the modern age - unless you like using keys for ordering by creation time, or you have such enormous volumes that storage is a consideration - is that they are cumbersome for humans to read and compare, e.g scanning log files.

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.

Re:The problem with invariants is that they change over time by mrkeen on Tuesday April 23, 2024 @02:34AM (#40129132)
> they are cumbersome for humans to read and compare, e.g scanning log files.

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.

Re:The problem with invariants is that they change over time by prometheus76 on Tuesday April 23, 2024 @08:55AM (#40131422)
We were pulling the entire list of tasks (around 80,000 tasks from various projects) from Microsoft Project 2012 (MSP) and dumping it into a database every day, so that we could track progress of projects daily. MSP uses GUIDs and they are great for uniqueness, but the memory footprint they caused when reporting using Power BI became untenable after about six months.

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.

Re:The problem with invariants is that they change over time by CodesInChaos on Tuesday April 23, 2024 @12:13PM (#40133638)
> Our memory footprint dropped by 10X

How? A UUID is only 4x the size of a 32-bit integer.

Re:The problem with invariants is that they change over time by prometheus76 on Tuesday April 23, 2024 @01:03PM (#40134240)
We had multiple columns using UUID that we needed, and the Tabular memory model goes up exponentially for every column.
Re:The problem with invariants is that they change over time by marcosdumay on Tuesday April 23, 2024 @10:05AM (#40132149)
I never found any kind of literature that clearly states that artificial keys are superior identifiers than natural ones.

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.

Re:The problem with invariants is that they change over time by CodesInChaos on Tuesday April 23, 2024 @12:21PM (#40133739)
I almost always use surrogate keys as primary keys (or a tuple of them, for join-tables). Natural keys are rarely as immutable and unique as we'd like.

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.

Re:The problem with invariants is that they change over time by bruce511 on Tuesday April 23, 2024 @03:17PM (#40136011)
It's less about literature, and more about experience.

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.

Re:The problem with invariants is that they change over time by abraae on Tuesday April 23, 2024 @04:52PM (#40137115)
It's an easy challenge. Just ask someone to name a natural key that will never change. They are as rare as rocking horse shit.