> On 4 Feb 2025, at 15:27, Rich Shepard <rshepard@appl-ecosys.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
representfacts but rather some entities that have identity independent from their attributes.
Thanks,
Michal
