Archives of the TeradataForum
Message Posted: Wed, 08 Dec 2004 @ 15:45:13 GMT
<-- 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.
ColA xxx ColB xxx Old_Record_Flag CHAR(1) COMPRESS ( 'Y', 'N' ) Shadow_Record_Flag CHAR(1) COMPRESS ( 'Y', 'N' )
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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 27 Dec 2016|