| Subject | Re: [firebird-support] Fast Temporal data in Firebird |
|---|---|
| Author | Kjell Rilbe |
| Post date | 2011-02-08T09:41:38Z |
Paul Vinkenoog skriver:
historical data. So, not what you need.
which supports historical data. But our requirements were a bit more
advanced than that, so we ended up building a complete two-faced
solution. The model contains a "current data" view that is modeled in
the usual way, e.g. one table for "Company", one for "Person", and so on.
But whenever a value is changed, our system captures it and stores it in
a separate subsystem where each value for every column and row in the
"current data" view is stored along with timestamps (both "from" and
"to" as well as "last confirmed") and a lot of other metadata, like data
supplier, which customers have bought it, etc. The data values are all
stored in a single large table "DataValues".
For us, 99% of all searches are on "current data", which will be fast
and simple as usual, but we also have capability to search historical
data when the need arises, although the searches are more complicated.
I'm not sure if this matches your use case, but I wanted to give you an
idea of a different approach. Not sure it can be easily extended to hold
future data. The "DataValues" subsystem certainly can, but mapping it to
the "current data" subsystem would require some kind of continuous
update from the "DataValues" subsystem. Seems rather complicated.
One option is also to do as you said and add the "data entered"
timestamp to the primary key, but probably also a "data removed"
timestamp. Then add a view that picks the rows where current_timestamp
falls between "data entered" and "data removed". How to index it for
efficient searches is left as an exercise for the reader. ;-)
Regards,
Kjell
--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64
> > We need to design a system, that keeps past/present/future data basedSeems to me that DB2's feature doesn't support future data, only
> on a time axis.
> >
> > ie. The system needs to know what price a product will be at times in
> future, was in time in past, and is currently.
> >
> > see this link for further details.
> >
> http://www.databasejournal.com/features/db2/article.php/3914031/Lets-Do-the-Time-Warp-Temporal-Data-Comes-To-IBM-DB2.htm
historical data. So, not what you need.
> > I want to know if anyone has attempted to meet this type ofWe have built a system on the Oo framework ECO (www.capableobjects.com),
> requirement in firebird?
which supports historical data. But our requirements were a bit more
advanced than that, so we ended up building a complete two-faced
solution. The model contains a "current data" view that is modeled in
the usual way, e.g. one table for "Company", one for "Person", and so on.
But whenever a value is changed, our system captures it and stores it in
a separate subsystem where each value for every column and row in the
"current data" view is stored along with timestamps (both "from" and
"to" as well as "last confirmed") and a lot of other metadata, like data
supplier, which customers have bought it, etc. The data values are all
stored in a single large table "DataValues".
For us, 99% of all searches are on "current data", which will be fast
and simple as usual, but we also have capability to search historical
data when the need arises, although the searches are more complicated.
I'm not sure if this matches your use case, but I wanted to give you an
idea of a different approach. Not sure it can be easily extended to hold
future data. The "DataValues" subsystem certainly can, but mapping it to
the "current data" subsystem would require some kind of continuous
update from the "DataValues" subsystem. Seems rather complicated.
One option is also to do as you said and add the "data entered"
timestamp to the primary key, but probably also a "data removed"
timestamp. Then add a view that picks the rows where current_timestamp
falls between "data entered" and "data removed". How to index it for
efficient searches is left as an exercise for the reader. ;-)
Regards,
Kjell
--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64