2

I have an employee and a corresponding employee history table.

Both the tables have same structure. History table is used to track the historical changes made to the employee over a period of time.

Now, I need to add an undo function to the changes made to the employee.

e.g. Employees title is changed on 1st August. Now, This will update the employees title in Employee table and insert an corresponding history record in employee_history table.

Now, I need to undo this change. Employee edit page will have a list of changes made to employee datewise with an undo button beside it.

Clicking on undo should revert changes in Employee table to previous value. Also I think the record in history table which says title is changed, should also be removed.

Also when I revert tghe changes to employee table i.e. revert title to previous title, this will fire an insert to history table, which I dont want.

I am not sure what is the best possible way to do this.

Any suggestions will be helpful.

Andrew Thompson
  • 168,117
  • 40
  • 217
  • 433
ajm
  • 12,863
  • 58
  • 163
  • 234
  • 5
    I know you're looking for something concrete, but consider using the [Command Pattern](http://en.wikipedia.org/wiki/Command_pattern) in your design - one of the advantages encapsulating changes like it does includes multi-level undo. – Rob I Aug 01 '12 at 13:42
  • 2
    It depends strongly on the ui framework – kostja Aug 01 '12 at 13:43
  • @kostja. Its a spring MVC based web application. – ajm Aug 01 '12 at 13:44
  • possible duplicate of [How to design undo & redo in text editor?](http://stackoverflow.com/questions/3583901/how-to-design-undo-redo-in-text-editor) – slayton Aug 01 '12 at 13:44
  • @slayton. Its not more about designing undo but how to manage the updates to history tables. – ajm Aug 01 '12 at 13:46

3 Answers3

2

In case you want to implement a "persistent" undo - one that would survive an application restart/session timeout, you should consider extending your DB schema width timestamp fields and either delete the last entry or replace it with an appropriate earlier entry.

A "light" version would be using a stack to store last interactions, including the original and the new value. You could persist the stack on session invalidation of course to combine both approaches. This seems to be what you are actually doing.

You could extend this solution by creating and storing or exporting SQL migration scripts for each change, recording the change and, if possible, the opposite action. So you could even transfer the scripts between application instances and environments and would have a complete "replayability" of your DB states.

tl;dr - it looks like you have already implemented a good solution

kostja
  • 60,521
  • 48
  • 179
  • 224
0

I would suggest using a flag telling the trigger/history logic to keep off while you have your undo running and not writing history data.

Normally this would be done by serializer-class feeding from your history table and restoring employee data and later cleaning up history-entries/unlocking history again.

Najzero
  • 3,164
  • 18
  • 18
0

You could maybe use the rollback feature of the transaction.

ricardoespsanto
  • 1,000
  • 10
  • 34