2

In our application a user can edit/delete certain items on a wall - which are viewable by others on the same team. Now what's the best design approach to solving this issue of history maintenance?

Here are some design ideas that I have in mind but don't know which is a good/better approach to go with:

  1. Create a duplicate schema (or a duplicate table per table) for the current tables. Call a trigger before update for each of them. But I only want it to fire only if the 'text' changes and not other attributes. Don't even know how to go about this
  2. Create a 'history table' - i.e., a global table where an attribute is used to isolate which table/item that history record is for.
  3. Have an asynchronous component that is responsible for updating a special table(s) with the corresponding update - more like a hashmap kind of storage. But I don't know how could this be done with Java/Mysql or is it even worth the effort. Creating asynchronous components are non-trivial

The first of the two approaches would use triggers. But then I don't want the trigger to do anything if just the timestamp or other configuration related fields have gotten updated for a record. Assuming about 50-100 concurrent users, would triggers be a good option? (or is it better to go with a stored procedure approach to do this...not sure).

Intention of such a design: We wish to basically perform some data analysis on the changes/deletions etc., We also wish to check for 'new items added' but that's easy from the timestamp and doesn't affect the history aspect. I'll have to design for each of the above points differently and wanted to know how best to approach the problem - i.e., best way to handle something like that in enterprise applications. I wanted it to run it by the community to get some suggestions/directions/pro-cons for some of the approaches. New/other better approaches are more than welcome!

I've gone throug some posts on SO and they seem to be DB specific but don't necessarily answer my question directly - they tell me the how for the first option but not necessarily whether it is a good way to go about it: Data history tracking best practice and How to track data changes in a database table

PS: I'm not using any ORM, but rather going with Spring JDBC. It was decided an ORM was not worth it...

Community
  • 1
  • 1
PhD
  • 11,202
  • 14
  • 64
  • 112

2 Answers2

1

What you probably is asking is called an audit trail. The best way is to maintain a separate audit trail table for maintaining actions (add/delete/modify).

In some of the applications I have worked earlier, I have used such tables, which are referenced by a foreign key to the original table.

in case of an delete the record in the master table is soft deleted, so that the audit trail reference remains.

A purge mechanism clears (cascade delete) the master and the audit trail based on a pre-decided interval.

I used a combination of app-logic and sql queries. no stored procs.

Ayusman
  • 8,509
  • 21
  • 79
  • 132
  • Does that mean the 'audit trail' table would be like a star schema with a foreign key to every possible table?? Wouldn't this also force the use of transactions too? – PhD Nov 01 '11 at 06:02
  • yes. But the number of tables for which you are maintaining the audit trail should be small. There is no point maintaining the audit trail for all the columns of all the tables of database. And yes transaction will be required. – Ayusman Nov 01 '11 at 06:04
  • Trigger based approach is too DB specific and will always remain out of transaction control. Trigger based approach is OK if you do not have strict requirement for all audit trails to be out of app logic control. It will happen as a DB entity and hence you will have 2 separate components to manage. – Ayusman Nov 02 '11 at 07:45
0

  • Log the records into a duplicate schema for history tables using java. It is easier to maintain java than to maintain triggers.

  • When logging history, it is not necessary to check the differences.

  • The differences should be checked during the validation phase to prevent a save if the record has not been updated.

r0ast3d
  • 2,639
  • 1
  • 14
  • 18
  • Wouldn't that force me to use transactions at some places where it may not even be required? I'll be forced to send to queries back to back?? – PhD Nov 01 '11 at 06:00
  • Not sure if I understood your comment, yes you will need transactional integrity even for your history records, but it need not be in the same online transaction it will be an asynch call to log the history. – r0ast3d Nov 01 '11 at 06:08
  • How would you make an asynch call in a j2ee app? MDBs? – PhD Nov 01 '11 at 07:47