Re: Way to design "history" in DBs

On Thu, 02 Mar 2000 09:48:56 +0100,
"Ruud" == Ruud en Maud <celebes_at_xs4all.nl> writes:
>> What I do to may tables is add a "Date Effective" column, plus whatever
>> other data is necessary to track the change. That date column, along
>> with whatever set of columns was used as the primary key, is now the new
>> primary key. The new tables can be reduced to the originals using
>> views. That way the queries are simpler when only current data is
>> important.

just a note: Illustra organized its transactions around the concept of a two system provided 'timestamp columns' on each row of each table (let's call them BORN and DIED, the names were probably different).

There is a function now(), which denotes the current time, are two special timestamps, lets call them PAST (always < now() and FUTURE (always > now())

To every ordinary SELECT, an implicit 'AND now() BETWEEN BORN AND DIED' was added.

INSERTions get a born=now(), died=FUTURE, and DELETEions simply consist of get setting died=now(). I.e., rows are never reallly deleted, unless you explicitly do this with a special utility (called vacuum I believe) to purge the tables.

UPDATEs translate to a DELETE and an INSERT, with the above behaviour.

Even the transactions used this, e.g. INSERTed rows have a born=FUTURE value until they're COMMITed. (I think there were actually two versions of the born and died fields; one public, the other private to the transaction; a COMMIT would consist by setting public_born=private_born and public_died=private_died)

The upshot of all this is that you can very easily travel back in time, i.e. you can inspect the state of the database as it was at some point in the past, and using some extra syntax e.g.

SELECT * FROM Tab WHERE ... ON to_date('10-oct-1582')

(I don't know the actual syntax). (This query will return NULL, for an entirely unobvious and nasty reason that is left as an exercise for the reader ;-).

This offers you a straighforward way to keep a full history in your tables, including 'infinite undo (rollback) capability'. It's easy to transplant into other RDBMSs. Cheers,

                                                                      Philip

-- 
Not getting what you want is sometimes a wonderful stroke of luck.
-----------------------------------------------------------------------------
Philip Lijnzaad, lijnzaad_at_ebi.ac.uk | European Bioinformatics Institute,rm A2-24
+44 (0)1223 49 4639                 | Wellcome Trust Genome Campus, Hinxton
+44 (0)1223 49 4468 (fax)           | Cambridgeshire CB10 1SD,  GREAT BRITAIN
PGP fingerprint: E1 03 BF 80 94 61 B6 FC  50 3D 1F 64 40 75 FB 53