Undo with database

Question

I'm trying to code an "undo" or "rollback" type scenario. Part of me thinks I am over complicating this and it must have been solved before.

Background: I have a Product (eg A book) the product has 20 fields. A Product has a Media Type (PDF, Hardcopy, EPUB) and each Media Type has multiple pricing (price, location ie Europe, Asia, America).

Looking at previous questions, the memento pattern seems to be the closest but I'm not sure how to do the multi levels.

Eg, Change 1 - Create the Product "Coding for Dummies", it is available in Hardcopy and is available for $100 in Europe and $75 in Asia

Change 2 - Change the Europe Hardcopy price to $90. Add a PDF, it costs $90 for Europe and $75 for asia

Change 3 - Delete the Hardcopy record

Change 3 was a mistake, I'd like to rollback to Change 2.

Looking at previous questions, the Memento pattern seems to be be the closest, but I would duplicate an awful lot of data. ie in Change 2 & 3, you are repeating the Product, Media and Pricing.

The Dataset I am using has almost 1,000,000 products. (The above example is simplified) In reality, a Product has about 50 fields, the Media Type has about 15 fields

Is there a better way?

Answer 1

I'm not quite sure what you mean by multiple levels and why the database is mentioned in your post, but generally command or memento are both good options to get undo and redo in an object -oriented program. Both ways you should be fine.

What do you mean by duplicating data? You just remember the changes in the state of the products - there is no other way if you'd like to revert them later...

Answer 2

implement date fileds on your tables.

in this way you would be entering the fact that the price changed from 21 to 30 on 01-MAR-2011 or something, not just whacking the old values. then you will be able to query not just the most recent change, but you could revert to old pricing just by manipulating the dates.