Wednesday, May 2, 2012

Database Programmer - The Importance of History Tables


Any programmer who has taken over where another programmer left off can easily compile a list of things they wish the previous programmer did or didn't do. Some people are anal about naming convention, some people are unhappy about the choice of language, choice of environment, or too large of a dependency on third party libraries.

Truth be told, when i took this job, I've complained to myself about ALL of these (my girlfriend can tell you all about it!) All those complains aside, the problem that seems to come up the most often and ends up consuming the largest amount of time fixing, is bad database architecture.

With that bad architecture, there was also a gaping lack of audit logs or any trail to follow when trying to find out how certain data in the database got to the current state it's in. As time has progressed, I've added history tables to some of our most important & and active tables, but as any database programmer knows...doing it right AFTER the fact can take up a considerable amount of time.

If my only job were to pose as the database administrator, this would not be a issue, it would just be my job. But since this is still considered a "start-up company" and our workforce is very small, i have the opportunity of filling all the roles that a programming team normally gets to divide and conquer in their specialized roles. That leaves me with little time, and a lot of frustration towards a database that wasn't done right in the first place.

If you are designing tables for a company, here's my plead to you: PLEASE, do it right the first time!

Here's why:
  • Change in Vision:  Once data is modified or, ultimately, lost....there is no getting it back. With history tables, if requirements change (we all know that the "visionaries" we code for are never done with their idea) on how the data should be structured, you can't recreate the data that was lost because of it.
  • Security: If you have programmers on your team, or who work on your project in the future who aren't that well verse in best programmer practices for security, they may leave an open invitation for hackers, who may happen to target you, to find some way to exploit it and possible alter or remove data. With proper structure, and more importantly audit/history tables, you'll be able to recreate data that was changed after the last backup of the database.
  • Reports: Nothing makes the boss more happy than being able to see data aggregated into some form of report. The more data, the better. I'm not one who is skilled in looking at a years worth of data and graphs and being able to change parts of our business model to make for a more productive upcoming year, but i know that some of the people we code for are!
  • Coding Mistakes: I know WE are all perfect and never make a mistake (right?), but it's always that dumb programmer who doesn't really know how to code, but thought it would be cool pretend to know how who makes a mistake that accidentally updates your entire customer database instead of just the one intended record.

I'll tell you a story:
When i was a young programmer (Hell, i'm still young), i was working in PL/SQL Developer and i wrote out a query to fix the price of certain people's orders who had gotten placed wrong due to a coding issue in a new part of the system (albeit a problem created by one of my coworkers), had it all formatted pretty so it was easy to read, highlighted it to execute it, but didn't scroll down far enough to include all of my WHERE clause. Ooops?  I changed the price of tens of thousands of orders instead of just the ~10 or so that fit my intended criteria. I about crapped my pants right there.
Fortunately, through my hyperventilating about the possibility of losing my job over a simple mistake,  i remembered that i had the forthought to create a history tables for the table i was updating back when i first created it. I immediately had all automated processes that work on that table temporarily suspended and after a couple of energy drinks, and a lot of sweating, I was able to restore the data.

Now i'm sure this would never happen to any of you, but what if young me comes into do some programming work for you and, because of my obvious excess of charm, manages to find a way past the screening process? Yeah, it will happen, and when it does, you'll be thankful you took the time to design your database properly.

Today, i write this to vent my frustrations about a situation that i'm having to clean up after regarding just a few of our orders: 
We have a partnership with a company that handles financing on some of our orders. We have a product that's tier'd to give additional access to the system depending upon the size of the product the customer chooses. Unfortunately, said finance company chose to originally accept, and then later deny financing on said order resulting in an order going through the completion process and then later needing to be reversed.
Due to poor site & database design, the access to the system is not based off of what data is in the database, but rather access values are inserted into a particular table based off of customer input (that is never stored in it's raw format) received at the completion of the order. Since there were never any history tables attached to this particular table, access was overwritten, and original access from the customer's original input was lost entirely. 


Are you pulling out your hair yet? Stressed out? 

If you're not, then congratulations, you've mastered the art of "not caring" :)





Please adjust the time expectations of the people you program for, to allow yourself enough time to Do Things Right the first time!

Here's a good write-up of how to design and what should be included in your history tables: http://goo.gl/Qxwyc