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.
General Performance: If performance turns out insufficient you might use database optimization patterns like Controlled Redundancy, Denormalization, or Overflow Tables. In our case of a symmetric n:m association things get slightly more complicated as you have to break symmetry in order to apply performance optimizations.
Prefetching objects: In case you know in advance, that you need access to all dependent objects (like Employees of a Department) for most use cases, get all data using a join operation and construct the Department object and the Employee objects from the result of a single database operation like:
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.
The pattern is closely related to Foreign Key Association. See also Representing Object Relationships as Tables [Bro+96].