Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tfMSt-001nMk-P7 for pgsql-general@arkaria.postgresql.org; Tue, 04 Feb 2025 17:12:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tfMSs-00625r-R2 for pgsql-general@arkaria.postgresql.org; Tue, 04 Feb 2025 17:11:58 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tfMSs-00625i-Fs for pgsql-general@lists.postgresql.org; Tue, 04 Feb 2025 17:11:58 +0000 Received: from mail-oi1-x231.google.com ([2607:f8b0:4864:20::231]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tfMSp-003GmH-2q for pgsql-general@postgresql.org; Tue, 04 Feb 2025 17:11:58 +0000 Received: by mail-oi1-x231.google.com with SMTP id 5614622812f47-3eba583fbe8so1454928b6e.3 for ; Tue, 04 Feb 2025 09:11:56 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1738689114; x=1739293914; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=Ueo2rdFseQB68yygjtqoyhzMSeIlRLose36omsEFk+U=; b=SvEji45nQE4rDjWdI3pawm09dB/mg0c1O7TC7ILzrOajm8KMmkmVsMPHRi/Q3WCnzn CVuO+Y6lpAVL5YjUomRJVXNQsUsE6Lln70IfTXYo5Pd85BllD4Ad5zPkiyz06ZIc9XSf lA6fc+zIMPADRi+aqR1zG0JrVcd8rMwT0A9eyUvdrWmK040QUIBW1gB17y57NdpYQPJ0 EYLE+QY8BP4PlhiYX+HolrzSDZVTbj/QLau1odiZuzYaGEJlKp4HgKLnwg4G3UUiPx2k HTxY5D4g5E4R9nx8JBEv0xCvqcAk+ad82TeKJpzLBDsuWC37mMImHBHmxHSsi1ktGBBa i0WQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1738689114; x=1739293914; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=Ueo2rdFseQB68yygjtqoyhzMSeIlRLose36omsEFk+U=; b=VMyIamJyhB1xsyH3wsPgyk3yOtUY0wlBkRRmKk4aU12E69dPuQb4gtL9uJ2rWuq3Uf hHe33ssMIjFMxlVvOh05LU9QDD/USRyfB75WStGQP7rI2JQ53yff3+ueqd14b6C4jZMo /lUZM86rG6GENi027RJzHLEVmeA8qIfEYAxUJPXTZfCcvaubCsy7/wcGvny8ro/IMFo+ O3jRJDNUfmZFSKQDp7T6Ey2fqD9TWLXUPVIZHId0UDt2aWILqllei220gDbzNlHwDAEq 5lXH/S+FuO3yG7MzdRMzFa3jcAmLLKCf6wDSj3j++HK+1IjFli0QayIPm7/4d3LZKnpK 0cdQ== X-Forwarded-Encrypted: i=1; AJvYcCV5qNXNTcInqebPN/1UyWe5Ys0tTNnXH9fnpYbpguG1mfAL/J41RfjQ4ligjIqxq0xB3/o3PNeAFboEvPYH@postgresql.org X-Gm-Message-State: AOJu0YwZ/7nbmimQmeTcV9IS8YalzUkkoizMdQ4KstRSzlZQCVdsMLrd Q0Nl+2xWabPp5QGQBZlVkl4mUWuaviQ3m+fNkqQVdmLysWHA83PGWZBhKzuzltjVJvEx5RGDRHA S+8PPYfqWiGREVKDOLjPU0NQxIRxZaA== X-Gm-Gg: ASbGnculCbKCP8KNNBCCCKiXrRztCcPrEih//bHC6IORmqqpmU46QXemvx873J6pZ05 gyag+Pv7tFuf8Hqe+ZkdFMJxvDxF9dB3o4iyWbOL/PJXD+vZS6aNGhLwiB76fdWy2TloZuCE= X-Google-Smtp-Source: AGHT+IE72tEPuJ4d6WOX/kRuqX72MFkse5MQkfguT+laltrbzwxqiHU1Z6xTT47o5We+eqHNOUi0hN5NffHLKE+eaac= X-Received: by 2002:a05:6808:13d2:b0:3ea:f809:4459 with SMTP id 5614622812f47-3f323b81847mr17802032b6e.36.1738689114571; Tue, 04 Feb 2025 09:11:54 -0800 (PST) MIME-Version: 1.0 References: <4e7a338-c7d3-e944-20bd-a6e346e175c7@appl-ecosys.com> In-Reply-To: From: "David G. Johnston" Date: Tue, 4 Feb 2025 10:11:18 -0700 X-Gm-Features: AWEUYZnWRezODqZdT09EnoeNbrqGEC6gw60eHDjW-Y_hmizNzmew1qEbyUfkaBc Message-ID: Subject: Re: Lookup tables To: =?UTF-8?B?TWljaGHFgiBLxYJlY3plaw==?= Cc: Rich Shepard , PG-General Mailing List Content-Type: multipart/alternative; boundary="0000000000000c8df9062d541bde" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000000c8df9062d541bde Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, Feb 4, 2025 at 9:31=E2=80=AFAM Micha=C5=82 K=C5=82eczek wrote: > > > > On 4 Feb 2025, at 15:27, Rich Shepard wrote: > > > > Should lookup tables have a numeric FK column as well as the descriptio= n > column? > > > > If so, how should I add an FK to the two lookup tables in my database? > > I=E2=80=99ve 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=E2=80=99t 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. --0000000000000c8df9062d541bde Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Tue, Feb 4, 2025 at 9:31=E2=80=AFAM Micha=C5=82 K=C5=82= eczek <michal@kleczek.org> = wrote:
<= blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l= eft:1px solid rgb(204,204,204);padding-left:1ex">

> On 4 Feb 2025, at 15:27, Rich Shepard <rshepard@appl-ecosys.com> wrote: >
> Should lookup tables have a numeric FK column as well as the descripti= on column?
>
> If so, how should I add an FK to the two lookup tables in my database?=

I=E2=80=99ve read the whole thread and the reasoning for having (numeric) a= utogenerated surrogate key is:
a) performance
b) no cascading updates

I would like to add another dimension to this discussion: logical consisten= cy.

Using the name of a restaurant as primary key gets rid of these logical ano= malies because
the database model now reflects facts from reality.
Well, we were talking about lookup tables and not entity modelli= ng...


Having surrogate keys makes your relational database more like a network/ob= ject oriented database where rows don=E2=80=99t 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 looku= ps) but rather are necessary because of the mutability of real-world object= s.

My identity is separate from any single value of = my attributes.=C2=A0 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 bus= iness should probably be considered part of its primary key - they don'= t announce "under new ownership/management" just for fun - the ne= w owner wants to keep the brand recognition but discard historical opinions= that are likely no longer true.

David J.

=
--0000000000000c8df9062d541bde--