0

I have few tables in which there could be an update on some of the fields.

I would like to keep a history of changes of different fields. Different fields however have different types, however I wouldn't want to create many different columns in my "auditing" table.

So I though should I use serialization (I used it often in PHP), I'm not sure however how efficient it would be in Java/MySQL (I'm not using any data frameworks etc). If serialization, then to what? XML/JSON?

I thought two tables:

  • AuditEntityProperty (Id (PK), EntityId (FK), PropertyName, From, To, DateTime, Username)
  • AuditEntity (EntityId (PK), EntityName)

From/To are my current problems that I'm not sure how to address. I'm leaning towards XML and using TEXT as datatype for both. Ideas?

Luke
  • 1,872
  • 20
  • 31

1 Answers1

1

I would suggest XML.

My table structure:

Audit_id, operation, preImage, postImage, userId, datetime, entityName, timestamp

Then I would map this to say an AuditBean, I would have used JPA, hibernate if I would have an option of, Use Spring AOP to update the audit table when table structure is updated.

One table insertion which would have preImage and postImage.

For XML generation I think XStream is one of the most easy use.

All the table which needs to be Audit, I would have marked them as XMLConvertable (So that I can easily get Class Alias, and other alias fields for XStream) and Auditable (So that my AOP code can extract the preImage from say database)

mprabhat
  • 20,107
  • 7
  • 46
  • 63