public inbox for [email protected]  

help / color / mirror / Atom feed

From: David G. Johnston <[email protected]>
To: Michał Kłeczek <[email protected]>
Cc: Rich Shepard <[email protected]>
Cc: PG-General Mailing List <[email protected]>
Subject: Re: Lookup tables
Date: Tue, 4 Feb 2025 10:11:18 -0700
Message-ID: <CAKFQuwYZdix7ydgUF3z1JHbpjgiofkixwjuSNHBxrnrU4tWiXQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>

On Tue, Feb 4, 2025 at 9:31 AM Michał Kłeczek <[email protected]> wrote:

>
>
> > On 4 Feb 2025, at 15:27, Rich Shepard <[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...


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

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.

David J.

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: <CAKFQuwYZdix7ydgUF3z1JHbpjgiofkixwjuSNHBxrnrU4tWiXQ@mail.gmail.com>

* 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