There’s a rule that most of us were taught in kindergarten–clean up after yourself. Why do we so often disregard that rule when we’re managing a database? The excuses are obvious ones: we don’t have enough time, it’s boring work, we never know when we might need something again, and so on. The problem is, those are just excuses.
If you work at the same company for more than 6 months as a DB owner, I guarantee that you will wind up wasting time trying to remember what a field was for, what a line of code was supposed to do, etc. Personally, I’d prefer to spend that time at the front end, where I can control it; and, frankly, it’s good karma to think about the next guy that has your job. Even if you don’t care about these issues, there are ones that strike very close to home: If your company decides to invest in ERP or CRM solutions, do you want to be the guy that has to do the data transformations? That situation can be horribly painful even in the best of worlds–better make it easier now.
The following are the Eight Commandments Of Cleanliness. I couldn’t think of ten. Sue me.
THOU SHALT DESIGN BEFORE YOU BUILD:
The less thought you put into designing a database, the more garbage there will be to clean up later. If you aren’t familiar with ERD, now is the time. Even Visio is better than nothing.
THOU SHALT DOCUMENT:
Every stored proc, every script. No shorthand allowed. My preferred format is to preface each comment with your initials (full name if it’s a large company), the date the code was written, and a brief description of what the code does. Include the reasons–don’t assume the guy coming after you knows why. When you change the code, don’t remove the original comment. Add a new one. If you don’t, and I get your job when you leave, I will personally hunt you down and smite you. Comment your diagrams, too. Didn’t create diagrams? Refer to commandment number 1, and prepare to be smote (smited? Hmmm…).
THOU SHALT NAME CONSISTENTLY:
Find or design a naming convention, document what it is, and stick to it. Within reason, it doesn’t matter what it is; personal preference is generally a good guide. Just don’t name your tables after NFL teams. I mean it. If there is any interest in this, I’d be happy to post the convention I like.
THOU SHALT NOT REUSE FIELDS:
More often than not, when new data is collected, developers tend to throw it in any unused field. This can cause intense confusion, as well as physical pain (don’t forget me and my predilection for smiting those who flout the Commandments). Create a new field. It isn’t THAT hard.
THOU SHALT DROP OBJECTS:
Clear out your temporary stuff regularly, and by that I mean “at least once a week”. If you leave “development” or “testing” objects lying around, they tend to become permanent. I’ve seen it happen. Even if they don’t, it’s messy and confusing to the next guy. If you’re worried that you might need something back, set up an archive database that you can move all that stuff to.
THOU SHALT FRAGMENT YOUR CODE:
Generally speaking, it’s possible to break up large, complex select statements into more readable bits, with little or no loss of efficiency. Use those commenting skills that you learned back in commandment #2.
THOU SHALT BE BIG BROTHER:
Keep an eye on your users. Remove accounts that aren’t used, remove stored procedures that aren’t used, etc. If you don’t do this, your system will spiral out of control and cause the end of the world as we know it. Scientists believe that this situation caused the Big Bang.
THOU SHALT MAKE EVERYONE ELSE DO IT TOO:
C’mon, everyone knows that YOU’RE the real brains of the company. The fact is, if only one developer is following these rules, they won’t work. Your entire organization has to be on board. It isn’t that hard… generally, I trap people in a small room with bad ventilation and explain 4th and 5th Normal form to them until they’ll agree to anything. WARNING: This does not work when picking up women at bars.
In conclusion, there’s no time like the present. Get it over with now, and you’ll thank me later. Unless you’re one of the ones I’ve smote.
Comments and suggestions for future articles are gratefully accepted. I will also post corrections if someone points out a mistake.
