How to keep track of user changes to objects in a web application?

I'm needing help to come up with a clever way of keeping track of user changes in a web application.

Lets say we have this scenarion:

1. User X logs in and updates a few fields in their profile, lets say the email address.
2. This causes the user to be flagged as updated and it should be checked by an admin
3. In the admin UI we want to highlight the modified field(s)

The question then becomes, what is the best approach to keeping track of these changes? The admin might not check the user for days or even weeks. I don't really have a need for keeping the original values (at this point at least), I just want to be able to determine what fields were changed. What would be the best way to keep track of this in the database?

The application is written in Java / JSF wit开发者_开发问答h Hibernate and MySQL for data access and storage.

Thanks!

---

Based on your use case, I'd go for something like this:

Create a "log" table with the following structure:

Username|Fieldname        |Processed
Pamar   |Profile.Email    |No
Pamar   |Homepage.Blurb   |No

Set up triggers on the fields you want to keep track of. Whenever a "logged" field is modified the trigger will create a record which points to the User and the field name.

The tagging part of the application will allow the admin to query the logging table (selecting only the "non-processed" records) and process one or more field at a time. You don't give many details on the tagging/indexing process, but this table should be enough to correctly identify the changed fields (and you can query these directly to access the current value, which is all you need). After having processed (indexed) a given update you can set its "Processed" flag to "Yes" (or delete it, your choice).

Optionally you can have a timestamp field in case you want, for example, to process your updates in the same order they were done by the users.

If you even need to keep a "previous version" you just add a text field to the current structure and update the trigger(s) so that they save the old value in it.

---

Have a look at Hibernate Envers. -- It provides Auditing and Versioning, but my this is too fat for your usecase.

---

You could create a table in the DB for changes...

1. Before writing any changes to the db, run a select query to retrieve existing data and compare the two (to find changes)
2. changed fields are written to a table (id, timestamp, user_id, field, old_value, new_value)
3. something happens here... either an email alert, or simply whenever the admin logs in, it outputs the entries of this table where the admin can chose to approve/ignore them (thus deleting them from the changes table).

If you don't need to keep the old values, then simply omit the old_value column in the changes table.