Subject: Lookup tables

From: Rich Shepard <[email-masked]>
Date: Tue, 4 Feb 2025 06:27:54 -0800 (PST)

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?

TIA,

Rich

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 4 Feb 2025 07:41:37 -0700

> If so, how should I add an FK to the two lookup tables in my database?

Most do (have a surrogate PK) since it removes cascading updates and is a
smaller value.

Lots of alter tables and update queries.

David J.

---

From: Rich Shepard <[email-masked]>
Date: Tue, 4 Feb 2025 06:46:41 -0800 (PST)

On Tue, 4 Feb 2025, Rob Sargent wrote:

> I would definitely add an Id for each of the looked up values. The code
> can use the Id (for the join or lookup) and the string value can safely be
> changed (think typo) without ripple effect.  It also prevents other tables
> from referencing the lookup witrh bogus values (those not in the lookup
> table)
>
> Unless your lookup tables are huge I would create a new table matching
> your current table but with an identity column and load from you original
> table.
> https://www.postgresql.org/docs/current/ddl-identity-columns.html

Thanks, Rob. Each lookup table has only a very few rows,

I'll make new tables today.

Regards,

Rich

---

From: Ron Johnson <[email-masked]>
Date: Tue, 4 Feb 2025 09:56:26 -0500

On Tue, Feb 4, 2025 at 9:41 AM David G. Johnston <[email-masked]>
wrote:

> Most do (have a surrogate PK) since it removes cascading updates
>

How does a synthetic PK "remove cascading updates"?  Doesn't the decision
on whether or not to cascade update depend on the ON UPDATE CASCADE clause
of the FK definition?

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 4 Feb 2025 07:59:09 -0700

On Tuesday, February 4, 2025, Ron Johnson <[email-masked]> wrote:

> On Tue, Feb 4, 2025 at 9:41 AM David G. Johnston <
> [email-masked]> wrote:
>
People don’t change synthetic PKs so updates never have to happen on the FK
side.  Labels do get changed, in which case you have to update the FK label
side.

David J.

---

From: Ron Johnson <[email-masked]>
Date: Tue, 4 Feb 2025 09:59:48 -0500

> Should lookup tables have a numeric FK column as well as the description

Does your lookup table just have one column?  (That's what your question
seems to imply, but that makes no sense, since the whole point of a lookup
table is to store some sort of a code in the "child" table instead of the
whole text of the description.)

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 4 Feb 2025 08:04:54 -0700

> whole text of the description.)

The point of a lookup table is to provide a unique list of authoritative
values for some purpose.  Kinda like an enum.  But having the label serve
as the unique value is reasonable - we only add surrogates for optimization.

David J.

---

From: Rich Shepard <[email-masked]>
Date: Tue, 4 Feb 2025 07:05:20 -0800 (PST)

> whole text of the description.)

Ron,

Yes, each has a single column of type names, industrytypes and statustypes.
I've always used the descriptive names in queries.

Rich

---

From: Rich Shepard <[email-masked]>
Date: Tue, 4 Feb 2025 07:08:41 -0800 (PST)

On Tue, 4 Feb 2025, David G. Johnston wrote:

> values for some purpose. Kinda like an enum. But having the label serve as
> the unique value is reasonable - we only add surrogates for optimization.

David,

The industrytypes table has 26 rows, the statustypes table has 8 rows. Your
explanation suggests that for this database adding a PK to each table adds
little, if anything.

Regards,

Rich

---

From: Tony Shelver <[email-masked]>
Date: Tue, 4 Feb 2025 17:11:00 +0200

Seen the suggestions.

After decades of playing with the alternatives, I don't overthink it
anymore.  Pretty much my lookup tables consist of an autogenerated primary
key (smallint or integer), a code (name?) and a description as default.

If there are a lot of tables fitting a common  data format, at times I have
consolidated them into a single table with a 'lookup type' column.

