The other day I received an e-mail from a buddy of mine who has started a new contract that, to complete, he must tackle the conversion of legacy data from an old system to a new one.
Here’s what he wrote after his first couple of days: The good part is that it appears that I have been slated for new development while the other guy is doing maintenance. The bad part is that none of the existing Access databases have relationships, primary keys or even (in many cases) indices. Ouch. Lots of free-flowing left and right joins, too.
I have heard this story before. It sends me over the edge every time.
It would be really nice if IT professionals would get a grip on the rudiments of relational databases sometime before databases become as quaint as Paleolithic flint tools. By this I don’t mean being able to argue the virtues of eighth normal form as if you were in some geekasaurus computer science class. Rather, how about maybe using third normal form in a real life problem?
By the way, if you claim to be an IT professional and don’t know what third normal form is, I suggest buying a book and reading up on it. This is because you are possibly one of the many people who don’t use basic database features and either a) code it in C because it’s fun or b) use 800 Excel or Lotus sheets and write absurd macros to link all the data.
An example of a real-life problem for which you can use simple database programs occurred on my project a couple of weeks ago. My rollout schedule changes faster than the average rotation of an electron around an atom. My project software only tells me how far I have fallen from the original path, not what the intermediate steps were and why they changed. To help me track and report this, I made a database which told me the rollout date changes, what regions it applied to, and why it changed. Every week I can generate a report that tells me the initial, the previous and the current plan dates. It took an odd query to produce the report, but it has been worth it. Without understanding database design, I’d be using a really ghastly Excel sheet and doing a horrific amount of cut-and-paste.
So are your database dysfunctional? Test yourself on your basic database knowledge:
A Table is:
a) A place you put coffee.
b) A set of rows and columns of information with predefined input restrictions on the fields.
c) The top of a mountain in Cape Town.
A Primary Key is:
a) The first key on your key ring.
b) One or more fields that uniquely identify a record in a table.
c) The key that Beethoven used in his Fifth Symphony.
A Foreign Key is:
a) The existence of a key field value from another table in the table you are looking at, providing a link back to the parent table’s detail.
b) A key to your lover’s apartment in Paris.
c) A French person trying to influence Beethoven’s Fifth.
A Relationship is:
a) Something you should be having with your spouse, not your lover in Paris.
b) A connection between two tables that enforces business logic.
c) The definition of the connection between a Primary and Foreign key.
An Index is:
a) Something at the back of your Access manual.
b) A way to help speed up searches for frequently referenced data.
c) The finger you put up your nose when confused.
Normalization is:
a) The process of convincing your children that you are not a creature from a pod after they watched Invasion of the Body Snatchers when you told them not to.
b) Making all the data in the database UPPER case.
c) Taking data provided by the previous IT professional and turning the flat ASCII files with horrific amounts of repeated data (or huge badly-constructed tables) into simple tables with clear relationships between them that represent the information the customer needs to track.
The answers to this quiz can be found at a computer bookstore near you.
Ford is a Vancouver-based systems consultant. He can be reached at quokka@portal.ca.