The pattern shows how to map aggregation to a relational data model by integrating all aggregated objects’ attributes into a single table.
Consider the following object model:
Figure
1: An AddressType aggregated by more than one other types
How do you map aggregation to relational tables?
Performance: For optimal performance the solution should allow to retrieve an object with one database access without any join operations. Database accesses should fetch a minimum number of pages to economize on I/O bandwidth.
Maintainability: For optimal maintainability, aggregated types, that are aggregated in more than one object type, should be mapped to one set of tables instead of being strayed identically across many different spots in the physical data model. Normalization should be used at the data model level to ease maintenance and ad hoc queries.
Consistency of the database: Aggregation implies that the aggregated object’s life cycle is coupled with the aggregating object’s life cycle. This has to guaranteed by either the database or application code
Put the aggregated object's attributes into the same table as the aggregating object’s.
The AggregatingObject is transformed to a table of the physical data model. The AggregatedObject’s Attributes are integrated into that table.
To resolve our above example we look at the table created for the Customer object. The InvoiceAddress and the DeliveryAddress are both integrated into the Customer’s database table.
Figure
2: Mapping
an aggregated object type into the aggregating object’s table
A prefix is used to distinguish the attributes. This is similar to the resolution of structures in C++, using a dot notation (like Customer.DeliveryAddress.Street).
Performance: The solution is optimal in terms of performance as only one table needs to be accessed to retrieve an aggregating object with all its aggregated objects. On the other hand, the fields for aggregated objects’ attributes are likely to increase the number of pages retrieved with each database access, resulting in a possible waste of I/O bandwidth.
Maintenance and flexibility: If the aggregated object type is aggregated in more than one object type, the design results in poor maintainability as each change of the aggregated type causes an adaptation all of the aggregating object types’ database tables.
Consistency of the database: Aggregated objects are automatically deleted on deletion of the aggregating objects. No application kernel code or database triggers are needed.
Ad-hoc queries: If you want to form a query that scans all AddressType objects in the database, this is very hard to formulate.
Naming convention: You need to think of a prefix or another naming convention for the aggregated object’s attributes that appear in the aggregating object’s table. In the above example we use a prefix that is a short form of the attribute name.
Physical database page size: The positive effects of aggregating an object in the same table can be partially compensated if the aggregated object’s attributes start a small fraction of a new database page. In such a situation two database pages need to be read instead of one.
We have discussed the simple case of a 1:1 relation between aggregating object type and aggregated object type. See Foreign Key Association for how to map a 1:n relation between aggregating object and aggregated object. See also Overflow Table [Kel+97 ] for a trick to avoid using foreign key associations in case of 1:n relations.
Foreign Key Aggregation is an alternative solution to Single Table Aggregation. See also Representing Collections in a Relational Database [Bro+96]. When applied to ordinary relational database access layers, it can be compared to Denormalization [Kel+97 ].
“Mainstream Objects”, a book by Ed Yourdon et al. [You+95] dedicates its whole chapter 21 to the question of when and how to use aggregation versus associations at the modeling level.