Depending on application requirements, sometimes there I will provide short
and long display names, and a generic description for further
clarification, but usually those requirements are overkill.

Tony

---

From: Ron Johnson <[email-masked]>
Date: Tue, 4 Feb 2025 10:12:33 -0500

> I've always used the descriptive names in queries.

Yeah, that's definitely Bad Design, for the reason David enumerated.

In the lookup table, the PK can be either synthetic (an integer that means
nothing) or natural (a short text code, typically four characters) that is
an abbreviation of the description.

Synthetic keys have been the norm for the past 25+ years, but I have a soft
spot for natural keys.

Natural keys become unwieldy, though, when more than two columns are
required to define primality.

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 4 Feb 2025 08:18:35 -0700

> explanation suggests that for this database adding a PK to each table adds

It’s the FK side where the cost savings are experienced.

David J.

---

From: Ron Johnson <[email-masked]>
Date: Tue, 4 Feb 2025 10:19:34 -0500

On Tue, Feb 4, 2025 at 10:08 AM Rich Shepard <[email-masked]>
wrote:

> as

How big is the database?  A tiny 500MB db just for you can get by with poor
design.  (But then, why are you using PG instead of SQLite?)

More importantly, will you ever update the descriptions?  Of course not!
Famous last words.  Having a separate PK means that you update one row in
one column, while what you've done means that tens/hundreds of thousands of
rows in possibly dozens of tables need to be updated.

It also means that *you* can easily change things in your ad hoc
database *without
forgetting* to update a table.

This is called an "update anomaly" in relational design theory.

---

From: Rich Shepard <[email-masked]>
Date: Tue, 4 Feb 2025 07:41:48 -0800 (PST)

David,

Okay.

Thanks,

Rich

---

From: Adrian Klaver <[email-masked]>
Date: Tue, 4 Feb 2025 08:06:00 -0800

On 2/4/25 07:19, Ron Johnson wrote:

> How big is the database?  A tiny 500MB db just for you can get by with 
> poor design.  (But then, why are you using PG instead of SQLite?)

For the reasons listed here:

https://sqlite.org/quirks.html

In particular:

[...]

2. SQLite Is Embedded, Not Client-Server

3. Flexible Typing

SQLite is flexible with regard to datatypes. Datatypes are advisory 
rather than mandatory.

4. Foreign Key Enforcement Is Off By Default

[...]

> Famous last words.  Having a separate PK means that you update one row 
> in one column, while what you've done means that tens/hundreds of 
> thousands of rows in possibly dozens of tables need to be updated.
> 
> database *without forgetting* to update a table.
> 

-- 
Adrian Klaver
[email-masked]

---

From: Michał Kłeczek <[email-masked]>
Date: Tue, 4 Feb 2025 17:31:13 +0100

> On 4 Feb 2025, at 15:27, Rich Shepard <[email-masked]> wrote:
> 

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

---

From: Karsten Hilbert <[email-masked]>
Date: Tue, 4 Feb 2025 17:51:14 +0100

> the database model now reflects facts from reality.

Reality tends to become so ambiguous as to not be
reflectable (two entirely different restaurants eventually,
within the flow of time, carry the very same name).

A primary key is very likely not the proper place to reflect
arbitrary business logic (is it the same restaurant or not ?
what if two restaurants have the same name at the same time
?). Primary keys are tools at the technical level.

Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

---

From: Rob Sargent <[email-masked]>
Date: Tue, 4 Feb 2025 09:57:16 -0700

> ?). Primary keys are tools at the technical level.
That OP is using a single column is interesting:  Taking this notion to 
the restaurant/visit theme, the list of names or restaurants becomes a 
proper table 'restaurant' (name, addresss, phone, website, etc).  The 
name is as useless as a key as first/last is for person.

---

From: Ron Johnson <[email-masked]>
Date: Tue, 4 Feb 2025 12:03:22 -0500

On Tue, Feb 4, 2025 at 11:31 AM Michał Kłeczek <[email-masked]> wrote:
[snip]

