Re: Lookup tables

04.02.2025 18:12:02 David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>:

> On Tue, Feb 4, 2025 at 9:31 AM Michał Kłeczek <michal(at)kleczek(dot)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 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).