MIME-Version: 1.0
References: <4e7a338-c7d3-e944-20bd-a6e346e175c7@appl-ecosys.com>
 <CANzqJaC3FeJCSy5EUw9VzunBgG3EK6e9=QzUk9wkF8o1Y5iToA@mail.gmail.com>
 <CAKFQuwYpoUxB8je87ozcajt2L7BrNtN9j9mTfomU6gw=jsKNNg@mail.gmail.com>
 <b67862aa-c9ac-2ec-c1c7-d2f07a8a39d2@appl-ecosys.com>
In-Reply-To: <b67862aa-c9ac-2ec-c1c7-d2f07a8a39d2@appl-ecosys.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Tue, 4 Feb 2025 10:19:34 -0500
Message-ID: 
 <CANzqJaBinU8nF56VcdSx21j1dLKZ+Q0MhUdtujYE+CjkGGMssg@mail.gmail.com>
Subject: Re: Lookup tables
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000f30690062d52891e"
Archived-At: 
 <https://www.postgresql.org/message-id/CANzqJaBinU8nF56VcdSx21j1dLKZ%2BQ0MhUdtujYE%2BCjkGGMssg%40mail.gmail.com>
Precedence: bulk

--000000000000f30690062d52891e
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Tue, Feb 4, 2025 at 10:08=E2=80=AFAM Rich Shepard <rshepard@appl-ecosys.=
com>
wrote:

> On Tue, 4 Feb 2025, David G. Johnston wrote:
>
> > The point of a lookup table is to provide a unique list of authoritativ=
e
> > values for some purpose. Kinda like an enum. But having the label serve
> as
> > the unique value is reasonable - we only add surrogates for optimizatio=
n.
>
> David,
>
> The industrytypes table has 26 rows, the statustypes table has 8 rows. Yo=
ur
> explanation suggests that for this database adding a PK to each table add=
s
> 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.

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

--000000000000f30690062d52891e
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Tue, Feb 4, 2025 at 10:08=E2=80=AFAM R=
ich Shepard &lt;<a href=3D"mailto:rshepard@appl-ecosys.com">rshepard@appl-e=
cosys.com</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_contain=
er"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bor=
der-left:1px solid rgb(204,204,204);padding-left:1ex">On Tue, 4 Feb 2025, D=
avid G. Johnston wrote:<br>
<br>
&gt; The point of a lookup table is to provide a unique list of authoritati=
ve<br>
&gt; values for some purpose. Kinda like an enum. But having the label serv=
e as<br>
&gt; the unique value is reasonable - we only add surrogates for optimizati=
on.<br>
<br>
David,<br>
<br>
The industrytypes table has 26 rows, the statustypes table has 8 rows. Your=
<br>
explanation suggests that for this database adding a PK to each table adds<=
br>
little, if anything.<br></blockquote><div><br></div><div>How big is the dat=
abase?=C2=A0 A tiny 500MB db just for you can get by with poor design.=C2=
=A0 (But then, why are you using PG instead of SQLite?)</div><div><br></div=
><div>More importantly, will you ever update the descriptions?=C2=A0 Of=C2=
=A0course not! Famous last words.=C2=A0 Having a separate=C2=A0PK means tha=
t you update one row in one column, while what you&#39;ve done means that t=
ens/hundreds of thousands of rows in possibly dozens of tables need to be u=
pdated.</div><div><br></div><div>It also means that <b>you</b>=C2=A0can eas=
ily change things in your ad hoc database <b>without forgetting</b> to upda=
te a table.</div><div><br></div><div>This is called an &quot;update anomaly=
&quot; in relational design theory.</div><div><br></div></div><span class=
=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_s=
ignature"><div dir=3D"ltr">Death to &lt;Redacted&gt;, and butter sauce.<div=
>Don&#39;t boil me, I&#39;m still alive.<br><div><div>&lt;Redacted&gt; lobs=
ter!</div></div></div></div></div></div>

--000000000000f30690062d52891e--