Walter,

I'll address each of your observations separately.

>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.


It is fully normalized. The audit table stores information about a specific entity, that being table records. Each memo field stores one attribute about the table records either, BeforeChangeState, AfterChangeState, or Structure. Normalization might be doubted if the record in the audit table were considered differently. That is if there is a Custoemr entity then all teh attributes must be atomic for that entity. If a record about a custoemr record in teh audit table was interpreted as a customer record than the structure of the attributes would not be atomic. However, if the record in the audit table is viewed as record of a transaction that occured to teh customer table, then the atributes being used are atomic (that is taking the BeforeChanges field aprat loses its meaning within the context of the entity.)

>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.

No, it doesn't, but that is an issue of the requirements. In my case the requirement was two fold, print a full audit report showing everything that changed and reset the clock on teh system to an earlier state without using a full backup and restore.

>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 ?

Yes, there is a surrogate PK on the audit table. Even though there are no children in the description I gave you, there was an anticipated requirement to be able to handle a self relation for chaining the changes that occured as part of a transaction. This never got done for a number of reasons, not the least of which was the time adn budget constraints.

>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.

Here's where you are misinterpreting my points. There is no RULE regarding surrogate keys. It is just tha way I do it, and I find compelling reasons to do it this way. For consistency I always use a surrogate key. In an invoice details table I have the InvoiceNumber, the LineNumber, adn the InvDetID (surrogate PK). In Garrett's example I would also use a surrogate PK.

>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 ?

Well, it is only a problem because relational databases are two dimensional and if you are storing more dimensions than that you have to figure out how to handle it since the RDBMS doesn't address this. The common mapping of 3=-d data to the 2-d of RDBMS is to store the data in 2-d and build the 3-d curing retrieval.

>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.

There is no redundant data in the example you are speaking of. Therefore there is no argument to support for denormalizing since the data is normalized.