On Tue, Feb 4, 2025 at 10:08 AM Rich Shepard <rshepard@xxxxxxxxxxxxxxx> 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!
