Re: historical data in relational model

#1

Christian Schuhegger wrote:

Quote:

i am thinking about introducing historical data in an already existing
database similar to what a version control system does for source code
management.
[...]
before i start to reinvent the wheel i would like to ask if anybody can
point me to some online references that describe solutions for this
problem?

Not online, but:

R T Snodgrass - Developing Time-Oriented Database Applications in SQL
C J Date et al - Temporal Data and the Relational Model

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler (AT) earthlink (DOT) net, jleffler (AT) us (DOT) ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

#2

Christian Schuhegger (Christian.Schuhegger (AT) cern (DOT) ch) wrote:
: hello,

: i am thinking about introducing historical data in an already existing
: database similar to what a version control system does for source code
: management.

: to be more specific: in the database there will be stored a given
: configuration of hardware elements and relations between these hardware
: elements. over time the hardware configuration will very likely change,
: e.g. old sensors will be replaced by new ones which have different
: callibration data and different identification codes. but i don't want
: to loose the old data and i would like to be able to ask questions like:
: what was the active configuration 12. march 2002?

: before i start to reinvent the wheel i would like to ask if anybody can
: point me to some online references that describe solutions for this problem?

: thanks a lot for any comments and suggestions!

In some systems I have seen they simply add a time frame to some of the
data, specificly a start_date and an end_date. To get specific versions
you just qualify all the data with the time frame involved. I haven't
used this much myself though, it's just what I have observed.

#3

In article <3F2677E5.4030004 (AT) cern (DOT) ch>,
Christian Schuhegger <Christian.Schuhegger (AT) cern (DOT) ch> wrote:

Quote:

before i start to reinvent the wheel i would like to ask if anybody can
point me to some online references that describe solutions for this problem?

http://www.celko.com/

Even better, buy his book SQL for Smarties.

--
Andy Dent BSc MACS AACM http://www.oofile.com.au/
OOFILE - Database, Reports, Graphs, GUI for c++ on Mac, Unix & Windows
PP2MFC - PowerPlant->MFC portability

#4

Quote:

i am thinking about introducing historical data in an already existing
database similar to what a version control system does for source code
management.

to be more specific: in the database there will be stored a given
configuration of hardware elements and relations between these hardware
elements. over time the hardware configuration will very likely change,
e.g. old sensors will be replaced by new ones which have different
callibration data and different identification codes. but i don't want
to loose the old data and i would like to be able to ask questions like:
what was the active configuration 12. march 2002?

Isn't that what a data warehouse does?

#5

Hi,

using timestamp (or just putting date when new record was added)
is probably the best way how to solve this problem. You will have
more records for every single place with proper timestamp.

You can query your database by 2 ways:
1. give me the most recent record = actual data
2. give me the newest record older than mm/dd/yyyy - you will get
state for particular date

igor

"Anthony W. Youngman" <thewolery (AT) nospam (DOT) demon.co.uk> wrote

Quote:

In article <bg8l5k$5tf$1 (AT) pheidippides (DOT) axion.bt.co.uk>, programmer
int.consultNOCAPITALS (AT) macmail (DOT) com> writes
i am thinking about introducing historical data in an already existing
database similar to what a version control system does for source code
management.

to be more specific: in the database there will be stored a given
configuration of hardware elements and relations between these hardware
elements. over time the hardware configuration will very likely change,
e.g. old sensors will be replaced by new ones which have different
callibration data and different identification codes. but i don't want
to loose the old data and i would like to be able to ask questions like:
what was the active configuration 12. march 2002?

Isn't that what a data warehouse does?

Absolutely NOT !!!

Imagine a real-world database, shipping loads of goods. They have stock
information, invoices, shipping manifests, and other assorted goodies.

All this is transferred to a data warehouse, sliced, diced, and analysed
ninety ways from Sunday, so that the Company Board can ask all sorts of
useless questions and get a meaningless answer back.

Okay, I'm being facetious, but the purpose of a data warehouse is to
store oodles of pre-analysed data to enable quick answering of queries.
Oh, by the way, some of the biggest are built on Pick technology,
because the quickest way to bring a SQL db to its knees is to ask a
question that the database hasn't been optimised to answer...

Cheers,
Wol

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: historical data in relational model

comp.databases comp.databases

Re: historical data in relational model - 07-29-2003 , 09:43 AM

Re: historical data in relational model - 07-29-2003 , 12:44 PM

Re: historical data in relational model - 07-29-2003 , 01:42 PM

Re: historical data in relational model - 07-30-2003 , 09:28 AM

Re: historical data in relational model - 08-01-2003 , 07:24 AM