public inbox for [email protected]  

help / color / mirror / Atom feed

From: Thiemo Kellner <[email protected]>
To: pgsql-general <[email protected]>
Subject: Re: Lookup tables
Date: Wed, 5 Feb 2025 23:36:10 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>


El 05-02-25 a las 22:19, Michał Kłeczek escribió:
> But you can guarantee that if you change the value of the key after the user displays it - the user will get an error on submission (whereas with the surrogate key it would happily proceed without user noticing).
As you very rightly say happily proceed, because that is, what you 
actually want. The user chose GREE meaning green, which is updated in 
the meantime to GREEN. All good. Your solution throws an error for nothing.
> How so? The user is going to get FK violation - you do not need any downtime to make sure users don’t submit wrong values.
Sorry, I was not aware, your aim is to bother the customer with FK 
violation messages, if you can avoid it.
>> Ensure, the update goes from GREE to GREEN? You cannot, simple as that. You just can minimize the risk by testing, testing, testing.
> You can also simply disallow updates with FK constraint eliminating risk.
This is nothing that is specific to surrogate or natural keys. If one 
disallow updates, one has to live with typos and everything.
>> But that holds equally true for the business key of a surrogate key table as natural key table. That's why the surrogate key is such an elegant construct. You can change business key of the record with id 3 from GREE to GREEN, VERT, GRÜN, VERDE or ASéLDHK()*NSLDFHP)(*Z . It keeps its meaning of the perception of the human eye of electromagnetic waves of the wavelength roughly between 495-570 nm (according to Wikipedia).
> And why do you think unconstrained updating of business key is a good thing?
I am not sure what is your take on "unconstrained". As already 
mentioned, testing is the only constraint you can set up. And I am 
neither sure where your problem is with updating unless you have an 
update orgy because your ref-constraint goes on natural keys.
> You must implement rules governing what can and what cannot be changed *somewhere* - not doing it in the database means you have to do it in applications.
That sounds adventurous. Maybe I am not seeing what you mean, but is 
sound to me that you build a shadow database where you map your GREE to 
GREEN in the application layer so the application can display the 
correct value in the GUI, but no one has to update the core database. 
And if there is a typo in the shadow data base you build another mapping 
database on top of it?
> Anyway - let’s agree to disagree :)
Agreed :-)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Lookup tables
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox