Update using PK from a lookup table, should I use the PK directly or select it using the associated lookup value? – best practice

foreign keyupdate

This is not asking for an opinion, simply what DBA's who are more professional than me tend to do in this situation.

Assume I have a simple lookup table status_type such as this, where status_id is an autoinc integer PK and status_value is a varchar.

status_id  status_value 
---------  ------------
        1  Active      
        2  Lapsed      
        3  Resigned    
        4  Deceased    
        5  Demurred    
        6  Terminated  
        7  Pending     
        8  Redacted

and another table person has a field the_status INT as a FK linked to status_type from which I can deduce the status string pertaining to that person. This is easy to maintain and I can alter the text of the status_value if necessary without altering the person record and can add new status values as needed.

If, inside a stored procedure, I want to update table person and change someone's status, say to 'Resigned', is it best practice / more common to say

(a) UPDATE person 
    SET the_status  = 3 
    WHERE personID = 1234;

or

(a) UPDATE person 
    SET the_status  = (SELECT status_id 
                       FROM status_type 
                       WHERE status_value = 'Resigned' 
                      ) 
    WHERE personID = 1234;

Option (a) assumes that the ID of 3 will always mean the same as 'Resigned', so it won't matter even if I change the string contained in status_value to 'Left the company' but it needs a magic number hard coded in the stored proc, which I don't really like.

Option (b) does not require me to hard code 'magic numbers' into the stored proc but requires me to know what status_value string is currently held in table status_type to indicate someone voluntarily no longer works for us. Consequently the stored proc will need changing if the status_value in table status_type is ever edited.

Is either way more widely used than the other?

I did look at this and this

Best Answer

This is a little close to an opinionated question, but in general, I think you'll find option a to be the mostly widely used one from the perspective of the database layer. For the purpose of this answer, I'm using the term enum as synonymous with lookup table, for all intents and purposes.

Enums are inherently meant to numerically give meaning to a concept with the added benefit of being able to verbalize that meaning with a name / string value. But it should be a rare event that the meaning of a specific instance of an enum changes from the original meaning for that numerical representation (in fact, if it did, it would likely logically break any existing references to that enum's numerical value). With that in mind, they shouldn't exactly be considered the same as magic numbers (despite being rather close), and it is the string value / name that is more flexible to change.

Should you use option b, you'd run into two issues:

  1. There's a performance difference (though likely negligible in this case) by doing a second lookup on the status_type table as opposed to your UPDATE statement in option a.

  2. If the name / string value of your enum were to change (remember this is very well more possible than the underlying meaning of the instance of that enum's numerical value to change) then your stored procedure would now be broken.

I would go with option a all day.

Related Question