Re: modelling history in a database

From: Alan Gutierrez <ajglist_at_izzy.net>
Date: Sat, 15 Feb 2003 02:50:14 GMT
Message-ID: <GVh3a.10623$4F3.524954_at_news2.east.cox.net>

Paul Vernon wrote:
> "stu" <smcgouga_at_nospam.co.uk> wrote in message
> news:b2ikjv$4rt$1$8302bc10_at_news.demon.co.uk...

 >>I actually did a simple version of this a while back. archive all  >>fields to csv format and store in a memo field:

 >>name, age, height, weight,....

> Come on. This is mean to be a theory newsgroup. You guys are just not
> trying.

I'm trying very hard in my application. I am tracking time series data through history tables and through normalization. Here I'll discuss the histoty tables in their latest incarnation:

My medical charting application requires that all changes to patient data are recorded, along with who and when. If a patient has their name misspelled at admission and the spelling isn't fixed for a week, the app must reporduce the medical assessment made at admission with the misspelling.

I keep the who and when in a separate table called transaction:

CREATE TABLE Trasnsaction
  (firm_id INTEGER NOT NULL, -- application is a time share    transaction_id INTEGER NOT NULL, -- fed by a sequence    modified TIMESTAMP NOT NULL,
   modified_by INTEGER NOT NULL,
   PRIMARY KEY (firm_id, transaction_id)    FOREIGN KEY (firm_id, modified_by) REFERENCES (Worker));

This table keeps track of each database transaction. It is created at the start of a database transaction.

Here is my patient table:

CREATE TABLE Patient
  (firm_id INTEGER NOT NULL,
   patient_id INTEGER NOT NULL,
   first_name VARCHAR(32),
   last_name VARCHAR(32) NOT NULL,
   physician INTEGER NOT NULL,

• more fields snipped for brevity PRIMARY KEY (firm_id, patient_id), FOREIGN KEY (firm_id, physician) REFERECES Worker);

For this table I create a table like so.

CREATE TABLE Patient_History
  (firm_id INTEGER NOT NULL,
   patient_id INTEGER NOT NULL,

   transaction_id INTEGER NOT NULL,          -- Transaction
   deleted BOOLEAN NOT NULL,                 -- Deleted?

   first_name VARCHAR(32),
   last_name VARCHAR(32) NOT NULL,
   physician INTEGER NOT NULL,

• more fields snipped for brevity PRIMARY KEY (firm_id, patient_id), FOREIGN KEY (firm_id, transaction_id) REFRENCES Transaction));

I've got triggers for insert and update that insert a copy into this table with the transaction_id for the current transaction. On delete a copy of the old values is inserted and the deleted column is set to true.

To travel back in time I create a view that references a temporary table. Your database might not let you build a view that references a temporary table, so use a global variable or a function.

CREATE VIEW Patient_Past
SELECT *
   FROM Patient_History
  WHERE transaction_id = (

          SELECT MAX(transaction_id)
            FROM Transaction AS T1
           WHERE T1.firm_id = firm_id
             AND T1.patient_id = patient_id
             AND T1.transaction_id <= (
                 SELECT past_transaction_id FROM Past_Transaction LIMIT 1
             )
        )

    AND NOT deleted;

Actually, with PostgreSQL that query can be expressed as:

CREATE VIEW Patient_Past
SELECT DISTINCT ON (firm_id, patient_id) * -- Non SQL standard?

   FROM Patient_History
  WHERE transaction_id <= (

          SELECT past_transaction_id FROM Past_Transaction LIMIT 1
        )

    AND NOT deleted
  ORDER BY transaction_id DESC;

So, when I want to take the database back to January 5th, 2002 I'd do this:

CREATE TEMPORARY TABLE Past_Transaction AS SELECT max(transaction_id) as past_transaction_id

   FROM Transaction
  WHERE modified <= '2002-01-05';

The DDL history tables assoicated triggers and past views are all genrated by a Perl script. Becasue it is so easy to create history tables, I maintain them for all the tables in my application.

Thoughts?

Alan Gutierrez - ajglist_at_izzy.net Received on Sat Feb 15 2003 - 03:50:14 CET