Examples of Using Denormalization | Teradata Vantage - 17.10 - Denormalizing with Repeating Groups - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Database Design

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
User Guide
Publication ID
B035-1094-171K
Language
English (United States)

Repeating groups are attributes of a non-1NF relation that would be converted to individual tuples in a normalized relation.

Example: Denormalizing with Repeating Groups

For example, this relation has six attributes of sales amounts, one for each of the past six months:

Sales_History
EmpNum Sales Figures for Last 6 Months (US Dollars)
Sales Sales Sales Sales Sales Sales
PK
FK
UPI
2518 32,389 21,405 18,200 27,200 29,785 35,710

When normalized, the Sales History relation has six tuples that correspond to the same six months of sales expressed by the denormalized relation:

Sales_History
Sales_History
EmpNum SalesPeriod SalesAmount (US Dollars)
PK    
FK
NUPI
2518 20011031 32,389
2518 20011130 21,405
2518 20011231 18,200
2518 20010131 27,590
2518 20010228 29,785
2518 20010331 35,710

Reasons to Denormalize With Repeating Groups

The following items are all possible reasons for denormalizing with repeating groups:

  • Saves disk space
  • Reduces query and load time
  • Makes comparisons among values within the repeating group easier
  • Many 3GLs and third party query tools work well with this structure

Reasons Not to Denormalize With Repeating Groups

The following items all mitigate the use of repeating groups:

  • Makes it difficult to detect which month an attribute corresponds to
  • Makes it impossible to compare periods other than months
  • Changing the number of columns requires both DDL and application modifications