Re: Historical data; how?

Karel Miklav wrote:

> Hi folks,
>
> I have some tables, where I should remember histories of every column.
> Ie. I should track how a person's (licence id) changes over time and be
> able to find him later by any of his (licence ids) shown. I could create
> related tables for _every_ column but... there must be a better way?
>
> Thanks, Karel Miklav

My database requires history as well. Here is my solution:

create table transaction (

    transaction_id integer not null,
    created timestamp,
    primary key (transaction_id)
);

create table patient_history (

    patient_id integer not null,
    transaction_id integer not null,
    deleted boolean not null default false,     first_name varchar (32),
    last_name varchar (32) not null,
    primary key (firm_id, patient_id, transaction_id) );

When I begin a transaction I create a new record in the transaction table using a sequence to generate the transaction_id. This transaction_id is used when a new patient_history row is created. Then, in subsequent transactions, rather than update or delete rows in patient_history, I add new rows, setting
deleted to true for deletes.

The latest patient can be found with this:

CREATE VIEW patient AS
SELECT *
  FROM patient_history
 WHERE transaction_id = (

         SELECT MAX(transaction_id)
           FROM patient_history as ph1
          WHERE ph1.patient_id = patient_id
       )

Alan Gutierrez - ajglist_at_izzy.net
http://khtml-win32.sourceforget.net/ - KHTML on Windows Received on Fri Jan 17 2003 - 16:48:02 CET