Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
From: =?utf-8?Q?Micha=C5=82_K=C5=82eczek?= <michal@kleczek.org>
Mime-Version: 1.0 (1.0)
Subject: Re: Lookup tables
Date: Tue, 4 Feb 2025 18:08:56 +0100
Message-Id: <6DCD3899-51A8-4032-A189-F2B51AD7CC8F@kleczek.org>
References: <Z6JFgsSGF_kM4yLE@hermes.hilbert.loc>
Cc: pgsql-general@lists.postgresql.org,
 PG-General Mailing List <pgsql-general@postgresql.org>
In-Reply-To: <Z6JFgsSGF_kM4yLE@hermes.hilbert.loc>
To: Karsten Hilbert <Karsten.Hilbert@gmx.net>
Archived-At: 
 <https://www.postgresql.org/message-id/6DCD3899-51A8-4032-A189-F2B51AD7CC8F%40kleczek.org>
Precedence: bulk


> On 4 Feb 2025, at 17:51, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:
>=20
> =EF=BB=BFAm Tue, Feb 04, 2025 at 05:31:13PM +0100 schrieb Micha=C5=82 K=C5=
=82eczek:
>=20
>> It is now completely unclear what it means to change the name of the rest=
aurant for already registered visits.
>> Is it still the same restaurant with a different name or a different rest=
aurant?
>>=20
>> Or let say someone swaps names of two restaurants.
>> That means a user that goes to the same restaurant every day would regist=
er visits to two different restaurants!
>>=20
>> Using the name of a restaurant as primary key gets rid of these logical a=
nomalies because
>> the database model now reflects facts from reality.
>=20
> Reality tends to become so ambiguous as to not be
> reflectable (two entirely different restaurants eventually,
> within the flow of time, carry the very same name).
>=20
> A primary key is very likely not the proper place to reflect
> arbitrary business logic (is it the same restaurant or not ?
> what if two restaurants have the same name at the same time

These are of course problems ( and beyond the scope of my contrived example )=
.

The point is though, that having surrogate PK not only does not solve these i=
ssues but makes them worse by kicking the can down the road and allowing for=
 inconsistencies.


=E2=80=94
Micha=C5=82=20=