Data hell, part 2

A question came in, What do I mean by data hell exactly? (Presumably from a UI guy :-)

Here are some of the characteristics:

1. duplicate data in multiple tables. Of course, data from one table doesn't match the others -- it never does because that's what happens with duplicate data.

2. no primary keys on tables. How to identify programmatically the exact row that you want? Not easily. Sometimes filters work in the right context -- e.g., "I know know there's only one manager in New Jersey, so I can filter to New Jersey and get the right manager." Painful.

3. columns that act as pseudo-primary keys vary by table, even if it's the same data. For example, every car has a unique license plate. That's the primary key for Table1. Wait, every car also has a VIN. That's the primary key for Table 2. Now join them together. Exactly.

4. the pseudo-primary keys aren't reliable. The column in Table1 that holds license plates has VINs for some rows. The join suggested in #3 to Table2 actually works -- sometimes. When? Hard to say.

5. cross-keys aren't reliable. To solve the problem, VIN is often added to Table1 or license to Table2. If you're really "lucky", as I am, you'll have both in both. Unfortunately, sometimes the two keys are out of sync, as predicted in #1: a car gets in Table1 with the one license and VIN, the VIN matches to a VIN in Table2, but that row in Table2 has a difference license.

6. multiple naming conventions. Can't remember if the column is spelled "licenseNum", "LicenseNum", "license_num" or other variations with "number", "no", etc.? That's because each table has a different variation. Often, a table will be consistent with itself (though often it won't be), but different programmers add new tables and bring their own convention without concern for fitting in.

7. just lots and lots of bad or missing data. Data entry just not happening reliably.

What do you do about it? It depends on how much time you have.

If it's a long project, you painstakingly fix the data in some key tables, make sure the input processes are clean. Then you build out from there, either refactoring tables or fixing data at each point along the way. All the while, you try to fit in other business requests as you can.

Sometimes, the business can't wait, and the requests pile up faster than you can take them off. Your only hope is to hire some good database developers and work like crazy. Keep at least one or two of them away from the firing lines, so they can fix things in the background.

Worst is if it's a short-term project. You might think that, being short-term, that is the easiest. Ride it out until it winds down, right? If you've been an app developer for any amount of time, you know that apps very rarely die. If the app works, someone will find a use for it. But, because it's considered short-term, no funds will be invested. It will go on living like a zombie. The people who keep these things going are real heroes.