Azure DevOps, Scrum, & .NET Software Leadership and Consulting Services

Free course! Predicting the Future, Estimating, and Running Your Projects with Flow Metrics

In praise of audit tables.


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

SUBSCRIBE TO THE BLOG


4 responses to “In praise of audit tables.”

  1. […] discovered them reading through this short article. This entry was posted in dba and tagged audit, database-design by admin. Bookmark the […]

  2. evan Avatar

    God bless that there is an audit table! Now I know the importance of audit tables.

  3. […] I came across them reading this article. […]

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.