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

On Oracle you can set up deterministic functions this kind of functions that notice the RDBMS that only is need to be called once.

create or replace package MAGIC is
  function magic_number return  number DETERMINISTIC;
end;
/

create or replace package body MAGIC is
  function magic_number return  number DETERMINISTIC
  is
  begin
    return 123;
  end;
end;
/

SELECT MAGIC_DATE
  FROM MAGIC_TABLE
 WHERE MAGIC_ID = magic.magic_number;

Answer 2

As we do queries as Strings this is usually something like

"AND myTbl.iTpe = "+AnEnum.THE_TYPE.ordinal()+"..."

Answer 3

Using SqlServer and probably other dialects you could create functions which return the magic number, so your sql would become

WHERE ... AND ( ( myTbl.myValue = MagicFunction1())     or (myTbl.myValue = MagicFunction2()) )-- Value =  No

Alternatively you could create a single function, or maybe one function/logical set of magic numbers and pass in a parameter.

WHERE ... AND ( ( myTbl.myValue = ProductFunction(1))     or (myTbl.myValue = ProductFunction(2)) )-- Value =  No

Answer 4

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.

Answer 5

Magic numbers are always avoidable, but avoiding them might not always be ideal (eg the performance hit you mentioned). In perfect code you would have another table in the database with a simple identifier (a string) like myMagicNumber that you reference.

However I've found the best solution is to keep a lookup (function/enum etc) in your program code (or SP if that's what you're using). Make sure you always use that lookup to add or select data from the DB.

Answer 6

I agree that magic numbers of this sort should all go in one place. Where that place should be probably depends on the culture around your app. If there is a common config file or area, especially a cascading set of configs, thats a good place.

I guess the first part you should figure is what's so magic about your number. Is it configuration sensitive? is it a mathematical constant? is it a value defined by some outside standard spec? knowing that might help locate the magic number's definition.

Answer 7

I find that a lot of the older generation of developers like to put comments in their code and provide system documentation to back it up. I don't hold with it myself, mind you -- just let people guess what it all means.

Answer 8

Often, magic numbers like this can be stored in the database and loaded into static readonly variables when the database is first started.