Date: Thu, 6 Feb 2025 22:03:18 +0100
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: Lookup tables
Message-ID: <20250206210318.kj2j5dvliidpmesy@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>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="lk4s4b6oljmuk5rj"
Content-Disposition: inline
In-Reply-To: <64603f36-b6d5-469b-8d6d-30376842241b@gelassene-pferde.biz>
Archived-At: 
 <https://www.postgresql.org/message-id/20250206210318.kj2j5dvliidpmesy%40hjp.at>
Precedence: bulk


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

On 2025-02-04 22:41:38 +0100, Thiemo Kellner wrote:
> 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.

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.


> 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.

> 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.

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.

        hp

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

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

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

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmelI48ACgkQ8g5IURL+
KF3r7w//bz1Ct2AFJzm1mwOzQe1fvpwg4uU1deyawXygl5ibOnISRqe6mX3BFIdq
Bw7/DnD7upqTGpi/CRiZw1oPLTGaTPaAGjbb1gI16QNvPl8uw3Q3lorjsltR6EhH
gzQbOQfkbscIzNq9MBRP4YsUyTun4l1svbUGTwDjRT2wFGRmCLeHtebBo194Pd25
JCYbOt75Mwhd1C4jRGFFZowTpXm2hMsO4cY6Pe4fMOQUp3lfIZAViuLlm+6bewMl
aNP105LMUKLXFI7TtbAgby78JYHLmqmhFQhD8x1iHhjlvbY6Jz6x4mbkSBkHccmx
QzPOU+lZUrUNiv74OCgDXOZkgakE85cLSRrrNQr7uaSSIaXwCVNpmdPyNozyMqKV
DIGyyTe62KUFx7i539jZ4tbbmhoPrO6+aOPJ4SRuzHloN5Dy1R49zpaIPG3O/Aw3
2A+TnZLYta/G512YR9dUp876gSUA1Tc8vKxxZODelPu/VcMTzhdVl/Umih0SXehL
VpzLdO3XuYrIRNhYmw+E9DrfFmfDxhWshQshCU9rNd6dj2iWx9q5TN+S4dF/RXxz
+PHgK/xxeHTlcZjEvPegVWkLZDPJJOHLe42hPfByJakn6VRk0rTfurvZ2L4AHI1M
0yyJll2IOfBqAubalWuurpNKMPKFvpQhvCZeuhjCrc60Hsd7iDM=
=IynY
-----END PGP SIGNATURE-----

--lk4s4b6oljmuk5rj--