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

JWColby jwcolby at colbyconsulting.com
Wed Nov 22 10:46:33 CST 2006


LOL, you may or may not lose me.  I can see applications where this would be
invaluable.  I just do not see where an entire database from state tables to
color tables needs to be temporal.

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Wednesday, November 22, 2006 10:46 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Temporal database (was: Stored Procedurenot 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

>>> artful at rogers.com 22-11-2006 15:10:35 >>>
My personal fave in this category is the Kimball Group. Specifically, for MS
SQL, I recommend "The Microsoft Data Warehouse Toolkit" by Joy Mundy and
Warren Thornthwaite. There is extensive discussion therein about SCDs
(slowly changing dimensions).

I will check out your sources, too.

A.

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

Hi Arthur

I don't recall you have written about PITA here, but what you describe is
generally known as a temporal database.
This is well-known and has been dealt with by some great capacities in the
database world, Michael Boehlen, Christian Jensen, Richard Snodgrass and
Andreas Steiner.

http://www.cs.arizona.edu/people/rts/timecenter/timecenter.html 

A brief explanation and introduction can be found here:

http://www.timeconsult.com/TemporalData/TemporalData.html 

Note the link to TimeDB, a Java implementing of "A Bitemporal Relational
DBMS" for Oracle 10g

Also, look up my previous post(ings) on these:

http://databaseadvisors.com/pipermail/accessd/2005-May/034503.html
http://databaseadvisors.com/pipermail/accessd/2005-May/034455.html 

This is a very exciting area. For some applications it won't even require
that much more data storage (accounting). 
A simplified approach is a write-only database (which means write and read
but neither delete nor update) which both Caché and MySQL offers.

Now, wouldn't it be nice if only TSQL2 could be implemented in Access:

http://www.cs.arizona.edu/people/rts/tsql2.html 

/gustav
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list