Database Normalization is a Communist Plot!

One fine day, upper management decided to re-organize us, the outsourcing team, to better align with our core client's organization.  We went from groups defined by specialities to groups defined by the department in the client company we "faced".  Thus we inherited a bunch of web applications under our care.

In 2005, the apps were all coded in classic ASP.  No, not the nice ASP.Net, but it's older, nastier brother.  The one with the scars and switchblades for programmers who want to write "nice" code.  There are good ways of coding ASP pages, and there are bad ways.  Then there are the ways you figure out when you are forced to write an ASP application for the first time in your life, and you just scrounged examples from the Visual Studio docs and books. The apps were the latter case.

Now the applications themselves were a bit of a horror show, for example, one page was recycled as the results page for five separate and unrelated queries—the programmer didn't want to re-write all that nasty ADO code, ASP loops and table mark-up.  Other things were horrifically abbreviated names: sAnnPlanR1S2.

Other wonderful things were the very, very specialized purposes these applications were created for.  For example, one application that is currently making my current co-workers consider changing careers is basically a glorified spreadsheet for one--yes, one--manager to track the budgets of the people directly reporting to him.  All the other managers at his level make do with a well-organized Excel spreadsheet.  He figured it would be cheaper for the company if we had a full-time developer dedicated to tweaking his "spreadsheet" ASP app every month.

Oh, but the fun doesn't stop there, kids.  The real horror came when I had to deal with the data model.

The First Circle of Hell...

What the hell...?

The first of these applications I had to touch was a simple equipment replacement database.  When a cable was removed from an underground duct, the engineer or field crew would use this application to record the kind of cable pulled out and the kind of cable pulled in.  Up to three segments could be changed in one job.  Why three?  Who knows...

The first WTF was that all three cable records were denormalized into the main table.  There were three sets of columns that only differed by their prefix: R1, R2 and R3. All six columns within each set were lovingly  duplicated.  Right down to the typos.

You see, for each cable, there were six attributes.  Two attributes describing the cable pulled out--sensibly abbreviated at PO--and two attributes describing the cable pulled in--sensible called PI.  Except for one of the two attributes that was mistyped as P1.

So there were  columns like this:

R1...PO_Type

R1...PO_Mfr

R1...P1_Type

R1...PI_Mfr

The mother-loving fool mis-typed PI as P1.  But oh, it gets better!

R2...PO_Type

R2...PO_Mfr

R2...P1_Type

R2...PI_Mfr

R3...PO_Type

R3...PO_Mfr

R3...P1_Type

R3...PI_Mfr

Yes, the typo was lovingly replicated through the other types.

Oh, but it gets better, folks!

The Second Level of Hell

There was another of these classic ASP applications we had to maintain.  It was simply a workflow management application that allowed different people in different departments to indicate what work they had done on a job and what was expected next.  For each of these jobs, there were something like 20 checkpoints.

The sensible thing to do would be a 1:n relation between a job and a table of check-points, but no, not our boys!  For our boys, database normalization was a communist plot!

   

...	R01	R01_USER	R01_DATE	...	R32	R32_USER	R32_DATE	...
...	Y	JDOE	2003/02/01	...	N			...

Almost 40 tracking points denormalized into a single row...