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:05:20 -0500
Message-ID: <CANzqJaCqpC1-pz3sm0DGF_sHFbRD14CqFsb6Detuyapf9nX84g@mail.gmail.com> (raw)
In-Reply-To: <CAKFQuwbhX=61U3nBgrx7483JcGyvuep6sVNYbQ=euo7V0S0wYA@mail.gmail.com>
References: <[email protected]>
<CAKFQuwaPUJCwmf75Uwu1_kLGfOgdt9L+Y-2evudS0RQBNU3-fA@mail.gmail.com>
<CANzqJaD-qFaf_v1fhhs73kfBFuTqkH_qZk1UGEJh95Wz7uF0Ng@mail.gmail.com>
<CAKFQuwbhX=61U3nBgrx7483JcGyvuep6sVNYbQ=euo7V0S0wYA@mail.gmail.com>
On Tue, Feb 4, 2025 at 9:59 AM David G. Johnston <[email protected]>
wrote:
> On Tuesday, February 4, 2025, Ron Johnson <[email protected]> wrote:
>
>> On Tue, Feb 4, 2025 at 9:41 AM David G. Johnston <
>> [email protected]> wrote:
>>
>>> On Tuesday, February 4, 2025, Rich Shepard <[email protected]>
>>> 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 decision
>> on whether or not to cascade update depend on the ON UPDATE CASCADE clause
>> of the FK definition?
>>
>>
> People don’t 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.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
view thread (49+ messages) latest in thread
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: <CANzqJaCqpC1-pz3sm0DGF_sHFbRD14CqFsb6Detuyapf9nX84g@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