Pattern: Association Table


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


As an example we use the n:m association between an Employee object type and a Department object type. An Employee can work for more than one department. A department usually comprises more than one Employee.


How do you map n:m associations to relational tables?


See the General Forces


Create a separate table containing the Object Identifiers (or Foreign Keys) of the two object types participating in the association. Map the rest of the two object types to tables using any other suitable mapping patterns presented in this paper.




The consequences are analogous to Foreign Key Association only adapted to the slightly different context. Hence we do not repeat them here.


            select * from DepartmentTable D, EmployeeDepartmentTable ED,
              EmployeeTable E
       where  D.SyntheticOID = ‘YourDepartment’
                    D.SyntheticOID = ED.DepartmentKey and
                    ED.EmployeeKey = E.SyntheticOID

This is faster than filling a container of Smart Pointers (Set<Ref<Employee>>) with object identities that have to be dereferenced one by one. The same discussion with slightly different arguments could be found in Foreign Key Association.

Related Patterns

The pattern is closely related to Foreign Key Association. See also Representing Object Relationships as Tables [Bro+96].