Re: Lookup tables

> On 6 Feb 2025, at 22:03, Peter J. Holzer <hjp-pgsql(at)hjp(dot)at> wrote:
>
> On 2025-02-04 22:41:38 +0100, Thiemo Kellner wrote:
>>
>> 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.
>
> I fail to see why use of a surrogate key is the problem here.
>
> Either changing the color from GREE to YELLOW makes sense or it doesn't.
>
> If it doesn't make sense, then it's release which is faulty, not the
> model.
>
> if it does make sense (I'm a bit at a loss when that might be the case,
> maybe the "color" is just a code word, or maybe they are colors in a
> design which are arbitrary but must be consistent), then the experience
> that the user has is exactly the same as if the maintenance release was
> applied just after they selected the color. Which might be a bit
> confusing but is almost certainly what is wanted.
>

This is simply saying “what is implemented is certainly wanted, so what’s the point”.

The discussion is about *defining* what is wanted and using DBMS to *enforce* that.

>
>> A) Your release changed the sementics of the record 3. It's meaning
>> changed. I cannot recommend doing that.
>
> If the release changed the semantics of an existing record the release
> was almost certainly wrong.

Is it possible to minimize the risk of “wrong releases” using mechanisms that DBMS provides?

>
>> B) If you absolutely must change the semantic, put your application
>> into maintenance mode in which noone can select anything beforehand.
>>
>> 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.
>
> Yeah, that's a good example where changing the color from GREE to YELLOW
> doesn't make sense. Presumably that ID 3 is used as a foreign key in
> lots of places, e,g. in an inventory table. Your bikes in stock won't
> just magically change color just because you changed some text in the
> database. So that change simply doesn't make sense and shouldn't be done
> as part of a maintenance release. Confusing a few people who just happen
> to open the dropdown in the wrong second is the least of your problems.

We are in agreement here. What I am also saying is:
having color name as PK and all FKs referencing it *prevents* these unwanted updates.

—
Michal