Database Design Horrors

Jen McCown, of the MidnightDBA (twitter / blog) recently posted a couple of articles about the basics of Database Design.  As she was writing the second of these posts, she put out a Tweet (well, two tweets) saying:

Help me think this through for a blog: How many ways is it WRONG to have a field mean one thing during the 1st half of processing, and for that same field to mean something different during the second phase of processing?  We’re talking permanent tables here.

I could feel my forehead tense up, ready to be smacked into the desk, partly at the horror, and partly to reduce the impact of the memories of a system I worked on many years ago that didn’t do that, but did something worse.  So much worse.

I managed to convey a flavour of this to Jen, in the tweets that she reproduced in her blog:

Not as bad as for a field to have different meanings (or even types of data) depending on another field, eg if TypeID=3 then Field3 is a phone#, else if TypeID=4 then Field3 is a sort code, or…

I’ve had an exchange of emails with one of the guys I used to work with there, to confirm my suspicions about this abomination.  Between the two of us, we’ve remembered a few extra “features” of this code.

The field currently under discussion was named “FullName4”, in a table called “tblParty” (steady… not that sort of Party).  Ignoring the WTF-age in prefixing the names of all tables with “tbl” (merely stating in its defence that it was originally developed in the 1990s), let’s look at the FullName4 field.  This was a nullable varchar() field (probably varchar(100), but that’s not important).

Anyway, Oscar (name changed to protect the innocent) & I have, between us, remembered the following “features” of this field, and how they depended upon another field, “PartyTypeID”:

• If the Party Type was a Client (provider of work), then FullName4 would be the full name of the client
• If the Party Type was an end customer, then this was (as expected) the name of the customer
• If the Party Type was a bank or building society, then FullName4 contained a flag to indicate if the interest calculations were to be performed Monthly or Daily
• If the Party Type is a Case Manager, then FullName4 contains the numeric ID of a linked party for working out the billing details
• If the Party Type is a Water Authority, then FullName4 contains the fee for a Drainage Search.

So, in one varchar field, we have “valid” reasons for storing a name, a flag, an ID, a currency amount.  I don’t recall any sort of enforcement by use of constraints or triggers on this, so it was possible that the application code could, if the data was poorly maintained (stop sniggering, Jen!), attempt to treat someone’s name as an integer ID…

I’m not going to go into the reasons why this is Not The Right Thing To Do, as Jen’s posts cover it.  When I joined this project, it had been live for some years, and the number of records in this table was well over a quarter of a million, the damage was done and it was too late to (quickly) sort this mess out.  Indeed, when I left several years later, this thing was still an issue.  And, of course, there was no documentation to explain what was going on here, and why.

And now, having remembered all this, I need a drink or three to obliterate the memory again.  When I recover, I may put together a few ideas about what to do if you find yourself in this situation.