public inbox for [email protected]  

help / color / mirror / Atom feed

From: Michał Kłeczek <[email protected]>
To: David G. Johnston <[email protected]>
Cc: Rich Shepard <[email protected]>
Cc: PG-General Mailing List <[email protected]>
Subject: Re: Lookup tables
Date: Wed, 5 Feb 2025 06:44:49 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAKFQuwYZdix7ydgUF3z1JHbpjgiofkixwjuSNHBxrnrU4tWiXQ@mail.gmail.com>
References: <[email protected]>
	<[email protected]>
	<CAKFQuwYZdix7ydgUF3z1JHbpjgiofkixwjuSNHBxrnrU4tWiXQ@mail.gmail.com>



> On 4 Feb 2025, at 18:11, David G. Johnston <[email protected]> wrote:
> 
> On Tue, Feb 4, 2025 at 9:31 AM Michał Kłeczek <[email protected] <mailto:[email protected]>> wrote:
>> 
>> 
>> > On 4 Feb 2025, at 15:27, Rich Shepard <[email protected] <mailto:[email protected]>> wrote:
>> > 
>> > Should lookup tables have a numeric FK column as well as the description column?
>> > 
>> > If so, how should I add an FK to the two lookup tables in my database?
>> 
>> I’ve read the whole thread and the reasoning for having (numeric) autogenerated surrogate key is:
>> a) performance
>> b) no cascading updates
>> 
>> I would like to add another dimension to this discussion: logical consistency.
>> 
>> Using the name of a restaurant as primary key gets rid of these logical anomalies because
>> the database model now reflects facts from reality.
> 
> Well, we were talking about lookup tables and not entity modelling...

True. Maybe this is not really the right place to explore the subject so this is my last message in this thread.

> 
>> 
>> Having surrogate keys makes your relational database more like a network/object oriented database where rows don’t represent facts but rather some entities that have identity independent from their attributes.
> 
> Exactly, this is why surrogate keys are not just inventions for performance (when it comes to entities, not attribute lookups) but rather are necessary because of the mutability of real-world objects.
> 
> My identity is separate from any single value of my attributes.  Basically any single thing about me can be changed but the coherent existence of my "self" remains the same.

This is really the difference in philosophy of database design:

In my view a relational database does not contain “entities” and their “attributes" but rather “relevant facts about real world”.
“Facts” are statements constructed from templates (ie. relation predicates) filled with tuple attribute values.

Based on facts recorded in the database I can infer logical conclusions using query language.
The queries are written based on some assumptions about facts recorded in the database and these assumptions are modelled as constraints.

What “facts” are relevant is of course requirements dependent (and sometimes it is necessary to also record time to account for mutability).

In this view surrogate keys are strictly internal and do not have any business meaning
They are no more than “row identifiers” and cannot be sensibly used for constraints (because constraints are modelled based on business requirements).
Their utility is more for technicalities like replication.

> 
> Frankly, the restaurant example the "Owner" of the business should probably be considered part of its primary key - they don't announce "under new ownership/management" just for fun - the new owner wants to keep the brand recognition but discard historical opinions that are likely no longer true.

Indeed - the choice of identifier is a very important one and cannot be hand-waved by blind introduction of a surrogate key :)

—
Michal

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Lookup tables
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox