The Database Design Goggles! They Do Nothing!

Today, I started waiting for feedback on a dry, dry, dry technical document I just sent on for the client to do peanut target-practice with. To entertain myself while I wait, I popped open a database sent to me as part of the same project for determining how well I could integrate it into other packages.

Did I get my entertainment? Yes, I certainly did, though in a way reminiscent of how I was entertained by “Hedwig and the Angry Inch“. The entire time I was doing it, I watched with interest as a part of me recoiled in horror and another other delighted in the sheer creative chaos that abounded.

Now… this database isn’t mine, and I’m not sure the creators would appreciate what I’m about to do here… so, I’ma gonna just blur all incriminating detail that could possibly help identify them. Now… dear reader, if by some microscopically small chance, you ARE the creator of this abomination…

GET THEE TO A DATABASE TRAINERY! THINE DB IS POSESSED BY DEMONS OF CHAOS AND BLOODY DOUCH-TARDERY!

Also.. thankyou… today has been a blast from a problem-solver perspective. You’re keeping me in a job, where I can confidently claim professional skills.

So, I started out where I always start out when introducing myself to a new database. “Show me da structure!” I asked, and got something that Microsoft Access renders thusly as an ER diagram:

The original ER Diagram for Database

The original ER Diagram for Database

Pictures are pretty, and hide behind abstraction the full horror. Let me help you get where I got:

  • Rampant redundancy abounds in the form of poorly considered primary keys that allow duplicate rows.
  • There was absolutely zero database-enforced referential integrity found between the tables. Reconstructing relationships by hand by manually comparing column data between tables did ensue.
  • Spurious SQL queries, and tables with junk data abounded. Three tables were obviously junk, and one isn’t related to any of the others in any way shape or form whatsoever.
  • Fantastic Access VB-Script code told me what to expect when on inspection of the first form, I saw the same user-error message misspell the word “empty” as “emty” over, and over, and over again. DRY violations abound!

So anyway, I needed to understand database structure. I jumped into the laborious task of introducing referential integrity. Sadly, I could only go so far. Here’s a pretty ER diagram of the same database, cleaned up to the point where I at least now get how it all hangs together:

ER Diagram of the Database with Referential Integrity Constraints

ER Diagram of the Database with Referential Integrity Constraints

Database Design peeps will notice that some of those relationships don’t have arity specified. I can guarantee you that in every single case, there’s a bunch of crap data in the foreign-key table that is close, but not precisely a foreign key to the primary key of another table.

Also, I noticed that for three of those satellite tables around the monster one in the middle, there was redundancy in the form of a column in the main table that munges up what’s evidently more accurate in the satellite tables.

What does that all mean? Any meaningful query you’re likely to want to do on this database will return crap. Yes peeps, we have full-on GIGO potential so huge it’s dizzying. I could integrate this database into another package, but without a serious scrub-down of the data, there’s not much point.

I’m SOOO done for today. To quote my daughter for the past few nights, “And that’s the end of the story! Goodnight!”

Advertisements

3 responses to “The Database Design Goggles! They Do Nothing!

  1. Pingback: Posterior samples | Sam Clifford

    • I know very few people without an information technology degree who know anything about database design.

      Makes me wonder what decisions are being made out there off the back of a database cooked up without the skill to keep its data sound.

      Like

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