I've come across many database designs getting following audit posts on all of the tables...
- Produced By
- Create DateTime
- Up-to-date By
- Upldated DateTime
In one perspective I see tables in the following view...
- Entity Tables:
- Good candidate for Audit posts)
- Reference Tables:
- Audit posts might needed. In certain situation last update details are not whatsoever needed because record is not going to be modified.)
- Reference Data Tables
- Like Country Names, Entity Condition etc... Audit posts might not needed since these details are produced only throughout system installation time, and not going to be transformed.
I've come across many designers blindly invest audit posts to any or all tables, is practice good, if so what is the main reason...
Among the finest to understand because in my experience it appears irrational. It is not easy that i can understand why will they design their db by doing this? That does not mean they're wrong or right, would like to be aware of WHY?
You may also suggest me, if there's an alternate auditing patter or solution available...
Thanks and Regards
Many programs are developed with a couple OOP language by which there's generally a category like BusinessObject that consists of what's perceived generally useful information like such auditing fields. Not every subclassing organizations may require it, but it is there when they do. Because the overhead from the db is small , the probabilities the client may request another odd statistic in line with the audit fields it's easier to ask them to around than not have them whatsoever. If something signifies a static listing of information for example country names I generally wouldn't place it within the db whatsoever - enumerated data type are produced only for such reasons.
Data auditing is really a needed internal control for a lot of business systems (see Sarbanes Oxley for explanations why). It should be in the database level to make sure that changes are taken especially unauthorized ones.
Despite research tables an unauthorized change could wreak havok in your body and therefore you should know who made the modification so when. Just when was particularly important since it helps the dbas understand how long ago to seize a backup to revive information accidentally or maliciously transformed.
We love to to consider all of our workers are reliable, quite a few the robberies of private data and also the malicious changes to eliminate company data originate from internal sources (for this reason it's harmful to possess many disgruntled employees) along with many of the fraud. Yet most developers appear to consider they just pretect against outdoors risks.
Obviously you're still going to possess a couple of individuals who could make unauthorized changes, you cannot prevent system admins from carrying this out. However with auditing a minimum of you are able to limit the opportunity of data damage (and become especially careful when employing dbas and permit nobody else admin privileges in your database servers).
These posts are for the advantage of the DBA and also the database designers. They simply give a quick mechanism to reply to questions like "When did this record last change?" "who transformed it?" They aren't robust enough or fine-grained enough to fulfill compliance with SOX, HIPAA or whatever.
It's just simpler to possess these posts on every table. All data can alter, so it's helpful to understand when changes happened, particularly if that data is not designed to change. You'll be able to automate the entire process of adding them, using the data dictionary to create scripts.
It's good practice of these posts to become populated individually from the application, by triggers or some similar mechanism. These posts are metadata, the applying should not really be familiar with them.
Depending on the full-blown audit trail to supply this functionality is generally no option. Audit data that is collected for compliance reasons normally has restricted access, and even might be saved inside a separate location.