Hi,
My application has a series of forms. I want to track the following for each of the data fields in a form:
Time of change, who changed it, old data and the new data
Any suggestions on a solution for this?
In my implementation of the database, the structure of a form is defined as DATA in a table, not by it's columns. For example for a simple form (profile) that has two fields (firstname and lastname), the DB looks like this:
--
"form" table:
ID, NAME
1, profile
--
"form_field" table:
ID, FORM_ID, FORM_FIELD_LABEL, FORM_FIELD_TYPE
1, 1, Firstname, textbox
2, 1, Lastname, textbox
--
I was thinking of introducing an audit table as follows (example assumes that firstname and lastname were changed by user that has user ID "1" in the DB):
"form_field_audit" table:
ID, FORM_FIELD_ID, OLD_DATA, NEW_DATA, CHANGER_USER_ID, TIMESTAMP
1, 1, "John", "James", 1, 2010-10-22 01:00:00
1, 2, "Smith", "Slater", 1, 2010-10-22 01:00:00
--
One requirement I have is that if a different user changes the same field, then the original user should be able to see which fields were changed and the old and the new data.
Any comments on my solution above?
Many thanks for your help in advance.