0

I need to keep track of the

  • CreatedByUserId
  • ModifiedByUserId
  • CreatedDateTime
  • ModifiedDateTime

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:

  • Id
  • UserId
  • DateTime

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.

Jeff
  • 35,755
  • 15
  • 108
  • 220

2 Answers2

1

"Better" depends on what your needs are.

If all you need is THE LAST modification event, then it's better (resourcewise) to add the audit columns to the table. An update will then only have to touch that one row.

If on the other hand you need to have an audit record for every time someone touches a record, then you've got no choice but to have a separate audit table.

mjl
  • 197
  • 3
  • Ah, so it's more a performance question: "Do I use an existing table and do (possibly costly) joins or do I keep a copy of the information de-normalised with the original data". In that case -- unless you're experiencing performance problems -- do the joins and let the database worry about making things fast. If you always want the audit data to appear, I'd even create a view for that. Some database have facilities to make complicated views faster (Oracle has "materialised views" for example). – mjl Jun 30 '11 at 10:53
1

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!

Andiih
  • 12,285
  • 10
  • 57
  • 88
  • See updated question regarding audit trail. Full audit information will exist regardless...I'm just wondering if the joins are ok really. UserId can't be implied from SQL unfortunately, so I'm handling it in EF upon saving changes. – Jeff Jun 29 '11 at 16:07