Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.300.87.4.3\))
Subject: Re: Lookup tables
From: =?utf-8?Q?Micha=C5=82_K=C5=82eczek?= <michal@kleczek.org>
In-Reply-To: <4e7a338-c7d3-e944-20bd-a6e346e175c7@appl-ecosys.com>
Date: Tue, 4 Feb 2025 17:31:13 +0100
Cc: PG-General Mailing List <pgsql-general@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <C7F5BFD3-929B-46CC-B20C-893189F154D5@kleczek.org>
References: <4e7a338-c7d3-e944-20bd-a6e346e175c7@appl-ecosys.com>
To: Rich Shepard <rshepard@appl-ecosys.com>
Archived-At: 
 <https://www.postgresql.org/message-id/C7F5BFD3-929B-46CC-B20C-893189F154D5%40kleczek.org>
Precedence: bulk


> On 4 Feb 2025, at 15:27, Rich Shepard <rshepard@appl-ecosys.com> =
wrote:
>=20
> Should lookup tables have a numeric FK column as well as the =
description column?
>=20
> If so, how should I add an FK to the two lookup tables in my database?

I=E2=80=99ve 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=E2=80=99s 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 =3D $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=E2=80=99t represent =
facts but rather some entities that have identity independent from their =
attributes.

Thanks,
Michal