Re: Surrogate Keys: an Implementation Issue

From: Paul Mansour <>
Date: 19 Jul 2006 11:39:36 -0700
Message-ID: <>

paul c wrote:

> Just an observation - I've seen audit trails for single apps that were
> 'bigger' in space and time to create and view than the recovery logs
> built in to the system where the developers failed to advise naive users
> of the ongoing physical cost of willy-nilly auditing with separate audit
> tables. In fact, the storage requirements of one audit trail I saw were
> bigger than the application. One feature that would be high on my wish
> list would be the ability to expose system logs at an application level
> and further to preserve selected portions of them for long periods of
> time, eg., beyond checkpoints in some kind of system-supplied view.
> (Such logs frequently contain timestamps which can be seen as candidate
> keys.)
> I can see no logical reason why some massive trails couldn't in fact be
> a separate physical database, perhaps updated asynchronously by a
> different cpu. Then it would be more clear to some users what the real
> cost of their audit requirement is.

If you start with the premise that "The Database is not the database - the log is the database, and the database is just an optimized access path to the most recent version of the log," it soon becomes clear that if you can get "an optimized access path to the most recent version of the log" you can easily get a path to any version of the log, which is to say, a snap shot of the db at any point in time. This in turn implies a complete audit trail. Granted this takes space, and is probably practical only for certain apps (certainly many corporate/business apps) To manage the explosion of data, you can just cut the log from time to time. This leaves in its wake a time-series of logs each of which will produce the database for any point in time over a certain interval. As stuff recedes in time, just archive it off somewhere. Received on Wed Jul 19 2006 - 20:39:36 CEST

Original text of this message