Sunday, August 9, 2009

Database Audit Trail

Its been a great experience for me to design a whole ERD of an ERP from the scratch. As a Developer I have found many new ideas and techniques for designing a proper database design. These days I am working with my senior members on Leaving an Audit Trail in the Database.

By leaving an Audit Trail in the database, one can keep track of all the activities (in the form of operations) going on the database. All this is concerned with the security of database. I am new to this important feature of Database and in a brainstorming session comes up with numerous ways to design it.

There are two most common ways to achieve this task, both have their pros and cons.

  • One is to maintain a single table in the database that stores all the actions within a database.
  • Another is to create a an Audit table for every table in the database.
Since our application consist of numerous tables having bulk of data load, it will be a good practice to have a separate table for every table. It can be possible to maintain a single table for all database tables but searching on a single flat table will be quite time consuming.

(More to Add)