Pattern: Foreign Key Aggregation


The pattern shows how to map aggregation to a relational data model using foreign keys.


Reconsider the example for Single Table Aggregation (see Figure 1 ). Presume you want a solution that treats the AddressType as a first class object and that allows better maintenance than Single Table Aggregation.


How do you map aggregation to relational tables?


See the Single Table Aggregation pattern.


Use a separate table for the aggregated type. Insert an Synthetic Object Identity into the table and use this object identity in the table of the aggregating object to make a foreign key link to the aggregated object.


The AggregatingObject is mapped to a table. The AggregatedObject is mapped to another table. The Aggregated Object’s Table contains a Synthetic Object Identity. This SyntheticOID is referenced by the AggregatedObjectsOID foreign key field in the Aggregating Object’s Table.

Example Resolved

If we apply the solution to the example , we get a Customer Table that contains two foreign key references to the AddressType Table . The AddressType Table contains a Synthetic Object Identity field that is used to link the two tables.

Retrieving a customer object from the database now costs three database access operations (one for the Customer and one for each AddressType, Invoice Address and DeliveryAddress) instead of one in the case of Single Table Aggregation.

This can be brought down to a single join database access, if the AddressType Table is equipped with an additional back link field that points to a Synthetic Object Identity of the Customer Table. The cost of this is getting a result set of two addresses, each with all the customer attributes.



Related Patterns

For an alternative see Single Table Aggregation. Foreign Key Association works very similar. See also Representing Collections in a Relational Database [Bro+96].