> It is now completely unclear what it means to change the name of the
> restaurant for already registered visits.
> That means a user that goes to the same restaurant every day would
> register visits to two different restaurants!
>

Valid concerns, which means that you add a new restaurant record when the
name changes.

---

From: Rob Sargent <[email-masked]>
Date: Tue, 4 Feb 2025 10:05:42 -0700

On 2/4/25 10:03, Ron Johnson wrote:
>     It is now completely unclear what it means to change the name of
>     the restaurant for already registered visits.
> Valid concerns, which means that you add a new restaurant record when 
> the name changes.
>

And there goes your unique index on phone number :)

---

From: Ron Johnson <[email-masked]>
Date: Tue, 4 Feb 2025 12:10:03 -0500

> And there goes your unique index on phone number :)

I don't think I'd ever do that, since phone numbers can get reassigned.

Capital-L Large and old businesses probably don't even have unique indices
on SSN.

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 4 Feb 2025 10:11:18 -0700

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

---

From: Rich Shepard <[email-masked]>
Date: Tue, 4 Feb 2025 11:08:42 -0800 (PST)

> your current table but with an identity column and load from you original

I created a new table:

create table ind_types_lu (
   ind_nbr serial primary key,
   ind_name varchar(32) not null
);

Now the database has the new table and a new sequence:

  public | ind_types_lu             | table    | rshepard
  public | ind_types_lu_ind_nbr_seq | sequence | rshepard

I want to replace the old lookup table (with no FK) with this one. Can I
use:

alter table rename ind_types_lu to industrytypes;

and have the sequence name changed, too?

TIA,

Rich

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 4 Feb 2025 12:11:25 -0700

On Tuesday, February 4, 2025, Rich Shepard <[email-masked]> wrote:

>
> I want to replace the old lookup table (with no FK) with this one.
>

Is the use of FK here intentional or a typo?  Because everything written so
far leads me to believe it should be PK.  Pri,are key is the unique side,
Foreign key is the usage side.

David J.

---

From: Rich Shepard <[email-masked]>
Date: Tue, 4 Feb 2025 11:33:40 -0800 (PST)

On Tue, 4 Feb 2025, David G. Johnston wrote:

> Is the use of FK here intentional or a typo?

Sigh, typo. Should be PK.

Rich

---

From: Rich Shepard <[email-masked]>
Date: Tue, 4 Feb 2025 12:57:34 -0800 (PST)

> I'll make new tables today.

That did not work as well as I expected. Instead, I added a new column (type
`serial') to each of the two lookup tables. That adds a PK to each while
retaining the table and column names referenced by other tables.

Thanks for all your comments,

Rich

---

From: Thiemo Kellner <[email-masked]>
Date: Wed, 5 Feb 2025 18:27:34 +0100 (GMT+01:00)

> Well, we were talking about lookup tables and not entity modelling...

I am under the impression that a lookup table IS an entity. You find them in star and snowflake models alike.

>> 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 thei…

The presence or implementation of surrogate keys do not define in the least the type of database. It sole purpose is to surrogate the (speaking) business key such that updates on that key (think of typo) does not end up in an update orgy. Ok, maybe to simplify matters if you business key is made of more than one attribute/column. IMHO it is very good practice to still build a unique key on the business key and place a not-null-constraint on all its attributes.
> 
> 
> 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.

I would not go that transcendently far, but my attributes change but it is still me, even though my age increases over time as do my good looks. ;-)

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

I'd prefer the term business key instead of primary key here, as, if you choose to use a surrogate key, that one becomes the PK while the BK is a UQ. ;-) Sorry, I got carried away. Having said that, I would leave the decision of taking the owner into the BK to the project. E.g. if you want to have the information of unbroken existence of a restaurant at a certain place, I dare say, it cannot be part of the BK. One could even argue that not even the name is part of the BK but only the geolocation (addresses can change too).