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.
|
part 7,000,000 rows |
|
part_price_history 70 000 000 rows |
||||
|
part_num |
quantity |
description |
|
part_num |
effect_date |
price_amount |
|
PK |
|
|
|
PK |
|
|
|
UPI |
|
|
|
FK |
|
|
|
1234 |
100 |
Widget |
|
NUPI |
|
|
|
1235 |
97 |
Thingie |
|
1234 |
2000/01/01 |
13.95 |
|
|
|
|
|
1234 |
2003/01/01 |
14.25 |
|
|
|
|
|
1234 |
2007/01/01 |
15.45 |
|
|
|
|
|
1235 |
2000/01/01 |
27.89 |
|
|
|
|
|
1235 |
2005/01/01 |
28.99 |
|
|
|
|
|
1235 |
2007/01/01 |
30.99 |