On Tue, Feb 4, 2025 at 9:31 AM Michał Kłeczek <michal@xxxxxxxxxxx> wrote:
> On 4 Feb 2025, at 15:27, Rich Shepard <rshepard@xxxxxxxxxxxxxxx> 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.
