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 1tfYDk-003FCS-6F for pgsql-general@arkaria.postgresql.org; Wed, 05 Feb 2025 05:45:08 +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 1tfYDi-00Bjz5-Rm for pgsql-general@arkaria.postgresql.org; Wed, 05 Feb 2025 05:45:06 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tfYDi-00Bjyw-Av for pgsql-general@lists.postgresql.org; Wed, 05 Feb 2025 05:45:06 +0000 Received: from mail-ej1-x62e.google.com ([2a00:1450:4864:20::62e]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tfYDf-003G0N-2D for pgsql-general@postgresql.org; Wed, 05 Feb 2025 05:45:05 +0000 Received: by mail-ej1-x62e.google.com with SMTP id a640c23a62f3a-ab7515df1faso243401766b.2 for ; Tue, 04 Feb 2025 21:45:03 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=kleczek.org; s=google; t=1738734302; x=1739339102; darn=postgresql.org; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:from:to:cc:subject:date:message-id:reply-to; bh=duihp3AN/4eeUihv0olKKluEiT1F8pm2jrdoyaG+zlw=; b=eRQLBq30uMeFhoDdCeMgxDRd6H0ufHu7O+57vLIc+8rPMAqkYPNz6dNhHoL0rX+nk5 M5ZG/Uag7XRTBrb+1Ev8trITqH9im6SQo1FZOKOSJSc+Qf8gz8woxGXU4IRJJJi6jHNt 45+JF8WqWRNTug1jGf04R1fcne6Vj79+PePew= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1738734302; x=1739339102; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=duihp3AN/4eeUihv0olKKluEiT1F8pm2jrdoyaG+zlw=; b=ddXs9KwdSN+cr9WgOkGhlZvuqPXP9ZMqYOzYW5k9tEEBhT75WHVaLLG6yZ8CGFXpJX OdaVk0zmUy+CiPW91D4J7/RdG1yy9hEYKFmHUqAopVxX0TZpCn8Y73thDs27XXM29gMC 27lo+o2LEPDk55G8eYwDkMozUgSfQdWQdBfVEPWQ4vDtjB014ikfAQA17yj2X5cuEuXw l3/j2Lmj5OhRUuj9wgJI11ReuekydoooTnW9Szl6hjE6hztMj5O0JH9iHla4Qgc+fpKC 3xfb4Z5bUFaA27x2cJKWnn1N8FrgnmPGig6pZWPVNx2TjCivoszgP6LuvmmgGJLlYhxh dxHA== X-Forwarded-Encrypted: i=1; AJvYcCUiHVEUXmp+DVWXsvNOmKLaRILsMn8R3UzjM/rnc2TtIvYE6WcH52ZQMBj7mBGofL0ukrORE3/nWlaxyejO@postgresql.org X-Gm-Message-State: AOJu0YxSCq5bLXQMDlAzfN4LKUCEk7VXFJbMeb5TwnQcJ9olv4Zvpu4v HFAKRGd3cYf6ERnCTkazABgZd6iTwuwwkpcOEvlJsx5UmjhzKtwtPrz8lTIXsLvs/Kz+bmHj5OW tL1s= X-Gm-Gg: ASbGnct9jzGgJAI6crpVTA8sK2vyk8tJ8tYmtRgYxdUYWoHQHH5VMQbCIu9ZfiA4z3p GT9NtX+uClyh7+CPh/vy6Vx3kxSS0LYXjic5Kn8kxg809V6S15LTOL6wmM2Wcp81vKkUucF8oj0 QPk2q30tJEbGc+2YdJ8vUrht+K0pgjGabbA2/to10nQ3jbP+KFFHnFeqPeXZYEHyv1CobsAtnnU 7JDRWU4qv40Yeld0xjTuLDJxjnNqWvcgFHTj5TiLeDyKz6TE4PGPY7oumu7m0SKKeTJEqalnE5C nE9z37fReIvOsU6szvVr7QPubUTZPhcw5QDZ79OitdY= X-Google-Smtp-Source: AGHT+IFSn/JtQumhueksPIpspuuBb43xOfywKoRGV4bWIGbeoebcam9Mny9URERtkAsvd4kILoWXYw== X-Received: by 2002:a17:907:6ea3:b0:aa6:a844:8791 with SMTP id a640c23a62f3a-ab75e346a37mr107824666b.45.1738734301378; Tue, 04 Feb 2025 21:45:01 -0800 (PST) Received: from smtpclient.apple (b15.kleczek.org. [185.20.172.182]) by smtp.gmail.com with ESMTPSA id a640c23a62f3a-ab6e47a793csm1032127766b.4.2025.02.04.21.44.59 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Tue, 04 Feb 2025 21:45:00 -0800 (PST) From: =?utf-8?Q?Micha=C5=82_K=C5=82eczek?= Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_6EA5A264-99FB-4262-93CB-88118931AFB4" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.300.87.4.3\)) Subject: Re: Lookup tables Date: Wed, 5 Feb 2025 06:44:49 +0100 In-Reply-To: Cc: Rich Shepard , PG-General Mailing List To: "David G. Johnston" References: <4e7a338-c7d3-e944-20bd-a6e346e175c7@appl-ecosys.com> X-Mailer: Apple Mail (2.3826.300.87.4.3) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_6EA5A264-99FB-4262-93CB-88118931AFB4 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On 4 Feb 2025, at 18:11, David G. Johnston = wrote: >=20 > On Tue, Feb 4, 2025 at 9:31=E2=80=AFAM Micha=C5=82 K=C5=82eczek = > wrote: >>=20 >>=20 >> > On 4 Feb 2025, at 15:27, Rich Shepard > wrote: >> >=20 >> > Should lookup tables have a numeric FK column as well as the = description column? >> >=20 >> > If so, how should I add an FK to the two lookup tables in my = database? >>=20 >> I=E2=80=99ve read the whole thread and the reasoning for having = (numeric) autogenerated surrogate key is: >> a) performance >> b) no cascading updates >>=20 >> I would like to add another dimension to this discussion: logical = consistency. >>=20 >> Using the name of a restaurant as primary key gets rid of these = logical anomalies because >> the database model now reflects facts from reality. >=20 > 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. >=20 >>=20 >> 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. >=20 > 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. >=20 > 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 =E2=80=9Centities=E2=80=9D= and their =E2=80=9Cattributes" but rather =E2=80=9Crelevant facts about = real world=E2=80=9D. =E2=80=9CFacts=E2=80=9D 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 =E2=80=9Cfacts=E2=80=9D 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 =E2=80=9Crow identifiers=E2=80=9D and cannot be = sensibly used for constraints (because constraints are modelled based on = business requirements). Their utility is more for technicalities like replication. >=20 > 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 :) =E2=80=94 Michal --Apple-Mail=_6EA5A264-99FB-4262-93CB-88118931AFB4 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

On 4 Feb 2025, at 18:11, David G. Johnston = <david.g.johnston@gmail.com> wrote:

On Tue, Feb 4, 2025 at 9:31=E2=80=AFAM Micha=C5=82= K=C5=82eczek <michal@kleczek.org> = wrote:


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

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

This is = really the difference in philosophy of database = design:

In my view a relational database does = not contain =E2=80=9Centities=E2=80=9D and their =E2=80=9Cattributes" = but rather =E2=80=9Crelevant facts about real = world=E2=80=9D.
=E2=80=9CFacts=E2=80=9D 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 =E2=80=9Cfacts=E2=80=9D = 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 =E2=80=9Crow identifiers=E2=80=9D 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 = :)

=E2=80=94
Michal

= --Apple-Mail=_6EA5A264-99FB-4262-93CB-88118931AFB4--