Archives of the TeradataForum

Message Posted: Wed, 08 Dec 2004 @ 15:45:13 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- Anonymously Posted: Wednesday, December 08, 2004 10:43 -->

There are a bunch of things you could do if you put version columns in your table, and stored history all in one table.

For the simplest, and probably least functional, approach:

You could just add columns to indicate whether it's a shadow row, an old row, or an unchanged row.

E.g.

     ColA                 xxx
     ColB                 xxx
     Old_Record_Flag      CHAR(1) COMPRESS ( 'Y', 'N' )
     Shadow_Record_Flag   CHAR(1) COMPRESS ( 'Y', 'N' )

The data:

     ColA     ColB     Old_Record_Flag   Shadow_Record_Flag
     Foo       123            Y                 Y
     Bar       234            Y                 Y
     Mary      456            Y                 Y
     John      789            Y                 N
     John      000            N                 Y

     /* CHANGE 789 to 000 */

Normal views would have:

     WHERE Old_Record_Flag = 'Y';

Shadow views would have:

     WHERE Shadow_Record_Flag = 'Y';

Your web application would have to update the entire record where a record exists with the Shadow_Record_Flag = 'Y' and the Old_Record_Flag = 'Y'. If that does not exist, it would have to insert a new row and update the Shadow_Record_Flag on the existing row to 'N'.

You can accomplish the same kind of thing with version numbers, insert timestamps, etc.

It may not be feasible in a complex model, but the optimizer should at least have at low confidence.

	<Prev	Next>		<<First	<Prev	Next>	Last>>