Re: TimeSeries and Bi-Temporaility
Posted by:
Rick James
Date: December 29, 2014 07:12PM
Do you have a tentative CREATE TABLE and some SELECTs or pseudo code?
I would use the database as the "source of truth" for the data, then write application code to do an ordered scan of the database, computing stuff as it is read.
The point is, you still need to do a lot of work yourself, and the database may (or may not) be of much use. Did you consider storing the data in a file? Or a NoSQL? MySQL has SUM(), ORDER BY, and other useful things, but (depending on the complexity of your bi-temporal data), you may not even be able to use such.
For example, in banking, if you have a check being deposited, that is a new row. If the check bounces, the deposit record is _not_ DELETEd; instead, you add another row to indicate the bounce. If that record has a negative number, then SUM() would still work.
But, if (due to bi-temporal-ness) you insert the bounce record before inserting the deposit record, what should happen? Will reports show incorrect information in the intervening time? Etc.