> On 4 Feb 2025, at 22:41, Thiemo Kellner <thi...@gelassene-pferde.biz> wrote:
>
> 04.02.2025 18:31:09 Michał Kłeczek <mic...@kleczek.org>:
>
>>
>>> On 4 Feb 2025, at 18:27, Thiemo Kellner <thi...@gelassene-pferde.biz> 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
