What are some good strategies for solving magic numbers in your database?

Question

I work with a lot of processes that have WHERE clauses that look like this:

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

I talked about this with some colleagues, and such code seems inevitable. I think it would be nice to put this code in one (and only one) place. It can be a presentation, it can be a table, etc. I am thinking of a view that selects from a base table and has a bit field that says that the value 1234 or 1235 is 0. Or "N", etc. This way, I can add the value "No" without having to change the code. I would not use UDF for this, too many function calls if you use it in a connection.

What other options are there for working with special values ​​in your database? Are views a good solution to this? Are there any ways to avoid this kind of thing at all? I think that if this value has to change for any reason, I don’t want to deal with the change of hundreds of processes. On the other hand, an additional connection, so this is a performance hit.

Update: if someone has strategies for just getting rid of the damn things that would be good too. As I said, we talked with colleagues, and this seems inevitable in organizations that have a lot of business logic in the db layer.

PS: I saw a few questions about the magic number, but nothing specific to the database.

Answer 1

How many magic numbers are we talking about? If it is less than a few thousand, put them in a table and make a connection. If they are often used in WHERE clauses as a sequential grouping (for example, 1234 and 1235 in your example), assign a category column and use IN or EXISTS. (None of them will be a significant performance hit with a small amount of magic numbers and a corresponding index.)

I hate hard-coded numeric values ​​like the ones in your example, except for special SQL statements. This makes maintenance a real PITA later.

Answer 2

In Oracle, you can configure deterministic functions of these kinds of functions that notice DBMSs that need only 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 3

I agree that magic numbers of this kind should go in one place. Where this place should be probably depends on the culture around your application. If there is a common configuration file or area, especially a cascading set of configurations, this is a good place.

I assume that the first part that you should pay attention to is what is so magical in your room. Is it configuration sensitive? is it a math constant? is this value defined by some external standard specifications? knowing what can help determine the definition of a magic number.

Answer 4

Using SqlServer and possibly other dialects, you can create functions that return a magic number, so your sql will become

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

Alternatively, you can create one function or, possibly, one function / logical set of magic numbers and pass a parameter.

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

Answer 5

Magic numbers can always be avoided, but avoiding them may not always be ideal (for example, the performance you talked about). In the perfect code, you will have another table in the database with a simple identifier (row), for example myMagicNumber that you are referencing.

However, I found a better solution - save the search (function / enum, etc.) in your program code (or SP, if that is what you are using). Make sure you always use this search to add or select data from the database.

Answer 6

I find that many older generations of developers like to add comments to their code and provide system documentation to back it up. I don’t stick with it myself, mind you - just let people know what it all means.

Answer 7

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

Answer 8

Since we execute queries as strings, this is usually something like

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