Re: How to Implement Versioned Rows?

From: Alan Gutierrez <ajglist_at_izzy.net>
Date: Tue, 28 Jan 2003 15:15:58 GMT
Message-ID: <O8xZ9.45701$GX4.1723299@news2.east.cox.net>

Jonathan Leffler wrote:

> Alan Gutierrez wrote:

>> My application is gathering personel and patient data for a hospice. It
>> must keep track of all changes to patient stats, chart, and med sheet
>> over time. Therefore, I would like to keep versions of the rows in many
>> of my tables.

>
> This sounds rather like a temporal database. You should probably
> review the material in two books (at least):

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

I am ordering Snodgrass.

[snip]

> So, in general, there'll be many rows identified as being modified by
> any given transaction ID.

Correct. All the rest of the stuff I wrote was unecessary.

[snip]

>> -- Example versioned table.
>> 
>> create table person_history (           -- Base for patient and employee
>>     firm_id integer not null,
>>     person_id integer not null,
>>     transaction_id integer not null,
>>     first_name varchar(32),             -- Just two example data columns
>>     last_name varchar(32) not null,
>>     deleted boolean not null,
>>     primary key (firm_id, person_id, transaction_id)
>> )
>> \g

>
> In the absence of the tracking, you'd have person_id as the primary
> key? And is there a cross-reference between the transaction table
> (modified_by column) and this table? Suppose a new person is created
> with person ID P1 by a person from firm F1 with transaction ID T1;
> subsequently, the information is updated by a person from firm F2 with
> transaction ID T2. Does the primary key now change from {F1,P1,T1} to
> {F2,P1,T2}? Does that mean all cross-references to {F1,P1,T1} now
> need to be updated to reference {F2,P1,T2}? Or do they continue to
> reference just P1 and let the temporal information {F1,T1} vs {F2,T2}
> go by the wayside?

No one from F2 will be allowed to modify data in F1 ever. Never, ever. Never, ever, never. The application is a time share. The use of firm_id means that to add a customer to our time share we create a new row in the firm table. If two of our customers were to merge, it would mean that we would copy data from one instance to another. It would be as if the application resided on two separate machines.

[snip]

>> Thoughts:

[snip]

>> * No archive tables or such means no copying, new version is a simple >> insert. Good.

> Read Snodgrass...

Rodger, Wilco.

[snip]

>> Questions:
>> 
>> * Is this viable or overly clever?

>
> Gut feel: not really viable. And both overly clever and not yet clever
> enough.

Since I've posted this, I am much more conformatable with the deisgn myself. It doesn't seem to vary much from the many other designs that I've seen proposed in c.d.t when historical data is discussed. Something about when is added to the key, either a sequence or a timestamp. I just want to use the same sequence value as in all the versioned tables for a particular transaction.

The design boils down to this:

CREATE TABLE Transaction

   (transaction_id INTEGER NOT NULL, -- sequence fed     modified TIMESTAMP NOT NULL,
    PRIMARY KEY (transaction_id));

CREATE TABLE Patient

   (patient_id INTEGER NOT NULL,
    transaction_id INTEGER NOT NULL

        REFERENCES Transaction (transaction_id),     first_name VARCHAR(32),
    last_name VARCHAR(32) NOT NULL);

All the other prattling confused the issue. My posts are becoming far more concise.

>> * Should I have a boolean latest column on a versioned table? This would >> mean update and vacuum, but potentially a faster query.

> It converts every non-SELECT operation into a double operation - one
> to update the 'latest version' flag on the old record plus the
> operation that creates the new record (or removes the old record).

Yes, but then the subsequent selects may be faster, the double operation can be expressed as a single trigger attached to all version tables, and the double operation ought to go quick.

[snip]

Thank you for your thoughtful response. I didn't notice it until just now. I am sorry it took so long for me to respond.

Alan Gutierrez - ajglist_at_izzy.net Received on Tue Jan 28 2003 - 09:15:58 CST