[AccessD] Temporal database (was: Stored Procedure not producing results)

Quite right, on all counts, Gustav. I stated purposely that I was providing a thumbnail sketch, but I ought to have included these points in said thumbnail. Thanks for the amendments.

Arthur



----- Original Message ----
From: Gustav Brock <Gustav at cactus.dk>
To: accessd at databaseadvisors.com
Sent: Wednesday, November 22, 2006 10:45:32 AM
Subject: Re: [AccessD] Temporal database (was: Stored Procedure not producing results)

Hi Arthur and Shamil

"We need to be able to roll back the database to any given PIT without
actually having to do a restore. We just want to specify a date (Jan 1
2004) and without changing a single line of code, run the reports."
>>> 
> You mean "virtual rollback" of course? IOW a "point in time aware"
view/SP/UDF?

Note that to implement a temporal database in full (I guess we will
loose JC here), not two but _four_ time fields must exist:

<quote>

In the literature, two time lines of interest have been mentioned,
transaction time and valid time. The valid time line represents when a
fact is valid in modelled world (i.e. when it was believed) and the
transaction time line represents when a transaction was performed. A
bitemporal database is a combination of valid time and transaction time
databases where these two time lines are considered to be orthogonal.
(Snodgrass & Ahn 1986)

</quote>

Again, accounting can display the beauty of this: Consider writing an
invoice; you do this, of course, "today", while the invoice date may be
either past, present, or future. Later, selecting on transaction dates
will reveal wether this invoice was created or not; selecting on valid
date will show wether this invoice was issued or not.

You may put it in another perspective: Transaction dates record by
themselves an audit trail which allows you to, at any time, to perform a
dynamic virtual rollback to any previous time, while the valid date will
show you the (approved) state of the data at that time. Specifically,
this allows you to correct previous recordings (say, a wrongly typed
invoice number of a supplier's invoice), or record a future change of a
customer's address and - until he moves - to issue invoices correctly
addressed to his present address.

/gustav