1 of 2

There is no clear-cut answer to this I'm afraid. It depends on what you want to do, how strict your requirements are and what tools you have available or want to use. To give an extreme example, banks don't just write a log on a file somewhere, but actually print that log line onto real paper...

What I did for one of my applications was your suggestion #3, where the live-data sits in optimized-for-access tables residing entirely in memory while the data for 'undeletes'/'rollbacks' is stored on traditional tables that have just an index on the entry-id.

But that's just me, because I have a lot of searches per second, but only few changes and rollbacks. Your requirements might differ. Maybe your application is more GoogleDocs-like where you expect changes to happen often, and even simultaneously, but don't need that fast lookups/searches. Then it might be better to store the base-document and keep track of change-deltas that happen over time.

Others again, that are already using an enterprise-DBMS like Oracle, might feel much more comfortable 'outsourcing' this to the database. Be it by using triggers, or specialized versioning Add-Ons available from the DBMS vendor directly. This could also be the proper choice if the data versioning and traceability is vital to the data and you want to offload the responsibilty of doing it properly to someone else.

And so on...

So you see, it really depends on your requirements