Adrian Klaver wrote:
I faced a similar problem where I was trying to keep track of changes
to a
FoxPro database that I only had indirect access to. My solution
followed
your proposal to a degree. I imported the new data on a daily basis to
holding tables. I then ran a series of functions to compare the data
in the
holding tables to the data in my 'real' tables. The differences
(added,deleted,changed) were written to audit tables with a timestamp.
The
'real' tables where TRUNCATED and the new data transferred into them
and then
the holding tables were cleaned out. This way my 'real' tables only
contained the minimum data necessary. The audit tables grew but where
not
queried as much as the 'real' tables so the apparent speed of the
lookup
process stayed relatively stable.
I do something similar, but because I do not require precise update
timestamps on each row, my setup has the following wrinkle: Instead of
the audit tables having a timestamp column, I have a separate
updateSessions table, with start and end timestamps. My audit tables
then just have a foreign key into this sessions table. A minor
advantage of this is that the session ID (possibly) takes up less space
than a full timestamp. A more important advantage, from my point of
view, is that the session table has columns for who is running the
update, the filename on which the update is based, an MD5 digest of the
update, etc. My update scripts fill these in, as well as a general
comment string that they take as a parameter.
- John D. Burger
MITRE
