Date: Fri, 7 Feb 2025 19:42:08 +0100
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: Lookup tables
Message-ID: <20250207184208.y6htcxbhlravtrte@hjp.at>
Mail-Followup-To: pgsql-general@lists.postgresql.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>
 <20250206210318.kj2j5dvliidpmesy@hjp.at>
 <AC64A87D-D673-4D8F-B4F1-73516798BA5D@kleczek.org>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="iqn7eqyu362qmivx"
Content-Disposition: inline
In-Reply-To: <AC64A87D-D673-4D8F-B4F1-73516798BA5D@kleczek.org>
Archived-At: 
 <https://www.postgresql.org/message-id/20250207184208.y6htcxbhlravtrte%40hjp.at>
Precedence: bulk


--iqn7eqyu362qmivx
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2025-02-07 09:22:13 +0100, Micha=C5=82 K=C5=82eczek wrote:
>=20
>=20
>     On 6 Feb 2025, at 22:03, Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
>=20
>     On 2025-02-04 22:41:38 +0100, Thiemo Kellner wrote:
>=20
>=20
>         I might see what you want to point out. E.g. the table is COLOURS=
=2E 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
>=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
>=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 cas=
e,
>     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 experien=
ce
>     that the user has is exactly the same as if the maintenance release w=
as
>     applied just after they selected the color. Which might be a bit
>     confusing but is almost certainly what is wanted.
>=20
>=20
>=20
> This is simply saying =E2=80=9Cwhat is implemented is certainly wanted,

Not at all. It may or may not be what is wanted but we can't tell unless
we know what is wanted. We have a single table without any context. What
does this table represent? What is the identity here? If it's the color
itself, changing green to yellow doesn't make any sense ("it's the same
color, but now it's yellow instead of green" is self-contradictory). If
the identity has some application-defined semantics, it may make sense.

> so what=E2=80=99s the point=E2=80=9D.

Indeed. Without knowing the semantics discussing the data model or the
advisability of certain updates is indeed rather pointless.
Disagreements will be more likely to be due to unstated assumptions
about the application than because of general principles.


>         A) Your release changed the sementics of the record 3. It's meani=
ng
>         changed. I cannot recommend doing that.
>=20
>=20
>     If the release changed the semantics of an existing record the release
>     was almost certainly wrong.
>=20
>=20
> Is it possible to minimize the risk of =E2=80=9Cwrong releases=E2=80=9D u=
sing mechanisms that
> DBMS provides?

Maybe. But the person who thought that

    update colors set name =3D 'YELLOW' where id =3D 3; -- YELLOW is the ne=
w GREE

was a good idea might also think that=20

    update doodads set color =3D 'YELLOW' where color =3D 'GREE';

is a good idea.

Is it? I don't know. Maybe it is. Maybe it isn't. There is no way to
know unless we know the requirements.


>         If the maintenance would just correct the typo from GREE to GREEN,
>         nothing would happen. Yor customer still ordered the lavishly gre=
en
>         E-Bike her hear ever desired.
>=20
>=20
>     Yeah, that's a good example where changing the color from GREE to YEL=
LOW
>     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 d=
one
>     as part of a maintenance release. Confusing a few people who just hap=
pen
>     to open the dropdown in the wrong second is the least of your problem=
s.
>=20
>=20
> We are in agreement here. What I am also saying is:
> having color name as PK and all FKs referencing it *prevents* these unwan=
ted
> updates.

I'm not convinced.

        hp

--=20
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

--iqn7eqyu362qmivx
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmemU/oACgkQ8g5IURL+
KF3w1w/+Ko8VJWc/6H76AsuxwzUb6vP54DqD4TaBXr5/JiRp1mIkmu7foN3wBiAv
quuM/FQBjJckgeaXQ9litZHB41HRDkyjUUA9nJuEkxPtIjH2B3mGgWcB3YUVpP8J
S+yuz6AbQquivdE/SuM6E13zRH9B2CBQWlFVLBgzQ2lNBP57uG2qKR27xkmkEe7j
TIgdVaIow5V2MIsOOlcFJ3mT31ssujgLflCxa8UpKblS7kNqi0Pdza01cA6ZFP07
Q61AeNTZxhKsm1zAsuE2E6bUMX614nt95CRUiBD+Q40S+01ZGLVQmhLsnDd8MZWi
aZkx20RyFVme8yu2/w4n9NR5u+s5jIWf+dhO5uI+8/dspafauXo33ogEVPS7Oos2
uQ+lqF3aaCiHVjDbNrpJ1PdV3uVhgB8xiyXRdJUA+CVOj3TkrovoxnoaKar/t2O/
UFPO5zmOLNPn0XebYs6Bsmq/VY2q2L+Ldwm4u/6vjuWO+nuXKpQ6qe8q6JJeEa2i
j7Lpqlr1jggubgkeuvsUBAOGrsKLTqNidI7+07CZcOvb7gUGuftZpnb0bnCM+oK2
vDR43R54yk1nMIT7XeExWr0ZR0C+Icu4Zr429YHwWQ9EByhVfbZSZBV8Dxr97IP8
NogzGfFoAW7rTGPPu1bg9bbBFGCpCZgydJgEALvTQa1XIFqhU9s=
=XjjO
-----END PGP SIGNATURE-----

--iqn7eqyu362qmivx--