Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tgJd8-009X9H-Dr for pgsql-general@arkaria.postgresql.org; Fri, 07 Feb 2025 08:22:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tgJd7-00Gyxg-H2 for pgsql-general@arkaria.postgresql.org; Fri, 07 Feb 2025 08:22:29 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tgJd7-00GyxV-2U for pgsql-general@lists.postgresql.org; Fri, 07 Feb 2025 08:22:29 +0000 Received: from mail-ed1-x530.google.com ([2a00:1450:4864:20::530]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tgJd4-004KkK-1D for pgsql-general@lists.postgresql.org; Fri, 07 Feb 2025 08:22:28 +0000 Received: by mail-ed1-x530.google.com with SMTP id 4fb4d7f45d1cf-5dced61e5a3so3222960a12.0 for ; Fri, 07 Feb 2025 00:22:26 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=kleczek.org; s=google; t=1738916546; x=1739521346; darn=lists.postgresql.org; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:from:to:cc:subject:date:message-id:reply-to; bh=FTFMD8t1pLkNZJFlzWtQTS9uCzO5RprFqDKXOjxMB5k=; b=OI3pfB7KREjBCR6+51/+XWWiiDyn77urRSWr9HftdjvRvh8ldxv+X2ad5vwT2TErdH uNdrbwPUtXEo2UNeJb9VN2fywQnV97nWm0wWOo8g2s6oyPmEUkrHjG9KbYZhX3fIjoPU tAE6v2OaizsEoN3qJ5oIQ5kxKI9dgr7fPk2vw= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1738916546; x=1739521346; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=FTFMD8t1pLkNZJFlzWtQTS9uCzO5RprFqDKXOjxMB5k=; b=Ap9FEY7lMNDDGaKpd4oxw9I+ZptUOrpKt1MIGgpnjJ0pvnE2OgYTCKipyUHicGaqRx 4mk9eFWHgiuvAJSRZaj6wsZslROEj4v6UlI+ejBUKUgd+wQWUujCTRvUmE78YAwzqhQP 67D2RT+RcWcFf0Y2PeG15LVGyDSg+HCSf5AlavYqXSJ+Fon7sAL1GfdlvuiEpm7Y1dUT sXGGUnfyoD+F1tL9S7zDAOgdr9NAxkQJg8TAqHnHnDqTKhbadeWuYZM6qWMPDLCH6o0o 9QmixHAZI2QWH8bh8FINGtjfwl+Nvq+E5uxNtR02qnVd32EJkluydLsqPhpgX4/Ri6sh ZGXQ== X-Gm-Message-State: AOJu0Yx8SoAm5Za+5Ixwb5Tn1ZJOGdTVK73k2GGtH1/VGg757QNmlB2D FvUtkLvoA3gR5HcVcfSC5OG41qWLByZ0bPrXNuBqV6gcF7ooSXx5kLIgCfBhtY3ofduf2jmzTDm O3no= X-Gm-Gg: ASbGncvp06mvepZg/zi58z1JwiAH/HEhovN5IfA8hs3qq5quP0xXz81RI4vaPm7eZ8Z 6bNLjc9s0xVQxVjCu+7yi0u7+yFdjEXPw4S5tL0PbdmcG5s4991m28P7VIxGvViAUSO27dfnd1A onbgg4hG2xUZnYQbJmqT02PRxhnc9Stnu+opN7KtVSUPSLJ9hIDARCTxKYYzP3TB4iokbFr1rO5 tNsVF0OEpFZQE5RKvvLpfGISNVWoo7rjYRkKjAHSz0fxwLVYWR4YcZqv7uKQtSWti/L/SS8VMW+ pe3ERv2H9G+l0OwYCN8F8iYV8Zt2WAllTwegnas/mh0= X-Google-Smtp-Source: AGHT+IG+2Laljq4AQsKbD6uSgSrMwPT3O2cMvI0WSMpNxvR8lwaq2pNR7+H5ixacCkqV8JfcAnfv8A== X-Received: by 2002:a17:906:6a93:b0:aa6:a844:8791 with SMTP id a640c23a62f3a-ab789cbe584mr178485066b.45.1738916545193; Fri, 07 Feb 2025 00:22:25 -0800 (PST) Received: from smtpclient.apple (b15.kleczek.org. [185.20.172.182]) by smtp.gmail.com with ESMTPSA id a640c23a62f3a-ab77dcb9336sm171163866b.27.2025.02.07.00.22.24 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Fri, 07 Feb 2025 00:22:24 -0800 (PST) From: =?utf-8?Q?Micha=C5=82_K=C5=82eczek?= Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_304B57CB-BC71-47CC-B3B0-94D4F3CEE457" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.300.87.4.3\)) Subject: Re: Lookup tables Date: Fri, 7 Feb 2025 09:22:13 +0100 In-Reply-To: <20250206210318.kj2j5dvliidpmesy@hjp.at> Cc: pgsql-general@lists.postgresql.org To: "Peter J. Holzer" References: <628D4022-5365-4D00-810B-10349A90874A@kleczek.org> <64603f36-b6d5-469b-8d6d-30376842241b@gelassene-pferde.biz> <20250206210318.kj2j5dvliidpmesy@hjp.at> X-Mailer: Apple Mail (2.3826.300.87.4.3) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_304B57CB-BC71-47CC-B3B0-94D4F3CEE457 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On 6 Feb 2025, at 22:03, Peter J. Holzer wrote: >=20 > On 2025-02-04 22:41:38 +0100, Thiemo Kellner wrote: >>=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 > I fail to see why use of a surrogate key is the problem here. >=20 > Either changing the color from GREE to YELLOW makes sense or it = doesn't. >=20 > If it doesn't make sense, then it's release which is faulty, not the > model. >=20 > 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. >=20 This is simply saying =E2=80=9Cwhat is implemented is certainly wanted, = so what=E2=80=99s the point=E2=80=9D. The discussion is about *defining* what is wanted and using DBMS to = *enforce* that. >=20 >> A) Your release changed the sementics of the record 3. It's meaning >> changed. I cannot recommend doing that. >=20 > If the release changed the semantics of an existing record the release > was almost certainly wrong. Is it possible to minimize the risk of =E2=80=9Cwrong releases=E2=80=9D = using mechanisms that DBMS provides? >=20 >> B) If you absolutely must change the semantic, put your application >> into maintenance mode in which noone can select anything beforehand. >>=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. >=20 > 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. =E2=80=94 Michal= --Apple-Mail=_304B57CB-BC71-47CC-B3B0-94D4F3CEE457 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

On 6 Feb 2025, at 22:03, Peter J. Holzer = <hjp-pgsql@hjp.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 = =E2=80=9Cwhat is implemented is certainly wanted, so what=E2=80=99s the = point=E2=80=9D.

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 =E2=80=9Cwrong releases=E2=80=9D 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.

=E2=80=94
Michal
= --Apple-Mail=_304B57CB-BC71-47CC-B3B0-94D4F3CEE457--