>Everyone,
>
>Here's an example of denormalization as a valid design technique.
>
>In a Custoemr table you have moved the City and State fields to a related ZipCode table with the join based on the ZipCode in teh customer table. You did this to achieve 3rd Normal Form (All fields are dependent on ONLY the PK) when you realized that City and State were codependent on ZipCode and the PK.
>
>Your system is used to print 10,000,000 mailing labels once a week. Your tesint finds that a lable printed from a two table join as comapred to a single table operation takes an exteremly longer time to process. You determine that teh label printing is more of an isue than the update anomaly possibilities so you decide to denormalize and put the city and state fields in the customer table directly. You have denormalized the structure of the Customer table because it is not in 3rd normal form any longer. However you ahve done it for a valid reason.
>
>The fact that you went all the way to fully normalized before backed off for a specific reason has given you the ZipCode table for data entry validation and for correcting any update anomalies that may occur.

Jim,

Can you support this claim with some time trials? I have tried, and came to the conclusion that the normalized data was more efficiently managed by the database engine, and so you do not achieve any performance gain from denormalizing as you suggest in this example. Tests and results are at http://fox.wikis.com/wc.dll?wiki~DeVsNormSpeed

I have not tested this, but I belive this is an example where denormalizing does result in a performance gain.

* Requirement: determine where to have a company meeting
* based on the location of the people who have earned the most income.
* income follows the person, not the state it was earned in.

* Both queries would return the same results,
* but the second should execute faster;
* it requires less disk i/o and less processor cycles.

create database denorm2

create table CitySt ;
( pkzip c(5) primary key, ;
cCity c(30), cState c(2) )
create table Person ;
( pkPid c(10) primary key, ;
cFirstName c(30), cLastName c(30), cStreetAddress c(30), ;
fkZip c(5) references CitySt )
create table PayCheck ;
( cCheckNo c(10) primary key, ;
fkPid c(10) references Person, ;
yGross y )

select cState, sum( yGross ) ;
from PayCheck join Person on fkPid = pkPid join CitySt on fkZip = pkZip ;
group by cState

drop table PayCheck
create table PayCheck ;
( cCheckNo c(10) primary key, ;
fkPid c(10) references Person, ;
fkZip c(5) references CitySt, ;
yGross y )

select cState, sum( yGross ) ;
from PayCheck join CitySt on fkZip = pkZip ;
group by cState