Why storing (too much) data in tables is a Really Bad Idea (tm)

The world is full of Really Bad Ideas which look like good ideas at first glance. My favorite example of this is the Schick Slim Twin disposable razor. The Slim Twin is, as the name implies, a twin-blade razor. It has the "innovative" feature of having a small plastic tab between the blades. The tab is attached to a little button that lets you push the tab towards the business end of the blades thereby (ostensibly) forcing out the razor stubble and other assorted gunk that is trapped in the space between the blades. Schick actually had a series of TV commercials that touted this feature when the Slim Twin first launched many years ago.

It seems like a good if not particularly earth-shattering idea at first glance. That is, until you actually try it. What you find is that the Slim Twin actually collects a lot more gunk than other razors. This is because the plastic tab blocks the space between the blades and actually causes gunk to build up in the first place! When the tab isn't there, any accumulated gunk just falls out of the back of the blades. So this "innovation" actually causes the problem it purports to solve. (And in fact, it makes the problem much worse, because hair can get caught between the tab and the blades, at which point it becomes all but impossible to dislodge. Don't ask me how I know all this.)

Which brings me to this blog entry from Jeff Smith where he asserts that "data belongs in your tables -- not in your code." It seems like a plausible enough assertion on its face, kind of like the idea that having a little tab in the razor to push gunk out ought to be a useful feature. But he doesn't back up this assertion with any actual arguments, only with examples. And in those examples he looks only at the benefits of storing data in tables instead of code and none of the drawbacks.

There are a lot of problems with storing data in tables the way Jeff suggests, but there is one overriding uber-problem, but I won't spoil the fun by telling you what it is just yet. Instead, consider what happens if you follow Jeff's advice, for example:

Your company defines 3 shifts per day:  Shift 1 is 12:00AM-8AM, Shift 2 is 8AM-4PM, Shift 3 is 4PM-12AM.
 
So, when you need to evaluate a DateTime variable to see which shift it falls into, you write:
 
SELECT ..., CASE WHEN Hour(SomeDate) < 8 THEN 1
            WHEN Hour(SomeDate) < 16 THEN 2
            ELSE 3
       END as ShiftNo
FROM ...
 
Great, except you now have data stored in your code -- not in a table!  Let's store our data where it belongs by creating a “Shifts” table:

And now you simply write:
 
SELECT ...., Shift.ShiftNo, Shift.Description
FROM ...
INNER JOIN Shift 
  ON Hour(SomeDate) BETWEEN Shift.StartHour and Shift.EndHour

Now consider what would happen if the company's shift schedule were to change. Simple, you just update the SHIFTS table to reflect the new schedule and you're done, right?

Except that all your historical data is now wrong because it is based on the old shift schedule. And that old shift schedule is now gone.

So the first problem with storing data in tables is that relational databases don't have revision control. Code does. And if you have data that has the kinds of dependencies that revision control systems are good at tracking then you might well be better off having that data in your code so that you revision control system can track it.

But there is a much more fundamental problem with Jeff's advice, and that is that there is no sharp dividing line between code and data. Look at those SQL queries. They are just strings, and hence they are data. So should we store them in a QUERIES table? For that matter, look at the code itself. That is just data too. Why not store that in a table?

The fact of the matter is that the admonition to store data in tables is completely vacuous because the distinction between code and data is arbitrary. It is therefore, just like the tab in the Slim Twin, worse than useless because it seems like such a good idea but in fact it creates problems rather than solving them.

The right way to decide what to put where is to look at the properties of the data you need to store. If it's large quantites of identically structured data that doesn't change in ways that alters referential integrity then it probably belongs in the database. If it's small quantities of data whose structure defines the semantics of other data and which doesn't change at run-time, then it probably belongs somewhere else, if not actually in the code then probably in a configuration file under revision control.

But just because something "looks like data" doesn't mean it belongs in a table.

UPDATE: I do not deny that the problems with Jeff's original example can be fixed. But the point is 1) there are problems and 2) they have to be fixed and 3) the process of fixing the problems is, in this example and many others, essentially, re-invention of revision control. There is no magic that automatically accrues unvarnished benefits merely from moving "data" (whatever that means) out of code and into a database, and applying Jeff's advice uncritically is as likely to create problems as solve them.