-2

I want to add an audit trail for our system so when any Add/Delete/Update operation happen i will log it, with the following info:-

  1. the CRUD operation type. is it add, delete or update.

  2. the record ID which have been modified.

  3. Date and time.

Now i found two approaches to follow; either to have a single audit trail table with the following fields:-

  • ID .such as 123445.
  • CRUD_description. Such as Delete
  • Record_ID. Such as Qaeop12771
  • Date. Such as 1june2O13

Or to have two tables one for a lookup table for the CRUD operation such as

  • CRUD_ID. such as 3.
  • CRUD_Description.such as Delete.

And then the Audit trial will reference the above table:-

  • ID. such as 123445.
  • CRUD_ID (this will be a foreign key to the CRUD table) such as 3.
  • Record_ID. Such as Qaeop12771
  • Date. Such as 1june2O13

So which approach is better ??

Second question If i will follow the second approach . Then is it preferred to use the CRUD_ID inside my code for example if the oprration is delete i might have my code look like:-

Inset into audit_trail (ID, CRUD_ID, Record_ID, Date) values ( 123445, 3,12771,1june2O13) //CRUID 3 represents delete operation.

Best Regards

YasirA
  • 9,531
  • 2
  • 40
  • 61
John John
  • 1
  • 72
  • 238
  • 501
  • What is your RDBMS? Have you considered any available (if there are) auditing features in your RDBMS? – YasirA Jun 01 '13 at 02:08
  • i will be using Sql server 2OO8. but i can not log the chnages using the database as the users will be defined on the application level. – John John Jun 01 '13 at 13:00
  • See also http://stackoverflow.com/questions/23770/good-strategy-for-leaving-an-audit-trail-change-history-for-db-applications – Pixelstix Sep 07 '16 at 15:06

2 Answers2

15

From the viewpoint of database design (ignoring the database features and the application architecture ) I will prefer having a table for audit trail (change history) having changes per Entity and per field by implementing a flat table called Trail_History with no foreign key to any table, columns will be:

  1. UserCode: Application user unique identifier representing who made the change. (mandatory)
  2. TransactionCode: Any CRUD operation will need to have a unique transaction code (like GUID) (mandatory)
  3. ChangeDate: Transaction date. (mandatory)
  4. EntityName: Entity (table) that is being manipulated.(mandatory)
  5. ObjectId: Entity that is being manipulated primary key.
  6. FieldName: Entity field name.
  7. OldValue: Entity field old value.
  8. NewValue: Entity field new value
  9. OperationType: CRUD operation discriminator. (mandatory)

Having this approach
Any entity (table) could be traced
Reports will be readable
Only changes will be logged.
Transaction code will be the key point to detect changes by a single action and second question will be answered.

Hope be helpful.

Mohsen Heydari
  • 7,256
  • 4
  • 31
  • 46
0

Really the two approaches are prety much the same.

The seond one will be slighty more efficient but less flexible in the sense that if you need a new audit action you need to insert a new record in the type table.

The question how much information you need to audit is more interesting. Because say three users updated the record after eachother you still do not know who changed what in your design.

Pleun
  • 8,856
  • 2
  • 30
  • 50
  • Thanks for your answer, but why the second approach will be more efficient but less flexible. Regarding the concurrent update , i should have an insert statement not an update -i have updated my original question- . So in this case all the concurrent update, insert or delete will be recorded in the audit .. am i right? – John John Jun 01 '13 at 13:04