Re: Lookup tables

On Tue, Feb 4, 2025 at 10:08 AM Rich Shepard <rshepard(at)appl-ecosys(dot)com>
wrote:

> On Tue, 4 Feb 2025, David G. Johnston wrote:
>
> > 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,
>
> 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.
>

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.

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