Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

Background

A discussion that has come up at work recently is how we handle audit logging and the recording of events. We are integrating with a 3rd party app so triggers are a no no from the off so we are handling it in code. We've written a number of prototype components for handling it but nothing feels right as yet. The main issue being we want to create Facebook style time lines for the users to see what action have happened recently but these don't seem to fit well with how we record audits.

My question is how would be best to handle this type of scenario?

  • Should we tailor the audit log tables to fit the requirements of the front end?
  • Should we have separate tables to handle the "Actions" and have the events and auditing separate
  • Should we look to a more message based architecture so this will be more like an Event sourcing type component?

Input from somebody who has done this type of system would be much appreciated.

share|improve this question
    
You are integrating it with a 3rd party app. Understood. But why does that rule out triggers? –  Monster Truck Mar 20 '13 at 13:28
    
We've found that the COM API we deal with does funny things to the database whilst it's working and putting triggers on tables has proved to freak it out for some reason. –  Andyroo Mar 20 '13 at 14:11

2 Answers 2

up vote 1 down vote accepted

Should we tailor the audit log tables to fit the requirements of the front end?

No. Generally this is better when you tailor the audit log tables to fit the requirements of auditors and then worry about the front-end later.

Should we have separate tables to handle the "Actions" and have the events and auditing separate

Yes. This gives you more control over events, and adding new ones as you need.

Should we look to a more message based architecture so this will be more like an Event sourcing type component?

I prefer to keep things simple and merely store in the db. However a message-based architecture may work well if you are integrating into a multi-tier application and your code is somewhere in the middleware. But here it depends on what you need and we'd need to talk more specific requirements than we have here.

share|improve this answer
    
Thanks Chris the top two responses are great and have helped clarify what I was thinking. The 3rd one yes we are in a tiered app with this being middleware. So we are talking across multiple databases and with a legacy COM API which was why I though a more message based approach may be suitable. I.e CreateContact which adds not only to the legacy COM db structure but to our new database that has added functionality. but I think spitting audits and actions could be the answer either way. –  Andyroo Mar 20 '13 at 14:07

As long as you are aware that the actions outside the application will not be logged (I personally would not try to do auditing except through triggers but third part products are usually a separate problem because you have a limited ability to change the db itself.), the approach is OK.

What is generally needed in auditing though is an approach where you store in separate tables for each table audited (to avoid the audit table causing locks). Next you store what you will need to know to revert the data and find the culprit if the change is a problem as well as what an auditor will want to know.

So we store both the old and new values, we store the id of the person making teh change and the name of application that sent in the change (We have multiple applications hitting the same database) and we also store the datetime of the change (very critical in finding all th3 records that need to be reverted from a bad production push that happened on 02/01/2013 at midnight).

Becasue our changes are often in groups and not just one record at a time, each audit table is also split up into a table that stores the info about the insert/update or delete (datetime, person, application, type of change) and one that stores the specific records changed or inserted or deleted. That way if you find one bad record change, you can see if there were possibly other records changed by the same person or application at the same time.

While you are designing teh system, go ahead and write the sql you need to revert data to it's old form. Even if you don't intend to use auditing for that, there is about a 100% chance you wil use it that way once the data is there. Sionce that usually happens under rpessure, ti s better to have a script or stored proc ready for reverting data.

share|improve this answer
    
Thanks that's great insight and much appreciated. I had started to look at doing a system in a similar manner but the dev who does most our DBA work put the block on it from the out set saying it was storing to much un-necessary data and doubling the amount of tables required. Do you have a replica table for the audit table with additional fields or do you have just before after/values with property names stored? With the other details you mentioned as well –  Andyroo Mar 21 '13 at 10:16
    
We do before and after fields and have the same structure for each audited table. That way we can also create scripts to automatically create an audit table when a table is created adn we don't have to adjust it every time the table is changed. I have to say the person doing your dba work is unknowledgable if his worry is about creating too many tables. He should be worried more about performance and blocking. As far as the number of fields, he should be thinking about what the data will be used for. Those are not unnneeded fields, they are necessary to track down where bad data came from. –  HLGEM Mar 21 '13 at 13:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.