@qyloxe Interesting : ) Thank you!
So have you done UNDO / REDO before with a relational database ? Did it work well for you ?
yes, many times, BUT it of course depends on the definition of undo
The most “classic” undo/redo is in banking systems. There is a concept of transaction log, every event has a date, a money transfer, a source, destination and if you want to make a undo, then you just replay needed transaction (banking not db) with opposite value sign.
There were apps where in some domains the undo were beneficial and where this behaviour was implemented by record version history. It works very well when you do not need to undo many inter-table connections. As a matter of fact, I implement very, very often the record version history and operations log for almost every table. It is beneficial in long run.
And there are some tables, where it is possible to model the reality in such a way, where the actions and states of the records in those tables mimics the finite state machines. I do prefer and strongly recommend using this technique in relational tables. Imagine that you have a table “Users”. You can add a column “status” with such possible values: “N” - new, “P” - in process of registration, “C” - registration cancelled, “Z” - user active, “B” user blocked, “U” - user deleted. There are possible transitions (just as example):
If you have a table with versions of this table, than every update to users table would insert last version (automatically by trigger) to your version table. You have full log, full operations register, full info about who did what and when and… undo/redo possibility.
FSM (finite state machines) gives you guarantee, that your system, as a whole, will always be in determined and correct state.
Oh, this is my main modelling/architecture concept which works in most situations. It is of course only a tip of the iceberg, but I’m sure it is somewhat obvious, because all this is described and known from many years.