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 | ||
---|---|---|
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