0

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

1
  • as both are hardcoded i am missing the point and don't change the value make a new row with new id
    – nbk
    Apr 5 '21 at 16:20
1

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.

4
  • 1
    Thank you. That was what I was thinking of doing, for much the same reason that you suggest. Status type 3 should have the same 'meaning' regardless of what the descriptive text is. Apr 5 '21 at 16:50
  • But out of interest, how does an OP ask questions about accepted or normal practice from a huge bank of people who know better than the OP without it sounding like asking for an opinion? Such a public Q ands A would be just as valuable to those still finding their way in database administration as a question that makes the OP jump though hoops with code they wrote, things they tried where they got stuck etc. Apr 5 '21 at 16:56
  • @user2834566 Unfortunately I don't write the rules, otherwise I'd agree that some subjective questions and discussion could still have as much benefit as objective ones, in a community like this, in my honest opinion. I've felt the same struggles with having what I thought were valuable and valid questions closed because they just don't fit the standards of the community, which fair enough, the standards are the standards. Normal / best practice questions are generally acceptable as long as they're focussed and we'll detailed. The logic behind the rules is because a little work...
    – J.D.
    Apr 5 '21 at 17:12
  • ...upfront from the OP will usually save a lot of back and forth discussion to clarify those details later on anyway. So it definitely isn't meant to be wasted efforts to ask for those kind of details from the OP upfront. Especially when the community is mostly supported by people donating their time, it's good to have a rhetoric & cadence that allows for the most efficient problem solving. Anyways long story short, I hope my initial comment in my answer wasn't in bad taste (it wasn't meant to be) rather it was just a reminder that the question is a little subjective but it's still a fair one.
    – J.D.
    Apr 5 '21 at 17:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.