Say I have field Ice_Cream.flavor, with the current choices in lookup table Flavor.flavor.
I use Flavor.flavor like an enum() list, storing the value, not the record ID, in Ice_Cream.flavor. If Flavor.flavor changes, I don't want to update Ice_Cream:flavor. I want it to stay as created.
Should I set up Ice_Cream.Flavor as a foreign key, so I can see the source of the values in my ER diagram, or not?
If you want
Ice_Cream.flavor to stay as created even if there is no matching record in
Flavor (which is what your question sounds like) then you cannot create a
FOREIGN KEY relationship, it will not allow that condition to occur in your database.
Furthermore, if you're storing the actual text
Flavor.Flavor string in
Ice_Cream.Flavor, there's no particular reason to have a separate
RecordID column in