Pattern: Foreign Key Association


The pattern shows how to map 1:n associations between objects to relational tables


Consider the classic Order / OrderItem example. A valid Order may have from zero to many OrderItems.


How do you map an 1:n association to relational tables?


See the General Forces on page 1.


Insert the owner object’s OID into the dependent objects table. The OID may be represented by a database key or a Synthetic Object Identity.





            select * from Order O, OrderItem I
       where  O.key = ‘YourOrderKey’ and
                    O.key = I.OrderKey

This is faster than filling a container of Smart Pointers (Set<Ref<OrderItem>>) with object identities that have to be dereferenced one by one. For an Order with 20 OrderItems this costs 1 database access for the Order object, 1 multiple read access to get 20 object identities of the dependent objects plus 20 single record read accesses to get the OrderItem one by one.

Related Patterns

In practice an 1:n association is often hard to distinguish from an aggregation. Therefore also consider the aggregation patterns Single Table Aggregation and Foreign Key Aggregation. The later is the same solution for a slightly different problem.

As an alternative to using foreign keys, consider Controlled Redundancy, Denormalization, and Overflow Tables .

The pattern is a close relative of Mapping n:m Associations using Association Tables. See also Representing Object Relationships as Tables [Bro+96].