>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 see. You see this on a higher abstraction level. You think the of contents of the records as attributes themselfs. Hereby you ingnore (in terms of normalization) the fact that the records themselves contain other attributes.

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

Okay, i agree. Note that this is one of the situations where i've got problems with. I try to be consistent, but when I see a situations where it serves no benifit att all (the generated PK isn't used in RI, and only takes up space) I really doubt if I shoud be consistent.

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

Yep, you hit the hammer on the nail. This is what the history problem is about.

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

IMO, it is denormalized when it's only used in a synchronized (one to one) relationship. If this contraints are omitted and you CAN have different values for historical or incidental purposes, I agree it's designed to work this way.

But also note that this again has to do with history. Almost all statistical tables suffer from the fact that the relational model and therefore each RDBMS does not support 3D data storing, especially when there are situations where it must be possible to change the history. To give an example.

If I have a persons table with the mutations of the last 5 years, on detail level (method: stacking). This table look something like this:

BeginDate, EndDate, Personid, First_name, Lastname, Departmentid, Salarylevelid, BirthDate...

the department table looks like this

BeginDate, EndDate, Departmentid, Departmentname, Adress, ZIP, city,.....

A simular structure applies to the salarylevel table.

Note that you want to do this to track consistency trough time. This is esspecially important in financial systems, like banking and insurance companies) The default RI is getting you nowhere because you can't match keys (Begindate and EndDate is part of the key). You've got to write a custom RI mechanism to handle this.

With shadowing you can avoid some, but not all RI problems. Note that you might want constraints which aviod all kinds of consistency when changing historical data. Example, An employee points out that he started working for a particular department two years earlier than administered in the system. Of course you want a constraint that checks if the department already existed at that given moment. Another example might be that if a some data of a discontinued department has still to be inputed, you'll have problems when it's already discontinued within the system.

Of course you can say that these are not so common situations. But I think this is not the case:

In many systems you keep track of historical data, like sales. These historical data often contains foreign keys to their parent tables. But a logistic employee wants to delete an article of they don't sell them anymore. Of course here the RI mechanism objects because it still occurs in the statistical table. Of course there is something to say about that because you want to be able to query the article attrbutes when looking at historical data.

Practically we solve this by adding a discontinued attribute to the article entity, but this is IMO only a workarround. If we implement the article table in a historical way, we can not only track changes of the article (like pruchase and sales prices), but we can also DELETE the article when we don't sell it anymore.

Walter,