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.
Well, I use the same self hosted Email address since the late 80‘s
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).
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.
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.
Ignoring secondary email addresses, what was my primary [onlineaccount] E-Mail address has changed four times.
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).
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.