public inbox for [email protected]
help / color / mirror / Atom feedRe: Lookup tables
4+ messages / 2 participants
[nested] [flat]
* Re: Lookup tables
@ 2025-02-04 17:08 Michał Kłeczek <[email protected]>
2025-02-05 18:07 ` Re: Lookup tables Thiemo Kellner <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Michał Kłeczek @ 2025-02-04 17:08 UTC (permalink / raw)
To: Karsten Hilbert <[email protected]>; +Cc: [email protected]; pgsql-general
> On 4 Feb 2025, at 17:51, Karsten Hilbert <[email protected]> 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ł
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Lookup tables
2025-02-04 17:08 Re: Lookup tables Michał Kłeczek <[email protected]>
@ 2025-02-05 18:07 ` Thiemo Kellner <[email protected]>
2025-02-05 18:13 ` Re: Lookup tables Michał Kłeczek <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Thiemo Kellner @ 2025-02-05 18:07 UTC (permalink / raw)
To: [email protected]
El 04-02-25 a las 18:08, Michał Kłeczek escribió:
>> 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.
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).
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Lookup tables
2025-02-04 17:08 Re: Lookup tables Michał Kłeczek <[email protected]>
2025-02-05 18:07 ` Re: Lookup tables Thiemo Kellner <[email protected]>
@ 2025-02-05 18:13 ` Michał Kłeczek <[email protected]>
2025-02-05 20:15 ` Re: Lookup tables Thiemo Kellner <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Michał Kłeczek @ 2025-02-05 18:13 UTC (permalink / raw)
To: Thiemo Kellner <[email protected]>; +Cc: [email protected]
> On 5 Feb 2025, at 19:07, Thiemo Kellner <[email protected]> wrote:
>
> El 04-02-25 a las 18:08, Michał Kłeczek escribió:
>>> 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.
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Lookup tables
2025-02-04 17:08 Re: Lookup tables Michał Kłeczek <[email protected]>
2025-02-05 18:07 ` Re: Lookup tables Thiemo Kellner <[email protected]>
2025-02-05 18:13 ` Re: Lookup tables Michał Kłeczek <[email protected]>
@ 2025-02-05 20:15 ` Thiemo Kellner <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Thiemo Kellner @ 2025-02-05 20:15 UTC (permalink / raw)
To: [email protected]
El 05-02-25 a las 19:13, Michał Kłeczek escribió:
>> 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?
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.
>> 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.
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?
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-02-05 20:15 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-02-04 17:08 Re: Lookup tables Michał Kłeczek <[email protected]>
2025-02-05 18:07 ` Thiemo Kellner <[email protected]>
2025-02-05 18:13 ` Michał Kłeczek <[email protected]>
2025-02-05 20:15 ` Thiemo Kellner <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox