public inbox for [email protected]  

help / color / mirror / Atom feed

From: Thiemo Kellner <[email protected]>
To: pgsql-generallists.postgresql.org <[email protected]>
Subject: Re: Lookup tables
Date: Wed, 5 Feb 2025 18:27:34 +0100 (GMT+01:00)
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAKFQuwYZdix7ydgUF3z1JHbpjgiofkixwjuSNHBxrnrU4tWiXQ@mail.gmail.com>
References: <[email protected]>
	<[email protected]>
	<CAKFQuwYZdix7ydgUF3z1JHbpjgiofkixwjuSNHBxrnrU4tWiXQ@mail.gmail.com>

04.02.2025 18:12:02 David G. Johnston <[email protected]>:

> On Tue, Feb 4, 2025 at 9:31 AM Michał Kłeczek <[email protected]> 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 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.
> 

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) business 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 build a unique key on the business key and place a not-null-constraint on all its attributes.
> 
> 
> 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 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. ;-)

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


I'd prefer the term business key instead of primary key here, as, if you choose 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 the 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 the name is part of the BK but only the geolocation (addresses can change too).

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