Best implementation for fully auditable data model?

Question

My requirement is for a data model where a full audit trail is retained for changes to every attribute of every object. Object definitions are also fluid: new attributes can appear or go away over time. This audit trail will live separately from the original databases, so a trigger-based auditing model won't work.

In a relational database, I can implement this with a single large ATTRIBUTE_HISTORY table that records every individual change to each attribute, with appropriate timestamp and responsibility fields.

My question: are any of the newer storage models (BigTable, HBase, CouchDB, RDF stores, etc.) superior to a RDBMS for this purpose?

Answer 1

The question of how to store the data depends on how it is going to be used amongst other issues. I'd suggest going with something simple which you understand for now, testing if you have an idea of the likely load you expect. Then in future making improvements as necessary.

In relation to your issue with a trigger based auditing system, since it sounds like you're set on having the work done at the database level I've one suggestion. Use triggers to log changes to a table within the database, then overnight (or however frequently) process the contents of the table and create the audit trail wherever it is being stored and empty the contents of the table in the database. This way you can capture changes at the database level but still fulfil your requirement to store the actual audit trail elsewhere.

Answer 2

I see no reason why a trigger can't reference a different database. However, all changes would fail if that database was unavailable which can be a problem if the audit database is on another server and the conectin is down. But our auditing is through triggers and we have a separate audit database.