Database idea

December 7, 2006

Not sure if I wrote about this before - can’t find a reference here, so I’ll assume I didn’t. 

Something that has been a request on a lot of projects I’ve worked on is keeping a changelog of all data changes to a table.  I’ve seen this handled various ways, usually using triggers to look at every INSERT/UPDATE/DELETE statement and record the before/after (or sometimes just the ‘before’) state of each row of data modified.

After wrestling with this idea again about a month ago a light bulb went on above my head (literally, as my wife turned the light on because I was sitting in near darkness!).  Why isn’t this oft-requested/required functionality part of a database engine itself?  Yes, I’m probably not the first to think of this, but it was the first time I thought of it, so cut me some slack - I’m getting old!  If every db table had a corresponding ‘history’ table which could be queried automatically, and it was just a feature of the db, it would make many developer lives easier, and provide automatic auditing for just about any app.

The biggest issue I see here is that most auditing scenarios want the name of the person who did the changes logged as well.  In most traditional apps, the “db user” connecting and issues the queries is not the same as the “end user” using the app.  A standardized way of passing a ‘user ID’ number  to the engine (environment variable per db connection, or something similar) would be a passable way of doing this, I’d think.  Yes, it’s a slight hack, but far less work than writing and maintaining dozens of triggers on tables and keeping the triggers in sync with the table definitions.

Is this something that any databases offer ‘out of the box’?  I haven’t heard of any that do this.  MSSQL?  Oracle?  DB2?

Posted in Business, Database, Development, Ideas, Microsoft, MySQL, Software, Web, opensource