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.

Advertisements
This entry was posted in SQLServerPedia Syndication, Uncategorized and tagged , , . Bookmark the permalink.

13 Responses to Database Design Horrors

  1. Oscar says:

    I truly love the choice of pseudonym.

  2. thomasrushton says:

    Thought you would appreciate that!

  3. LadyRuna says:

    I’ve seen databases like that… and yes, when I started doing database work, I was taught to name all objects using those goofy prefixes (so tables start w/ tbl; views w/ vew; queries w/ qry; etc) . Breaking that habit was difficult, but I survived. 🙂

  4. Oscar Zamora says:

    Seem that the business owner was in charge of architecting the solution.

  5. thomasrushton says:

    @LadyRuna – I’ve managed to break myself of the habit. It took two projects to do it, but I managed!

    @Oscar – thankfully, that wasn’t the case…

  6. paschott says:

    Sounds like a case of “we’ll just make this one exception”. It won’t happen again and we won’t have to change the DB Design. Fun times when you’re the one coming in later to try to fix them. (Worse if you put them there a long time ago when you really didn’t know any better.)

    • Oscar Zamora says:

      Trying to convince the business owners that a database (or a piece of it) needs to be re-architected is a tough one. I have been through several discussions about that for some of our legacy applications that date from 2003, which were architected by folks who had no idea about databases.

  7. datachix2 says:

    I’m laughing and cringing because I’ve been there. I still have occasional flashbacks of my own db design disasters. Great post, though.

    • thomasrushton says:

      I would just like to disassociate myself from the design of that – I inherited it after it had been in production for three years…

  8. dcawvive says:

    Oh boy that sounds like the One True STUFF table created by a contractor more interested in billing hours than maintainability.

  9. Pingback: 2010 in review – according to WordPress | The Lone DBA

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s