When I first started doing software projects, I was really into audit tables. Anything that I thought was even remotely possibly important, I slapped in a table that would track all the changes to the data. For some reason or other, I gradually stopped using them. Dunno….maybe the data just didn’t lend itself to that. Probably more like it was totally excessive.
Anyway, I’ve been doing work for Fidelity building new applications on a database that I did back in 1999. Not an insane number of audit tables but there was one for the table that stores consultant timesheet data. You insert, update or delete a row…it’s going in that audit table.
Well, I got a call a few days ago saying that one of the users suddenly couldn’t see any of her timesheet data from before 4/1/2005. I’m like…crap! I hope there isn’t a bug in the new app that was wiping out old records. I took a look at this woman’s timesheet through the application and — sure enough — no data. Next step, Query Analyzer. Looked at the db. No data. Wow…that’s messed up. This user is INSISTING that there should be TWO YEARS worth of weekly timesheet data. Damn. I’m screwed. I really fucked this up. Damn it.
There are two pieces of new functionality that had just been added that were freaking me out. Feature #1: the ability to delete whole chunks of data from your timesheet. Feature #2: the ability of a “power user” to impersonate and enter time for another user. (This person was one of those impersonators.) Damn!
But then I remembered that there was an audit table. I’m saved! I can restore these accidentally deleted records and save the day (and my ass). Wheeee!
Then I start looking through the audit table data. And looking. And looking some more. And more.
This user is full of crap! There’s no way that there’s missing data. If she’d put the 2 years of time data in, there would be jillions of “insert” and “update” audits. If she’d deleted lots of rows, there would be “delete” audits. The audit data said that (surprise) she’d only entered time for the time periods that were viewable through the application. AND! She’d never deleted a single row!
Eventually, the user conceded that it was possible that she’d never entered time for that whole 2 years.
Unbelievable.
Anyway. It would have been very hard to say that without that audit table.
So…I think I’ll be putting more audit tables into my new database designs.
-Ben
Leave a Reply