16.10 - Normalization, Denormalization, and Primary Index Usage - Teradata Database

Product
Release Number
16.10
Release Date
June 2017
Content Type
User Guide
Publication ID
B035-1094-161K
Language
English (United States)

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.

Denormalized
part 7,000,000 rows
part_num quantity description current_effective_date current_price_amount
PK DD
1234 100 Widget 2007/01/01 14.25
1235 97 Thingie 2007/01/01 28.99
part_price_history 70,000,000 rows former_effective_date former_price_amount
part-num
PK
FK
1234 2005/01/01 13.95
1234 2005/01/01 15.45
1235 2005/01/01 27.89
1235 2005/01/01 30.99

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.

Normalized tables
part 7,000,000 rows
part_num quantity description
PK
UPI
1234 100 Widget
1235 97 Thingie
part_price_history 70,000,000 rows
part_num effective_date price_amount
PK
FK
NUPI
1234 2000/01/01 13.95
1234 2003/01/01 14.25
1234 2007/01/01 15.45
1235 2000/01/01 27.89
1236 2005/01/01 28.99
1235 2007/01/01 30.99