Re: Lookup tables

On Tue, Feb 4, 2025 at 9:59 AM David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> On Tuesday, February 4, 2025, Ron Johnson <ronljohnsonjr(at)gmail(dot)com> wrote:
>
>> On Tue, Feb 4, 2025 at 9:41 AM David G. Johnston <
>> david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>>
>>> On Tuesday, February 4, 2025, Rich Shepard <rshepard(at)appl-ecosys(dot)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 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!