The following example shows how a well-designed normalized table set can be as high-performing as the equivalent denormalized form while avoiding all the redundancy and update anomaly difficulties of denormalization. Note that denormalization is a logical, not a physical, concept (see Denormalizing the Physical Schema). The term is used here only because it is most commonly understood in such a context, not because it is correct.
The most frequently performed query against these tables determines the current price for a given part.
Denormalized Table Set
The first set of tables is denormalized by including current_effective_date and current_price_amount columns in the part table. Because the price of the part is also carried in the part_price_history table, this is a violation of normalization due to redundant data.
|part_price_history 70,000,000 rows||former_effective_date||former_price_amount|
Normalized Table Set
The second set of tables is normalized. By defining a NUPI on part_num, you can determine the current price of any part by reading only one additional block on the same AMP that holds the part table row.
With a composite UPI defined over part_num and effective_date, the lookup cost for determining the current price of a part would be substantially higher.