Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.300.87.4.3\))
Subject: Re: Lookup tables
From: =?utf-8?Q?Micha=C5=82_K=C5=82eczek?= <michal@kleczek.org>
In-Reply-To: <64603f36-b6d5-469b-8d6d-30376842241b@gelassene-pferde.biz>
Date: Wed, 5 Feb 2025 13:55:51 +0100
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <3A08C6E4-8B03-4D24-BAA7-53860A66C1AF@kleczek.org>
References: <c0fe3bc0-efb2-4648-ac9a-435601acc2bf@gelassene-pferde.biz>
 <628D4022-5365-4D00-810B-10349A90874A@kleczek.org>
 <64603f36-b6d5-469b-8d6d-30376842241b@gelassene-pferde.biz>
To: Thiemo Kellner <thiemo@gelassene-pferde.biz>
Archived-At: 
 <https://www.postgresql.org/message-id/3A08C6E4-8B03-4D24-BAA7-53860A66C1AF%40kleczek.org>
Precedence: bulk


> On 4 Feb 2025, at 22:41, Thiemo Kellner <thiemo@gelassene-pferde.biz> =
wrote:
>=20
> 04.02.2025 18:31:09 Micha=C5=82 K=C5=82eczek <michal@kleczek.org>:
>=20
>>=20
>>> On 4 Feb 2025, at 18:27, Thiemo Kellner =
<thiemo@gelassene-pferde.biz> wrote:
>>>=20
>>> =EF=BB=BF Unless the lookup table is actually a check constraint one =
can use to populate dropdown boxes in an interface.
>>=20
>> That is even worse because it ceases being transactional and users =
might select something different than what they see on the screen.
>=20
> 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.
>=20
> A) Your release changed the sementics of the record 3. It's meaning =
changed. I cannot recommend doing that.

That=E2=80=99s what using natural keys and FK=E2=80=99s 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=E2=80=99s 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.

>=20
> 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?

=E2=80=94
Michal=