Polymorphism as a tool for updating and versioning of software
Let's say that I have a record in the database and that both admin and normal users can do updates.
Can anyone suggest a good approach/architecture how to version control every change in this table so it's possible to rollback a record to a previous revision. This solution is good when you are largely interested in the current values and only occasionally in the history.
Personally I'd recommend not deleting anything (defer this to a specific housekeeping activity) and have an "action type" column to specify whether it is insert/update/delete.
For a delete you copy the row as normal, but put "delete" in the action type column.
Yes it'll grow big, easily millions of rows, but you can easily track the value for since the journaling started or the old journal rows got last purged, and who made the last change.In Oracle everything you need is generated automatically as SQL code, all you have to do is to compile/run it; and it comes with a basic CRUD application (actually only "R") to inspect it.You can perform auditing on a SQL table via SQL triggers.I woyuld recommend that you actually insert the new data, not the previous, so the history table has all of the data.Although it stores redyundent data, it eliminates the special cases required to deal with searching in both tables when historical data is required.