Re: Lookup tables

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

Imagine the following simplified schema with surrogate PK:

create table restaurant (
restaurant_id int not null primary key generated always as identity,
name text not null unique
);
create table restaurant_visit (
visit_id int not null primary key generated always as identity,
guest_username text not null,
when date not null,
restaurant_id not null foreign key restaurant(restaurant_id),
rating smallint not null
);

Let’s say users on your website register restaurant visits and rate them.

The query to register a visit is:
insert into restaurant_visit
select $user, current_date, restaurant_id, $rating
from restaurant where name = $restaurant_name

It is now completely unclear what it means to change the name of the restaurant for already registered visits.
Is it still the same restaurant with a different name or a different restaurant?

Or let say someone swaps names of two restaurants.
That means a user that goes to the same restaurant every day would register visits to two different restaurants!

Using the name of a restaurant as primary key gets rid of these logical anomalies because
the database model now reflects facts from reality.

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.

Thanks,
Michal