public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ron Johnson <[email protected]>
To: pgsql-general <[email protected]>
Subject: Re: Lookup tables
Date: Tue, 4 Feb 2025 10:19:34 -0500
Message-ID: <CANzqJaBinU8nF56VcdSx21j1dLKZ+Q0MhUdtujYE+CjkGGMssg@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<CANzqJaC3FeJCSy5EUw9VzunBgG3EK6e9=QzUk9wkF8o1Y5iToA@mail.gmail.com>
<CAKFQuwYpoUxB8je87ozcajt2L7BrNtN9j9mTfomU6gw=jsKNNg@mail.gmail.com>
<[email protected]>
On Tue, Feb 4, 2025 at 10:08 AM Rich Shepard <[email protected]>
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!
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]
Subject: Re: Lookup tables
In-Reply-To: <CANzqJaBinU8nF56VcdSx21j1dLKZ+Q0MhUdtujYE+CjkGGMssg@mail.gmail.com>
* 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