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 1tfjBf-004eUu-7Z for pgsql-general@arkaria.postgresql.org; Wed, 05 Feb 2025 17:27:43 +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 1tfjBd-00GILf-L4 for pgsql-general@arkaria.postgresql.org; Wed, 05 Feb 2025 17:27:41 +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 1tfjBd-00GILW-9Q for pgsql-general@lists.postgresql.org; Wed, 05 Feb 2025 17:27:41 +0000 Received: from sm-r-010-dus.org-dns.com ([84.19.1.238]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1tfjBa-003XtV-1H for pgsql-general@lists.postgresql.org; Wed, 05 Feb 2025 17:27:40 +0000 Received: from smarthost-dus.org-dns.com (localhost [127.0.0.1]) by smarthost-dus.org-dns.com (Postfix) with ESMTP id 2AB55A16A0 for ; Wed, 5 Feb 2025 18:27:37 +0100 (CET) Received: by smarthost-dus.org-dns.com (Postfix, from userid 1001) id 1E44EA177D; Wed, 5 Feb 2025 18:27:37 +0100 (CET) X-Spam-Status: No, score=-0.9 required=5.0 tests=AWL,BAYES_00,HTML_MESSAGE, KAM_INFOUSMEBIZ,RCVD_IN_VALIDITY_RPBL_BLOCKED, RCVD_IN_VALIDITY_SAFE_BLOCKED,SPF_HELO_PASS,SPF_PASS autolearn=no autolearn_force=no version=3.4.6 Received: from ha01s018.org-dns.com (ha01s018.org-dns.com [62.108.32.138]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange ECDHE (P-384) server-signature RSA-PSS (2048 bits) server-digest SHA256) (No client certificate requested) by smarthost-dus.org-dns.com (Postfix) with ESMTPS id 88B22A16A0 for ; Wed, 5 Feb 2025 18:27:36 +0100 (CET) Authentication-Results: ha01s018.org-dns.com; spf=pass (sender IP is 146.185.68.202) smtp.mailfrom=thiemo@gelassene-pferde.biz smtp.helo=dummy.faircode.eu Received-SPF: pass (ha01s018.org-dns.com: connection is authenticated) Date: Wed, 5 Feb 2025 18:27:34 +0100 (GMT+01:00) From: Thiemo Kellner To: "pgsql-generallists.postgresql.org" Message-ID: <3fc6b3b2-a685-4698-830f-38abbc617fe5@gelassene-pferde.biz> In-Reply-To: References: <4e7a338-c7d3-e944-20bd-a6e346e175c7@appl-ecosys.com> Subject: Re: Lookup tables MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_37_115814118.1738776454658" X-Correlation-ID: <3fc6b3b2-a685-4698-830f-38abbc617fe5@gelassene-pferde.biz> X-PPP-Message-ID: <173877645642.3280967.8965886775928509676@ha01s018.org-dns.com> X-PPP-Vhost: gelassene-pferde.biz X-POWERED-BY: wint.global - AV:CLEAN SPAM:OK List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_37_115814118.1738776454658 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 04.02.2025 18:12:02 David G. Johnston : > On Tue, Feb 4, 2025 at 9:31=E2=80=AFAM Micha=C5=82 K=C5=82eczek wrote: >=20 > Well, we were talking about lookup tables and not entity modelling... I am under the impression that a lookup table IS an entity. You find them i= n star and snowflake models alike. >=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 rath= er some entities that have identity independent from their attributes. >=20 The presence or implementation of surrogate keys do not define in the least= the type of database. It sole purpose is to surrogate the (speaking) busin= ess key such that updates on that key (think of typo) does not end up in an= update orgy. Ok, maybe to simplify matters if you business key is made of = more than one attribute/column. IMHO it is very good practice to still buil= d a unique key on the business key and place a not-null-constraint on all i= ts attributes. >=20 >=20 > My identity is separate from any single value of my attributes.=C2=A0 Bas= ically any single thing about me can be changed but the coherent existence = of my "self" remains the same. I would not go that transcendently far, but my attributes change but it is = still me, even though my age increases over time as do my good looks. ;-) >=20 > Frankly, the restaurant example the "Owner" of the business should probab= ly be considered part of its primary key - they don't announce "under new o= wnership/management" just for fun - the new owner wants to keep the brand r= ecognition but discard historical opinions that are likely no longer true. I'd prefer the term business key instead of primary key here, as, if you ch= oose to use a surrogate key, that one becomes the PK while the BK is a UQ. = ;-) Sorry, I got carried away. Having said that, I would leave the decision= of taking the owner into the BK to the project. E.g. if you want to have t= he information of unbroken existence of a restaurant at a certain place, I = dare say, it cannot be part of the BK. One could even argue that not even t= he name is part of the BK but only the geolocation (addresses can change to= o). ------=_Part_37_115814118.1738776454658 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 04.02.2025 18:= 12:02 David G. Johnston <david.g.johnston@gmail.com>:

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

Well, we were talking about = lookup tables and not entity modelling...

I am under= the impression that a lookup table IS an entity. You find them in star and= snowflake models alike.



Having s= urrogate keys makes your relational database more like a network/object ori= ented database where rows don=E2=80=99t represent facts but rather some ent= ities that have identity independent from their attributes.


The presen= ce or implementation of surrogate keys do not define in the least the type = of database. It sole purpose is to surrogate the (speaking) business key su= ch that updates on that key (think of typo) does not end up in an update or= gy. Ok, maybe to simplify matters if you business key is made of more than = one attribute/column. IMHO it is very good practice to still build a unique= key on the business key and place a not-null-constraint on all its attribu= tes.


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

I would no= t go that transcendently far, but my attributes change but it is still me, = even though my age increases over time as do my good looks. ;-)


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


I'd prefer= the term business key instead of primary key here, as, if you choose to us= e a surrogate key, that one becomes the PK while the BK is a UQ. ;-) Sorry,= I got carried away. Having said that, I would leave the decision of taking= the owner into the BK to the project. E.g. if you want to have the informa= tion of unbroken existence of a restaurant at a certain place, I dare say, = it cannot be part of the BK. One could even argue that not even the name is= part of the BK but only the geolocation (addresses can change too).
------=_Part_37_115814118.1738776454658--