Dear Experts,
We need to design a database which has an audit trail for updates to a
certain set of tables. The two main approaches I've seen for this
include shadow tables (an extra table to track changes to the main
table) and point-in-time architectures (having an "event date" and an
"as known" date for every row). Can someone suggest the pros and cons
of each approach or point me to relevant literature discussing the
best approach?
To provide an example of what we are aiming for, imagine we have a
single table with date, value pairs, e.g., [(1/1/2000, 5.2),
(1/1/2001, 5.3), etc.]. Sometimes we may need to go back and correct
old values, e.g., on 1/1/2008 we may realize that the value on
1/1/2001 really was 4.2 not 5.3. The main use case is we want to get a
time series of most up-to-date date/value pairs. A secondary use case
is getting a time series of what we thought at a certain point in the
past. The shadow table approach would have an extra table to track all
changes while the point-in-time approach would an an event_date and
as_known_date column to the original table.
Thanks,
-Emin
