Best Practices: The 5 W's of Database Design

My company's hosted employee scheduling software started with a prototype, which worked pretty well, but just wasn't ready for prime time. So my partners and I revisited our design documents, rewrote large portions of the code base, and rebuilt the database, almost from scratch. It hurt, but it was necessary to create a production system. In doing this, we followed some best practices. One of them is implementing the 5 W's of database design: who, what, when, where, why.

Ok, maybe we didn't quite get five Ws out of that. We've got a logging and audit system to cover where and why. But we do have who, what, and when. And it's come in handy a few times.

Every table in our database has a few special fields:

• Creation Time
• Created By
• Last Update Time
• Last Updated By

These four fields are on every tuple, and our application logic updates them appropriately. Once a user logs into our system we've got a user id. Any time he or she updates a piece of data, we store that user id and the current time. We've got a couple of features in the work to provide a front-end for this kind of information. But it's also helpful for debugging and providing support.

Earlier this week I rolled out a beta feature. Shortly after the rollout, I got an email from one of my users. There was some unexpected data floating around the system. After getting the user's permission, I took a closer look.

What had happened is that the new beta feature, let's say it was an easter egg painter, had mis-painted some eggs. Eleven out of twelve eggs had correctly been painted blue. But that twelfth egg had turned red somehow.

After groaning—this is the kind of bug that takes a lot of investigation—I dug a little deeper. I checked out the creation date of the eleven blue eggs. All of them were from just a couple of hours earlier, as expected. But that twelfth, red egg had been created a week earlier.

So I sent my user a friendly reply to her panicked email. I asked her if she had happened to create any red easter eggs last week, without the automated painter. And if so, had she accidentally mixed her blue, automatically painted eggs in with those red ones. About fifteen minutes later I got a reply saying, "oops, my mistake. Thanks Nick!"

Best practice to the rescue. Problem solved, without writing a single line of code. Well maybe just a little SQL.