Bitemporal transaction time and value time

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Bitemporal transaction time and value time

koenjanssens
Dear Lars,

We are still having difficulties in understanding your implementation of the bitemporal model.
In every article we read (including “Developing Time-Oriented Database Applications in SQL” from Richard T. Snodgrass), a bitemporal table always contains four datetime fields: ValueTimeStart, ValueTimeStop, TransactionTimeStart, TransactionTimeStop.

In your implementation, the temporalization setting “bitemporal” creates only 2 datetime fields “recorded_at” and “erased_at”…
If an attribute in a bitemporal schema is flagged as “historized”, a third datetime field “changed_at” is created…

As we can see, “recorded_at” and “erased_at” are the same as TransactionTimeStart and TransactionTimeStop, whereas “changed_at” is the same as ValueTimeStart…

So, we miss the fourth column ValueTimeStop?!?

Without this fourth column, how would you store following example: today (2012-07-10) we see that the average blood pressure of a patient was “10/15” during two days (from 2012-07-03 until 2012-07-05), and we do not know the pressure for the previous nor the next days because it was not being monitored…

Then we would insert the next row in the attribute table PA_ABP_Patient_AverageBloodPressure:

PA ABP TTstart (recorded_at) TTstop (erased_at) VTstart (changed_at) VTstop (???)
1 10/15 2012-07-10 9999-12-31 2012-07-03 2012-07-05

If the next day (2012-07-11), a doctor sees that this ABP Value is incorrect for the first day, this should become:

PA ABP TTstart (recorded_at) TTstop (erased_at) VTstart (changed_at) VTstop (???)
1 10/15 2012-07-10 2012-07-11 2012-07-03 2012-07-05
1 11/14 2012-07-11 9999-12-31 2012-07-03 2012-07-04
1 10/15 2012-07-11 9999-12-31 2012-07-04 2012-07-05

Best regards,
Koen Janssens (and colleague)
Reply | Threaded
Open this post in threaded view
|

Re: Bitemporal transaction time and value time

roenbaeck
Administrator
The likely option is that it is of importance to know that the blood pressure was "Unknown" during some period of time. In that case, you must model this explicitly, for example by allowing the string "Unknown" as a value of ABP in your example. Then you can, using the single column ChangedAt, let values transition between known and unknown values over periods of time.
Reply | Threaded
Open this post in threaded view
|

Re: Bitemporal transaction time and value time

koenjanssens
We also have thought about this option, but then you always end with [n+1] "unknown" records when you have [n] records which are surrounded with "unknown" values... thus, in my example (the first table) there would be 2 extra records

PA ABP TTstart (recorded_at) TTstop (erased_at) VTstart (changed_at) VTstop (???)
1 unknown 2012-07-10 9999-12-31 0000-00-00 2012-07-03
1 10/15 2012-07-10 9999-12-31 2012-07-03 2012-07-05
1 unknown 2012-07-10 9999-12-31 2012-07-05 9999-12-31


Isn't that a bit stupid when the purpose of this model is to prevent saving unnecessary (null) values?

Another issue would be that this "unknown" can only be applied to varchar datatype, not to numeric/date datatypes...

We already begun to extend your model with this fourth column, but we struggle a bit with the constraints and triggers :)