Re: Lookup tables

On Tue, Feb 4, 2025 at 9:31 AM Michał Kłeczek <michal(at)kleczek(dot)org> wrote:

>
>
> > On 4 Feb 2025, at 15:27, Rich Shepard <rshepard(at)appl-ecosys(dot)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’ve 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’t 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.