Jim,

>The reason problem with trying to see this as a normalization issue is the confussion fo teh logical with the physical design.

Well, I've got trouble to draw the line between the logical and physical design. When I refer to the history problem, stacking is IMO not a normalized solution, though you tend to say it is. You can access tables trough views which themselves are normalized when the underlying tables are not. IMO normalization applies to the database scheme. If in a new project I come across a developed database, I should be able to determine of the database is normalized with the help of the database scheme only. How this scheme is used in the application has nothing to do with normalization.

So if a table is divided into 8 or more tables because you've have to deal with the technical limits of a tool, I'd say that you've denormalized the table because of technical limits (though I can't put my finger on the weak spot why the solution isn't normalized here)


>Here's an example of what I mean. You have a database with records of scientific test results. Each test has 2000 values recorded for it. These values are not multiples of one thing, they are discrete and separate values.

>Good RD design says these values shoudl all be part of the a record of the same entity. However, VFP is limited to 255 fields in a record, so you divide the record into 8 tables all related on a one-to-one basis. Have you violated normalization or any other rule of relational design? No, you haven't.

In your case, I would doubt if it would be wise to put all the tests in one record. I'd rather would define the tables in something like this:

Table Tests:
Testid, description, test_date, .....

Table Test_sort:
Test_sortid, Description, .....

Table testresults:
Testid, test_sortid, value

Especially when there are cirsumstances wherein it could be possible if there are cases where some tests are skipped, because of new technologies new tests could be invented and enventually want to store more information about a particular test.

>Remember we normalize the Logical design and we determine the physical design by merging the logical design with other conatraints (like limitations of the tool or performance considerations).

Yep, IMO this could be denormalization. In your case it would be difficult to see when looking at the physical database scheme whether this is a normalized table or not. I've got some serious problems to this approach. In fact if the only thing you've got is the physical database scheme you're not able to determine if the database scheme is normalized or not at all.

>The simplest way to explain denormalization is this, Normalization optimizes a design for updating and inserting with minimal anomaly possibilties

IMO, this isn't always true, because when information about an entity is stored within seperated tables (because of normalization, look at garrets example of solving the history problem which IMO is the ONLY pure right way to do this) You can have sitiuations where you've got to deal with more than one table when inserting entities, decreasing performance.

>and often causes a reduction in performance on the side of data retrieval. Denormalization is a process of backing off from the fully normalized state in order to address data retrieval problems.

It seems that you think of normalization trough a higher abstraction layer (the logical design). I tend to think about normalization at the physical design. I think, you think that controlled redundantion is normalized. For example the following case:

I've got a table employee:

Empid, first_name, last_name, ....

I've got a table Traveling_expenses:

Travelid, Empid, first_name, last_name, ....

From first sight you would say that the fields first_name and last_name are redundant. But when I say that this redundation is controlled (when you change the corresponding fields in the employee table it automaticly changes it in the traveling_expenses table and you are not able to change these in the last one) you would call this normalized tables ?

Physicly the tables are certainly not normalized, logically they are (because logically the fields are related one to one).

Since you look at denormalization from the logical design. Can you give me an example of denormalization ?

Walter,