public inbox for [email protected]  

help / color / mirror / Atom feed

From: Michał Kłeczek <[email protected]>
To: Thiemo Kellner <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Lookup tables
Date: Wed, 5 Feb 2025 13:55:51 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>



> On 4 Feb 2025, at 22:41, Thiemo Kellner <[email protected]> wrote:
> 
> 04.02.2025 18:31:09 Michał Kłeczek <[email protected]>:
> 
>> 
>>> On 4 Feb 2025, at 18:27, Thiemo Kellner <[email protected]> wrote:
>>> 
>>>  Unless the lookup table is actually a check constraint one can use to populate dropdown boxes in an interface.
>> 
>> That is even worse because it ceases being transactional and users might select something different than what they see on the screen.
> 
> I might see what you want to point out. E.g. the table is COLOURS. The rec with id 1 is RED, the one with id 2 is BLUE, 3 is GREE and so on. Now you load these values into the dropdown box that sports RED, BLUE, GREE and so on. While someone selects GREE, there is a maintenance release changing GREE to YELLOW. So when that someone sends the selection by id to the backend, not GREE is selected but YELLOW.
> 
> A) Your release changed the sementics of the record 3. It's meaning changed. I cannot recommend doing that.

That’s what using natural keys and FK’s restricting their changes guarantee: no (accidental) changes to meaning of data.
Even with cascading updates you still have transactional semantics (ie. the user selects what’s on the screen or gets an error).

> B) If you absolutely must change the semantic, put your application into maintenance mode in which noone can select anything beforehand.

All this error prone hassle and downtime can be avoided with natural keys and guarantees that DBMS gives you.

> 
> If the maintenance would just correct the typo from GREE to GREEN, nothing would happen. Yor customer still ordered the lavishly green E-Bike her hear ever desired.

The question is: how do you _ensure_ that?

—
Michal

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], [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