Collapsing Relationships

Refining the Database Design › Eliminating Unnecessary Entities › Collapsing Relationships

Collapsing Relationships

During the normalization process in logical database design, you separated multiply-occurring data into a separate entity type (first normal form). It may be more efficient to move this data back into the original (parent) entity.

Consider this option if data occurs a fixed number of times and the data is not related to another entity. An example of such data is monthly sales totals for the last twelve months collapsed into a sales entity.

Advantages

By maintaining the data in a single entity instead of maintaining two separate entity types, you can:

Save storage space that might otherwise be used for pointers or foreign-key data.
Reduce database overhead by eliminating the need to retrieve two entities. When you express a one-to-many relationship within a single entity, application programs can access all desired data with a single DBMS access.

Note: Expressing a one-to-many relationship within a single entity offers little I/O performance advantage over clustering two separate entities.

Comparison of collapsing relationships and maintaining separate entities

The following table presents a comparison of collapsing relationships into a single entity type and maintaining separate entities.

Efficiency Considerations	Potential Impact
I/O	Expressing a one-to-many relationship within a single entity offers little I/O performance advantage over clustering two entities.
CPU time	By storing a repeating element in an entity, you can reduce the amount of CPU time required to access the necessary data.
Space management	By storing a repeating element in an entity instead of maintaining two separate entity types, you can save storage space that might otherwise be required for pointers or foreign key data.
Contention	No difference

SQL considerations

Because repeating elements violate first normal form, they are incompatible with the relational model and cannot be defined in SQL. However, if there are a fixed number of repetitions (such as months in a year), the repeating elements can be separately named (such as JANUARY, FEBRUARY, and so on). If there is a variable but quite small number of occurrences (such as phone numbers), a fixed maximum number of elements can be named (PHONE1, PHONE2, for example), using the nullable attribute to allow identification of occurrences that might not have a value.