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