After a fair amount of work estimating a new component for our current project, I finally nailed down, amongst other things, the specifications of our audit trail system. Following the XP concept of "the simplest thing that could work", I reduced the audit trail to two tables: audit type and audit item. The audit_item contained the following fields:
I was pretty happy with that until a coworker suggested that we use this to determine when a new item was added to inventory as this will be stored in the audit table. That's when it dawned on me that the one thing I forgot to include in the table is the item being audited :) (of course, this makes the system much simpler as inventory simply becomes a matter of reporting that they have 23,946 products in stock, instead of that pesky "product by product" hassle)
As a side note, the XP ideal of "simplest thing" has been a great antidote to a problem I have long dealt with: the assumption that if something was too easy, I was doing it wrong.
If you find yourself a lot of date-dependent data analysis queries against the audit table, you might find that it works better to adopt a scheme whereby a value for an item has an "effectivity range". That is, explicity record both start and stop datetimes for the validity of a value. (Any item that's currently effective would have a NULL stop time.) This makes it a lot easier to answer "what was the value for item on January 10th" questions.
