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
- 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 | Dynamic |
|
| High | High | Scheduled | Use an aggregate join index or global temporary table. |
| High | Low | Dynamic | Use an aggregate join index or global temporary table. |
| High | Low | Scheduled |
|
| 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
- Aggregate join index (see Aggregate Join Indexes)
- Global temporary table with derived column definitions
- View with derived column definitions