what are some good strategies for dealing with magic numbers in your database?

Question

I'm working through a wide variety of procs that have a WHERE clauses that look like this:

WHERE ... AND ( ( myTbl.myValue = 1234) 
    or (myTbl.myValue = 1235) )-- Value =  No

I've talked this over with some colleagues and this sort of code seems unavoidable. I think it's a good idea to put this sort of code into one (and only one) place. That might be a view, it might be a table etc. I'm thinking a view that selects from the underlying table and has a bit field that says value of 1234 or 1235 is a 0. Or a 'N', etc. That way I can add 'No' values at will without having to change any code. I wouldn't use a UDF for this, too many function calls if you use it in a join.

What are some other options for dealing with special values in your database? Are views a good solution for this? Are there any ways to avoid this sort of thing altogether? I'm thinking that if that value needs to change for whatever reason I don't want to deal with changing hundreds of procs. On the other hand, extra join so it's a performance hit.

Update: if anyone has strategies for just getting rid of the damn things that'd be great too. Like I said, talked it over with colleagues and these things seem unavoidable in organizations that have a lot of business logic in the db layer.

PS: I saw some magic number questions, but nothing specific to a database.

Answer 1

How many magic numbers are we talking about? If it's less than a few thousand, put them in a table and do a join. If they're frequently used in WHERE clauses as a consistent grouping (like the 1234 and 1235 in your example), assign a category column and use IN or EXISTS. (Neither of those will be a meaningful performance hit with a small amount of magic numbers and an appropriate index.)

I detest hard-coded numeric values like those in your example for anything except ad hoc SQL statements. It makes maintenance a real PITA later on.