On Tue, Feb 4, 2025 at 9:59 AM David G. Johnston <david.g.johns...@gmail.com> wrote:
> On Tuesday, February 4, 2025, Ron Johnson <ronljohnso...@gmail.com> wrote: > >> On Tue, Feb 4, 2025 at 9:41 AM David G. Johnston < >> david.g.johns...@gmail.com> wrote: >> >>> On Tuesday, February 4, 2025, Rich Shepard <rshep...@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 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!
