Subject: Re: Lookup tables

From: Michał Kłeczek <[email-masked]>
Date: Tue, 4 Feb 2025 18:08:56 +0100

> On 4 Feb 2025, at 17:51, Karsten Hilbert <[email-masked]> wrote:
> 
> Am Tue, Feb 04, 2025 at 05:31:13PM +0100 schrieb Michał Kłeczek:
> 
>> It is now completely unclear what it means to change the name of the restaurant for already registered visits.
>> Is it still the same restaurant with a different name or a different restaurant?
>> 
>> Or let say someone swaps names of two restaurants.
>> That means a user that goes to the same restaurant every day would register visits to two different restaurants!
>> 
>> Using the name of a restaurant as primary key gets rid of these logical anomalies because
>> the database model now reflects facts from reality.
> 
> 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).
> 
> 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 issues but makes them worse by kicking the can down the road and allowing for inconsistencies.

—
Michał

---

From: Thiemo Kellner <[email-masked]>
Date: Wed, 5 Feb 2025 19:07:30 +0100

> The point is though, that having surrogate PK not only does not solve these issues but makes them worse by kicking the can down the road and allowing for inconsistencies.
Only if you do not see the primary key as the main immutable value 
identifying an object, entity, you name it. Having said that, it is very 
questionable that a natural key (names to name one) can be a suitable 
primary key (think of typo).

---

From: Michał Kłeczek <[email-masked]>
Date: Wed, 5 Feb 2025 19:13:49 +0100

> On 5 Feb 2025, at 19:07, Thiemo Kellner <[email-masked]> wrote:
> 
> Only if you do not see the primary key as the main immutable value identifying an object, entity, you name it.

Surrogate key cannot identify any (real) object by definition :)
What object is identified by PK value 42 in “restaurants” table?

> Having said that, it is very questionable that a natural key (names to name one) can be a suitable primary key (think of typo).

Typos are indeed a problem but adding surrogate key does not solve it, I’m afraid.

—
Michal

---

From: Thiemo Kellner <[email-masked]>
Date: Wed, 5 Feb 2025 21:15:41 +0100

> What object is identified by PK value 42 in “restaurants” table?
What object is identified by a PK value "löasidfhaösliw" in a restaurant 
table? It is the context only giving it sense and not less sense then 42 
or "Pizza Hut". In fact on disk, you won't even find 42 or "Pizza Hut". 
On hard disks, e.g., it is the direction of a magnetic field of several 
locations on the disk. Btw, 42 seems to me a quite geeky name for a nerd 
restaurant.
> Typos are indeed a problem but adding surrogate key does not solve it, I’m afraid.
In how far does it not solve it? Or maybe better asked. Is your problem 
that typos occur or is the problem the amount of hassle to fix it?