I need to keep track of the
for most of my entities. Pretty standard.
Do you think it's better to add these columns to every table...or to just have a CreatedAuditEntryId and ModifiedAuditEntryId FKs on the tables you want audited that point to existing separate auditing tables which are used to keep track of all changes.
AuditEntry looks like:
There's the obvious performance impact of having to do two joins to get the Created and Modified information...but the benefit is that I'm not maintaining state in two difference places, which is like cardinal rule number one in my design book.
UPDATE:
Just to be clear, the AuditEntry table contains every modification to every table, regardless. The question here is whether to utilize that table for Created and Modified information via FKs...or to just add the four columns above to each table I want the information for, so as to avoid the joins.
My preference would be for a separate audit table with a FK relationship otherwise you can only see the last entry and that's not much of an audit...
What are you using to update these entries? If UserID can be implied from the SQL user it can all be done in triggers.
I'm looking at something similar in MVC and about to implement a filter to record system-wide controller actions in a who, what when and to-what type table.
EDIT: in view of the updated question where it is clear that the Audits table will be existing anyway, I'd have to choose linking to the audit table. The thought of having non-coherent audit data is just too scary! Unless the app is totally crippled by the relational design, keep it normalized!