100% agree with - based on experience (with my own databases and others):
- Same database for auditing
- Triggers are evil - conceptually awesome, but not always so practical in the real world
As far as “what needs an audit trail”:
I have gradually been pulled over the years - through hard-earned experience - to the idea of auditing “almost everything”. The exception is not so much “what do I think we don’t really need” to “what tables are just so huge as to be impractical to audit” and also, sometimes, “are there ever any real changes to audit”. Some specifics:
- Any small table (let’s say 1,000,000 records or fewer) with occasional changes (up to 10,000 changes a day?) - just do it. That’s most of the tables we will have except Posts, Comments, Votes.
- Any medium-sized table (let’s say up to 100,000,000 records) with occasional changes (10,000 to 1,000,000 changes a day) where the changes are useful - just do it. It will affect performance (but not a lot if done well) and will be far easier than any kind of “send it off someplace else (NoSQL or a text log file or whatever) to retrieve it when we need it”. Just keep it all in SQL, use a big enough server, make sure your indexes are configured appropriately and done. This should cover Posts and Comments for quite a while.
- Any large table (100,000,000 on up) with occasional changes - think carefully about what will work best. This is where it gets big enough for performance to be a concern, and then you have to balance performance vs. utility.
- Any large table with hardly any changes - audit changes only. That is a little different from the usual “audit all create, update, delete” because it will make a big difference in performance and storage to NOT create an audit when a record is added. I have one system like that - most of the small to medium tables are audited (should be more, but didn’t start with the concept at the beginning…always harder to add it later) but deliberately not auditing the two biggest tables (energy data from mostly automated sources) which have very few updates.
- Any small table with frequent changes - do not audit unless there is a business need. I am not talking about “Users like to change their profiles frequently” - in the grand scheme, those changes are “occasional” and important. I mean if we have tables containing cached summary information or other primarily derived information that changes frequently. There is no need to audit (since the information can be recreated from the audit trails of the other tables) and there is great benefit to not auditing in terms of storage & performance.
In our current proposed schema (including variations for reference tables vs. lots of little fields, etc. - that makes little overall difference), the result is that I would audit pretty much everything, including User, Community, Post, Comment, UserCommunity, Tag, etc., including Instance, Community, User and Post specific details (all the reference tables, linked tables, etc.) except:
- Votes - Each vote is basically Up or Down and rarely changed. If a User changes a Vote, just delete (flag, not true Delete) and add a new Vote - avoids having an Audit table that is nearly a duplicate of the large (in terms of records, not fields) Vote table.
There may be other exceptions, but not that I can think of at the moment.