The temporal sql / time travel thread brought to mind a feature for an
application I'm developing which I'd like to implement but am unsure of an
appropriate approach. Each record in a major table (ie. order, inventory
item, customer) has a user foreign key and a modification date. I'd like to
also keep a versioned history of the table. The main difference over the
solutions given earlier in the temporal sql / time travel thread is that I'd
like it to be a limited history governed either by a fixed number of steps
back or more preferably by some limited number of days from the present. The
main purpose of these historical logs is not for history records since other
tables are designed to store history where it is needed explicitly. Instead
with the application I'm developing each field of the database is updated as
soon as focus leaves the field the user was working on (and only if changes
were made to the field). Since changes are instantaneous from the user's
perspective and there is no save/cancel, I'd like to be able to give some
room to undo/redo and review changes which have been made (ie. in the
previous two weeks perhaps). I picture the ability to turn on a history view
window for a given object's view, then select a historical entry in the
window (ie. presented as a columned list of date/time, user and maybe a
presentable name of the field which was changed) which changes the displayed
values of all modified fields from the selected history line up to the
present. This may be made more visible by having the modified fields change
background colour to red or something similar. The user could then
optionally choose to explicitly persist these changes.
Given that apart from the initial creation of a record, each update will be
of a single field, I also wonder if there isn't a more efficient way to
store the changes. One possibility, I've considered is leaving each
historical record 'open' as long as some limited time span has not elapsed
(ie. 15 minutes since first update) and as long as the updates are
non-overlapping. In this way lots of consecutive changes could be written to
the same record. Multi-user, concurrent editing would be problematic to
reconstruct accurately in this fashion, however. Perhaps a further
simplification of this problem could be to store the value of each modified
record at the end of each day. In effect this would be a small scale backup.
However, both of this possibilities make the live undo/redo editing
capabilities an impossibility but preserve the broader review capability.
Finally, assuming that the original table cannot have any NULL values would
it save any space by writing only the value of the fields to be updated into
the otherwise NULL record of the historical table (ie. therefore a NULL
value in the historical table means the field was not changed).
I think the problem is that two different methods are needed. When
immediately making changes it is useful to undo/redo every field change in
sequence. However, coming back later it is more useful (in general) to see a
higher level overview of the changes.
I suppose it might work to do updates as previously described (select
current record into historical table, then apply current update or using a
trigger - which is better?). Later some other process could perform the
winnowing out of records which are too old and perhaps collapse concurrent,
non-overlapping changes into a single record.
Any other thoughts on this?
Patrick.
