I’ve
normally gone for the history table route, normally implementing triggers on
Update, deletes, and create on the table I want to track. These write to a
second table which captures date time, username, the action and in cases where
we actually care, the old data.
Though I
normally try and keep this to an absolute minimum, using my current contract as
an example, some parts of the business process are naturally self ‘journaled’,
for example tracking an order through. Reason being as the order goes through
each stage, the previous stages are effectively locked down and become immutable.
You may ask
how an order can be amended, as long as the order hasn’t been queued for
picking it can amended by effectively canceling the order then creating a new
one, the old order is then linked to the new order for tracking. As the order
system is pretty much in 5NF the order system is broken down into lots of small
tables, once inserted you can’t update or delete. From the customer clicking ‘order’
the process is pretty much automated from the payment processors to the distribution
center which has auto pickers, auto packers which land the package on a
conveyer belt before going into the awaiting lorry the very idea of being able
to amend already recorded data is pretty much a WTF.
Where we do
journal, we use option 1 (interactions with customers and customer services), occasionally
option 2 for things like product price changes and other state changes (we don’t
care about the performance impact, as this data is sucked out and shoved into a
caching system) and sometimes we allow data to transient up to a point (say
creating a promotion) with no tracking, but once published and approved it becomes
immutable, but can be ‘cloned’ and the old one canceled).
Oh and one I
forgot, similar to price changes, say a customer name changes, the account data
which holds the account number and other key account information is divorced
from the personal details of the account holder(s), this table (well tables as
account holder names, contact details and addresses are all separated out) are
naturally built for history to track telephone number, name changes.
So, IMHO
there is no such thing as a one size fits all approach to tracking, sometimes
option1, sometimes option 2 and sometimes just don’t allow changes! As long as
your tracking mirrors the actual business process you can’t go wrong.
Out of interest,
has anyone read Temporal Data and the Relational Model? Was quite an
interesting read, of course can’t be done with current SQL implementation. <o:p></o:p>