I am designing a database and want to have audit information for all the changed value. I want to validate my approach -if there could be any down side to this, I already have gone through existing questions and have tried a few. This link summarizes almost all Database design for audit logging
Following is what I am thinking: A common table, say audit_info which have following columns
-->ID (generated)
-->TableName
-->Timestamp
-->beforeHashedValue -this will have all the info before changes hashed with key
-->afterHashedValue -this will have all the info After changes hashed with key
How will it work:
->Whenever a value change, capture entire row, encrypt it and write to audit table
->Write to audit table in async mode -By async mode I mean, before updating store a image of current data, If transaction successful, feed it to some thread which will do encryption and update table
->Use unique ID as foreign key in primary table, store it as comma separate string for multiple updates
->To retrieve values, Query on table and order by time stamp to get historical values
->decrypt each row to get the timeline data
Pros:
->I thinks this is pretty simple
->Would not impact primary tables
->No cluttering in each table, the main tables just need to store the foreign key to audit row (In current I have been storing audit info with each table and it has become a maintenance nightmare)
->For multiple audits, a comma separate list can be maintained which can used to fetch audit values
Some cons which I can think is:
->encryption and decryption can take time
->What about very large object? for example text, blobs? How encryption will behave?
->If audit is needed in real time, this can result in performance bottleneck!
->Need logic to map it back to domain model based table name
Given that:
My table do not have any BLOB, but at max text say 3k-4k words
There is no need of real time auditing,
Is there any major disadvantage with this approach?
