Again, every time I have seen natural keys in a big long lived system I have seen duplication. This is something you will have to deal with.
You'll regret using natural keys
161–170 of 568 posts
Re: You'll regret using natural keys
#162One of the most interesting part to me is the fact he is a university professor in a good country but rides a used/second hand car... in an era where status and success are highlighted by displaying what one buys and owns on social media.
> in an era where status and success are highlighted by displaying what one buys and owns on social media
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.
Re: You'll regret using natural keys
#163Another example that happens surprisingly often in healthcare. A registration clerk will incorrectly enter a personal health number (PHN) into the system. Then the actuall holder of that PHN shows up. If this were the PK then the system just wouldn’t handle this case and the reg clerk would have a huge mess to sort out on the spot. A surrogate key on the Person table allows this registration to be made where 2 people…
Finding and eliminating duplicates is a very common software problem that is rarely solved in a reusable, user-friendly way that preserves history while eliminating redundant data. In fact, in 40 years of working with computers I can't think of a single UI that I'd want to emulate.
Ultimately the key is the business-modeling that captures "duplicates" as Things That Happen.
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.
Re: You'll regret using natural keys
#164As with everything, natural keys are sometimes useful or even good. Where business rules fit, I like using them. But you have to know that the value will never change or be reused.
Re: You'll regret using natural keys
#165You think your surrogate key will save you? It will not. The world has an external reality that needs to be reflected in your database. If the unique identifier for your object — VIN, CUSIP, whatever — if it changes, the world will henceforth refer to it by both. You will need to track both. Adding a synthetic key only means you have to track all three. Plus you have to generate a meaningless number, which is actuall…
> Model your data on the real world. Do not depend on spherical horses.
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.
Re: You'll regret using natural keys
#166> I already predicted that changing the number would prove to be either impossible, or have all sorts of cascading effects, ultimately terminating in official records no longer recognizing that the car is mine. 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 is…
> Mailing a check to transfer money
Real 1800 vibes here.
Re: You'll regret using natural keys
#167You can get performance benefits from using natural keys, as many databases store rows in the order of a table's primary key (sometimes called the clustered index, though it may or may not have a unique constraint requirement depending on the DBMS and a few other bits).
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.
Re: You'll regret using natural keys
#168Natural keys have a tendency to change over time in what is considered unique. 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 tab…
Ah, how I remember changing my name. People think it only happens now and only to 0.1% of people who decide to change their gender in a leftist Western society, but somehow they forget that people have been changing family names for centuries.
And of course, my work email address also changed. Now THAT is fun.
Re: You'll regret using natural keys
#169I've become a fan of unique, relatively short and "human-readable" IDs, such at the ones used by Stripe, e.g. `cus_MJA953cFzEuO1z` for an ID of a customer. Here's a Stripe dev article on the topic: https://dev.to/stripe/designing-apis-for-humans-object-ids-3... If you use JavaScript/TypeScript, you can make them like this: function makeSlug(length: number): string { const validChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcde…
Please don't use % to generate integers from a range, it's not uniform, which can be disastrous if you rely on your numbers not being predictable. You can use crypto.randomInt instead.
Re: You'll regret using natural keys
#170Another massive annoyance with natural keys - privacy. If your table's primary key contains personal information, that PII now infects every other table that holds a foreign key to that table.
AFAIK, even using a UUID is still considered PII if it uniquely identifies the user.