Jeff Smith of SQLTeam brings us a
great blog post about data in tables vs. data in code.
I especially like his fourth example, which involves "lookup" tables
and what I like to call "magic keys" (generally referred to as "magic
numbers" in other types of code, but who said that keys have to be
numbers?) How often do you see particular surrogate key values
hardcoded into SQL or application code? I see this practice all the
time in my work, and I completely agree with Jeff: It's a VERY
dangerous habit to get into.
One client I recently did some work for has hundreds of different
lookup codes, each with both a text-based representation (usually in
the format, "domain.code" where domain is the business vertical and
code is the actual code within that vertical) and a surrogate integer
key (IDENTITY). The text-based codes, I don't have that much of a
problem with, but in many cases developers chose instead to hardcode
the integer keys -- from the development database they happened to be
working on! And no one ever stopped this practice; instead, the
database people supported the effort by creating data load scripts to
ensure that the integer keys in the development database matched those
in QA and production systems.
So now you have a bunch of code hanging around that looks like:
WHERE SomeId IN (44332, 45084, 59005)
Of course, there are no comments anywhere to help someone understand
what a key such as 44332 might represent. And as a result, there are
people there who actually have big chunks of these codes memorized,
because it's faster to remember than to look them up every time while
maintaining the scripts.
This problem is certainly not restricted to that shop. I've seen it
all over the place both in my work and in forums. It's a very common
anti-pattern and I think it highlights the need for developers to take
a step back from coding and think just for a moment. Ponder
maintainability. Do you really think that the next developer who looks
at your code will know what these numbers represent? Do you think you
will know, when you look at your code again six months or a year down
the road? Furthermore, do you really want to have to memorize codes?
Don't you have better things to do with your time and brain power?
Thanks, Jeff, for bringing up such an important issue!