Jim,

>When I need that I use an audit trail table. The most complete audit trail I ever built was for a company in NYC. We used a table with three memo fields adn timestamp and user id field. When a use modified a record we did an AFIELDS() and saved teh resulting array into one memo, then we saved the original data into another memo and finally we saved the changed data into the third memo. The date fiedl was the date of edit and the suerid was the login account that made the changes.

I've got doubts if this aproach is normalized. IMHO, The cell now contains data from more than one attribute wich should be stored in normalized tables. But of course of practical issues, your solution is one of the next-best.

I used more or less the same approach for replication purposes, but this would not allow me to easely track changes in time regarding specific attributs.

Therefore I use stacking, and usually use a begindate and an enddate to easely determine the scope of the record. This would be cross record redundant data because the enddate can be determined otherwise. One program i've written had to have the possibility to make reports about the historical changes of instruments trough time. Stacking made this possible but it has its problems too.

In the context of the original message, do you have to have an artificial key for this stacktable ? Folowing the relational theory each relation has to have a primary key. If yes, what purposes does it have when it does not have any child tables, other than an unique constraint ? Wouldn't it be more logical to name the combination of begindate(or timestamp if neccesary) and instrument as the primary key ?

The same does apply to the sultion garret mentioned. Do we have to insert a artificial primary key in the Date,PersonId,DepartmentId table ? The rules say yes, but my practical intuition says no.

>But the approach you are speaking of isn't redundant data the history data has a different purpose for existing than the curretn data does and that's causes teh domains to be different. For example, a user changes the name field, now the current table has "The Current Name for the customer" the history table (or record) has "The name for the customer at the stamped point in time".

I agree, this is just point i'm making. Virtually the tables are not two dimensional anymore, but three dimensional (Rows, Columns, time). The issue is, how do you solve this problem ?

>The two definitions are different so the two attributes are different attributes and there is no redundancy, no violation of Normalization.

You can solve this problem in diffent ways, each having it's own problems, one solution can be less relational than the other, IMO.

IMO we enter a grey area. For example if I have a ordertable where besides the clientid, I include some invoiceadress data, or the name of the contactperson while having included a personid, Many people will argue this is redundant data and this table isn't normalized. But when this order table is (or are going to be) used for OLAP purposes also you're able to track the actual data at that time. Another solution would be to take these data out and make a new table with all invoice adresses ever used, and insert a parent-child relation up here, but this won't be as pratical as the number of changable attributes grows.

Though I agree this could be solved in another way (by stacking persons and client data) the former sulion makes the database more flexible because now your able to change adress data in incidental cases. (In practise this often is also solved by changing the client data, print the invoice and change the data back, but here you would lose significant data). A number of logistic systems use this approach.

>I might argue with using the same table for both current and history, but that would be an implementation discussion.

Very good point. Yes when using stacking in the way I did use it you can't solve Referential Integrity issues in an elegant way, because you don't have a persons parent table. Therefore it would be more logical to have aside from the stacking table a parent person table in which the record data is the current data. Within the stack you would probably have the same data copied to make tracking changes trough time less difficult (you don't have to use the parent table in these queries). But in this way you'll create (row)redundant data again (which can't be avoided within normalisation, of course).

>This would be an example of what I said in a previous message, that often I hear a developer talk about normalization being a burden because of ... only to discover that the issue was a non-issue and normalization woudl not require the structure the way the developer had thought it would.
>Normalization is not ONLY data in one place, it is also data in the right place.

I think I don't agree here, IMO, Garrets solution was a more pure solution in which there is no doubt that the database is normalized (because it is). Because it is difficult to handle these data, because performance drops down by the more attributes that can change (because of the joins), database designers often use a more practical approach.

Walter,