I need an advise for the Audit Trail of the Help Desk module for my application. Customer wants to keep the the track of each and every changes in the help desk module of application. There are more then 40 fields on the help desk form(more cold be added in future). I have two options for the complete audit trail.
1st: I create a denormlized table for the track of all the fields having four columns for each field i-e new value, old value, updated date, updated by. on the basis of these column i can track all the fields.
2nd: I create a table that keeps the track of each changes i-e
change_id, HD-Ticket_id, updated_date, updated_by
and create another table that keep the track of each changes along with the change_id as forign key in this table i-e id, change_id, field_name, value.
in the 1st option table is very complex but it will give the full log quickly and in the 2nd option tables are very simple but query that will generate the log is seems very complex, we tried a query to generate the log but it take too much time and causing the connection time out almost every time we try to pull data.
Can anyone help me to find the batter way for Audit Trail.
First option is widely used, you have to also add more columns like PageName,KeyId(PK of that table)