Derived attributes are attributes that are not atomic. Their data can be derived from atomic attributes in the database. Because they are not atomic, they violate the rules of normalization.
Derived attributes fall into these basic types:
- Summary (aggregate) data
- Data that can be directly derived from other attributes
Approaches to Handling Standalone Derived Data
There are occasions when you might want to denormalize standalone calculations for performance reasons. Base the decision to denormalize on the following demographic information, all of which is derived through the ATM process.
- Number of tables and rows involved
- Access frequency
- Data volatility
- Data change schedule
Guidelines for Handling Standalone Derived Data
As a general rule, using an aggregate join index or a global temporary table is preferable to denormalizing the physical implementation of the fully normalized logical model.
The following table provides guidelines on handling standalone derived data attributes by denormalization. The decisions are all based on the demographics of the particular data. When more than one recommended approach is given, and one is preferable to the other, the entries are ranked in order of preference.
|Access Frequency||Change Rating||Update Frequency||Recommended Approach|
|High||High||Scheduled||Use an aggregate join index or global temporary table.|
|High||Low||Dynamic||Use an aggregate join index or global temporary table.|
|Low||Unknown||Unknown||Calculate the information on demand rather than storing it in the database.|
Any time the number of tables and rows involved is small, calculate the derived information on demand.
Reasons Not to Denormalize Using Derived Data
The following items deal with the issues of derived data without denormalizing user base data tables:
- Aggregate join index (see Aggregate Join Indexes)
- Global temporary table with derived column definitions
- View with derived column definitions