99
You'll regret using natural keys
(blog.ploeh.dk)
you are viewing a single comment's thread
view the rest of the comments
view the rest of the comments
this post was submitted on 20 Jun 2024
99 points (94.6% liked)
Programming
17314 readers
78 users here now
Welcome to the main community in programming.dev! Feel free to post anything relating to programming here!
Cross posting is strongly encouraged in the instance. If you feel your post or another person's post makes sense in another community cross post into it.
Hope you enjoy the instance!
Rules
Rules
- Follow the programming.dev instance rules
- Keep content related to programming in some way
- If you're posting long videos try to add in some form of tldr for those who don't want to watch videos
Wormhole
Follow the wormhole through a path of communities !webdev@programming.dev
founded 2 years ago
MODERATORS
Got hands on experience with this. Wasn't my design choice but I inherited an app with a database where one of the keys was tied to a completely separate database. I mean at the time it probably made sense but the most unlikely of scenarios actually happened: that other database, the one I had zero control over, was migrated to a new platform. All of those keys were synthetic so of course they were like, "Meh, why we gotta keep the old keys?" So post-migration my app becomes basically useless and I spent 6 hours writing migration code, some of it on off hours, to fix my data.
So it's questionable whether a foreign key of a completely different system is a natural key, but at the very least never use a key YOU don't control.
I don't think that holds true in all scenarios. You need to use a key that has some guarantees. In many systems you will use data you don't control, like email addresses, IBANs, ISBNs, passport IDs and many more. You have zero control over those keys, but because each comes with certain guarantees, they might be suitable as a foreign key in your context.
People regularly change email addresses. Listing that as an example is a particularly bad example in my opinion.
@Kissaki @state_electrician Well, I use the same self hosted Email address since the late 80‘s. I do not consider that changing it regularly.
Personal anecdotes are rarely pose a valid argument (unless you are designing a database specifically for users who use the same email address since the late 80's).
Ignoring secondary email addresses, what was my primary [onlineaccount] E-Mail address has changed four times.
Even in this scenario it's feasible for standards to change. ISBN-15 becomes a thing and suddenly you have books that never get an ISBN-13 so your primary key constraints cause an error for trying to insert a null. Granted, you can see a lot of these changes coming but again, they come on a schedule you don't control.
These things can just be unique fields. I think the takeaway here is exactly to not use these unique fields as database keys if you have the option / if it's up to you.