MIME-Version: 1.0
References: <4e7a338-c7d3-e944-20bd-a6e346e175c7@appl-ecosys.com>
 <CANzqJaC3FeJCSy5EUw9VzunBgG3EK6e9=QzUk9wkF8o1Y5iToA@mail.gmail.com>
 <50498443-1599-c62f-b3b7-485828c54e22@appl-ecosys.com>
In-Reply-To: <50498443-1599-c62f-b3b7-485828c54e22@appl-ecosys.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Tue, 4 Feb 2025 10:12:33 -0500
Message-ID: 
 <CANzqJaCEUN0UnpjW29guR1psfw3JhNmR_AeHEMMmoxCzKJXE_g@mail.gmail.com>
Subject: Re: Lookup tables
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000db1414062d5270a8"
Archived-At: 
 <https://www.postgresql.org/message-id/CANzqJaCEUN0UnpjW29guR1psfw3JhNmR_AeHEMMmoxCzKJXE_g%40mail.gmail.com>
Precedence: bulk

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

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

> On Tue, 4 Feb 2025, Ron Johnson wrote:
>
> > Does your lookup table just have one column?  (That's what your questio=
n
> > 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 t=
he
> > whole text of the description.)
>
> Ron,
>
> Yes, each has a single column of type names, industrytypes and statustype=
s.
> 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.

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

--000000000000db1414062d5270a8
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:05=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, R=
on Johnson wrote:<br>
<br>
&gt; Does your lookup table just have one column?=C2=A0 (That&#39;s what yo=
ur question<br>
&gt; seems to imply, but that makes no sense, since the whole point of a lo=
okup<br>
&gt; table is to store some sort of a code in the &quot;child&quot; table i=
nstead of the<br>
&gt; whole text of the description.)<br>
<br>
Ron,<br>
<br>
Yes, each has a single column of type names, industrytypes and statustypes.=
<br>
I&#39;ve always used the descriptive names in queries.<br></blockquote><div=
><br></div><div></div></div><div>Yeah, that&#39;s definitely Bad Design, fo=
r the reason David enumerated.</div><div><br></div><div>In the lookup table=
, the PK can be either=C2=A0synthetic (an integer that means nothing)=C2=A0=
or natural (a short text code, typically four characters) that is an abbrev=
iation of the description.</div><div><br></div><div>Synthetic=C2=A0keys hav=
e been the norm for the past 25+ years, but I have a soft spot for natural =
keys.</div><div><br></div><div>Natural keys become unwieldy, though, when m=
ore than two columns are required to define primality.</div><div><br></div>=
<span class=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=
=3D"gmail_signature"><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;Redact=
ed&gt; lobster!</div></div></div></div></div></div>

--000000000000db1414062d5270a8--