public inbox for [email protected]  

help / color / mirror / Atom feed

From: Michał Kłeczek <[email protected]>
To: Rich Shepard <[email protected]>
Cc: PG-General Mailing List <[email protected]>
Subject: Re: Lookup tables
Date: Tue, 4 Feb 2025 17:31:13 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>



> On 4 Feb 2025, at 15:27, Rich Shepard <[email protected]> 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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Lookup tables
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox