Re: Surrogate Keys: an Implementation Issue

Paul Mansour wrote:
> ...
>
> The second is perhaps more profound. Consider a rollback database, or a
> database that must provide a complete audit trail of every change. For
> example, the database must provide the answer to "who changed this SS
> number from X to Y, and when did they change it? As far as I can tell,
> if there is no way to answer this without an immutable identifier. ( I
> suppose you could design the DB to handle specific cases, but I'm
> interested in DBMS with native rollback and audit trail support.)
>
> Again, let me restate that I'm talking about the internals of the DBMS,
> not how to design a database.
>

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.

p Received on Wed Jul 19 2006 - 19:01:27 CEST