MIME-Version: 1.0
References: <4e7a338-c7d3-e944-20bd-a6e346e175c7@appl-ecosys.com>
 <CAKFQuwaPUJCwmf75Uwu1_kLGfOgdt9L+Y-2evudS0RQBNU3-fA@mail.gmail.com>
 <CANzqJaD-qFaf_v1fhhs73kfBFuTqkH_qZk1UGEJh95Wz7uF0Ng@mail.gmail.com>
 <CAKFQuwbhX=61U3nBgrx7483JcGyvuep6sVNYbQ=euo7V0S0wYA@mail.gmail.com>
In-Reply-To: 
 <CAKFQuwbhX=61U3nBgrx7483JcGyvuep6sVNYbQ=euo7V0S0wYA@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Tue, 4 Feb 2025 10:05:20 -0500
Message-ID: 
 <CANzqJaCqpC1-pz3sm0DGF_sHFbRD14CqFsb6Detuyapf9nX84g@mail.gmail.com>
Subject: Re: Lookup tables
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000001abedf062d525786"
Archived-At: 
 <https://www.postgresql.org/message-id/CANzqJaCqpC1-pz3sm0DGF_sHFbRD14CqFsb6Detuyapf9nX84g%40mail.gmail.com>
Precedence: bulk

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

On Tue, Feb 4, 2025 at 9:59=E2=80=AFAM David G. Johnston <david.g.johnston@=
gmail.com>
wrote:

> On Tuesday, February 4, 2025, Ron Johnson <ronljohnsonjr@gmail.com> wrote=
:
>
>> On Tue, Feb 4, 2025 at 9:41=E2=80=AFAM David G. Johnston <
>> david.g.johnston@gmail.com> wrote:
>>
>>> On Tuesday, February 4, 2025, Rich Shepard <rshepard@appl-ecosys.com>
>>> wrote:
>>>
>>>> Should lookup tables have a numeric FK column as well as the
>>>> description column?
>>>>
>>>> If so, how should I add an FK to the two lookup tables in my database?
>>>>
>>>
>>> Most do (have a surrogate PK) since it removes cascading updates
>>>
>>
>> How does a synthetic PK "remove cascading updates"?  Doesn't the decisio=
n
>> on whether or not to cascade update depend on the ON UPDATE CASCADE clau=
se
>> of the FK definition?
>>
>>
> People don=E2=80=99t change synthetic PKs so updates never have to happen=
 on the
> FK side.  Labels do get changed, in which case you have to update the FK
> label side.
>

That's the argument between a synthetic PK and a natural PK.  (Everywhere
I've seen natural PK used -- that was 25 years ago -- you didn't use the
whole text of the description of the PK, you used a code, like 'HRLY' for
hourly wage employees and 'SLRY' for salaried employees. Then, HRLY or
SLRY would go in the t_employee table income_type VARCHAR(4) column,
referencing a lookup table.

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

--0000000000001abedf062d525786
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 9:59=E2=80=AFAM Da=
vid G. Johnston &lt;<a href=3D"mailto:david.g.johnston@gmail.com">david.g.j=
ohnston@gmail.com</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote=
_container"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0=
.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">On Tuesday, F=
ebruary 4, 2025, Ron Johnson &lt;<a href=3D"mailto:ronljohnsonjr@gmail.com"=
 target=3D"_blank">ronljohnsonjr@gmail.com</a>&gt; wrote:<br><blockquote cl=
ass=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid=
 rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr">On Tu=
e, Feb 4, 2025 at 9:41=E2=80=AFAM David G. Johnston &lt;<a href=3D"mailto:d=
avid.g.johnston@gmail.com" target=3D"_blank">david.g.johnston@gmail.com</a>=
&gt; wrote:</div><div class=3D"gmail_quote"><blockquote class=3D"gmail_quot=
e" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204)=
;padding-left:1ex">On Tuesday, February 4, 2025, Rich Shepard &lt;<a href=
=3D"mailto:rshepard@appl-ecosys.com" target=3D"_blank">rshepard@appl-ecosys=
.com</a>&gt; wrote:<br><blockquote class=3D"gmail_quote" style=3D"margin:0p=
x 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Sh=
ould lookup tables have a numeric FK column as well as the description colu=
mn?<br>
<br>
If so, how should I add an FK to the two lookup tables in my database?<br>
</blockquote><div><br></div><div>Most do (have a surrogate PK) since it rem=
oves cascading updates</div></blockquote><div>=C2=A0</div></div><div>How do=
es a synthetic PK &quot;remove cascading updates&quot;?=C2=A0 Doesn&#39;t t=
he decision on whether or not to cascade update depend on the ON UPDATE CAS=
CADE clause of the FK definition?</div><div><br></div></div></blockquote><d=
iv><br></div><div>People don=E2=80=99t change synthetic PKs so updates neve=
r have to happen on the FK side.=C2=A0 Labels do get changed, in which case=
 you have to update the FK label side.</div></blockquote><div><br></div><di=
v></div></div><div>That&#39;s the argument between a synthetic PK and a nat=
ural PK.=C2=A0 (Everywhere I&#39;ve seen natural PK used -- that was 25 yea=
rs ago -- you didn&#39;t use the whole text of the description of the PK, y=
ou used a code,=C2=A0like &#39;HRLY&#39; for hourly wage employees=C2=A0and=
 &#39;SLRY&#39; for salaried employees. Then, HRLY or SLRY=C2=A0would go in=
 the t_employee table income_type VARCHAR(4) column, referencing a lookup t=
able.</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 &l=
t;Redacted&gt;, and butter sauce.<div>Don&#39;t boil me, I&#39;m still aliv=
e.<br><div><div>&lt;Redacted&gt; lobster!</div></div></div></div></div></di=
v>

--0000000000001abedf062d525786--