Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Practical PHP Patterns: Association Table

DZone's Guide to

Practical PHP Patterns: Association Table

· Web Dev Zone ·
Free Resource

Jumpstart your Angular applications with Indigo.Design, a unified platform for visual design, UX prototyping, code generation, and app development.

The Association Table pattern is a classical way to express an association between two entities in a relational model, and by extension a way to map the set of pointers (handlers) between two objects over an additional table. In this pattern's implementations, a single association between two objects is represented as a row of a relational table which imports keys from the original two tables.

This pattern is commonly used for many-to-many relationships, but it is not strictly limited to this kind of multiplicity, especially in the case of an ORM where simplifying the translation between the relational and object models is crucial.

The reason many-to-many relationships are prone to be mapped into an Association Table resides in the constraint of representing atomic values into fields in relatonal databases. While an object can keep an arbitrary number of references to related collaborators because the occupied space is allocated dynamically, duplication of columns is strictly forbidden in a relational database. The use of a foreign key is limited to one-to-many relationship, and the only way to represent a many-to-many one is to add an external table.

Implementation

An useful metaphor for this pattern is a data structure commonly used to represent a graph. In this case, the graph is a bipartitioned graph, because it is divided into two different classes of objects, and the data structure is an adjacency list, which contains every instance of the association. The underlying implementation of tables is however dbms-specific, and does not correspond to a simple adjacency list since this is only the logical model presented to clients (and without a predefined ordering).

When implementing a Data Mapper, there is no domain object in the graph that stands for a row in the Association Table, so this is another example of the advancement from the Active Record pattern (which would have a UserGroup or similar object to link between User and Group ones).

Only in particular cases the intermediate row is promoted to a first-class domain object, such as a Subscription object with references to a User and Group ones. Typically this occurs because the association has fields that are not mapped in the object graph but are present as columns in the relational table.

In entity/relationship diagrams associations can have fields, but in object models you need a data structure different from a simple list of pointer/handlers to store the fields. No one ever said that classes should only correspond to entities of a model, and there is an equivalence between an association and an association entity, thus introducing a new object with one-to-many relationships towards the original ones is not incorrect.

The relational Association Table usually has foreign keys to the entity tables, so that its primary key is compound and consists in the combination of these columns. If more than two entities are involved, of course a foreign key for each of them is needed.

Unidirectional one-to-many associations

The particular use case that makes an Association Table useful for more than many-to-many relationships is a unidirectional one-to-many association, when a foreign key on the many side is a stretch. This is the case because the association is only navigable in one direction. The many side may be a simple entity, which shouldn't have foreign keys for all the different entities that reference it via unidirectional associations; this is the point of unidirectionality: do not introduce coupling on the target side, which is composed or aggregated by the source side without it having any knowledge of the process. However, with classical one-to-many relationship mapping, its relational representation comprehends a foreign key for each source. You won't see the foreign keys in the object model, however, if the Data Mapper is built right, but the unidirectionality will be lost as an object reference from the many side must be present for the ORM to work.

As an example, consider an Article object, which can be referred by many different entities, like a Section, a Category, an ArticleCollection and so on. The Article table will be populated by fields like section_id, category_id and collection_id, thus the easy way to maintain real unidirectionality and solve the object-relational impedance mismatch is introducing an additional AssociationTable.

In this case the constraints should be enforced by the Data Mapper, since this mapping leaves open the possibility of multiple sources sharing the same target, which would transform the association in a many-to-many one. This can be an advantage: if the association may transition to a many-to-many in the future, there will be no data migration to perform. If in development the multiplicity is unclear and you must deploy a schema, when in doubt you can map to an Association Table and retain every option.

Actually if you follow any relational database-related university course, it will teach you that mapping an association to its own table is the norm, and one-to-one and one-to-many have different kinds of simplifications available to avoid creating an external table.

Example

The sample code is taken from the Doctrine 2 test suite and manual, and it shows hot to map many-to-many and one-to-many relationships on Association Tables (via annotations parsed by the ORM). As always I've cut every non-relevant code to show the relevant part.

<?php

namespace Doctrine\Tests\Models\CMS;

use Doctrine\Common\Collections\ArrayCollection;

// Many-to-many, bidirectional

/**
* @Entity
* @Table(name="cms_users")
*/
class CmsUser
{
/**
* @ManyToMany(targetEntity="CmsGroup", inversedBy="users", cascade={"persist"})
* @JoinTable(name="cms_users_groups",
* joinColumns={@JoinColumn(name="user_id", referencedColumnName="id")},
* inverseJoinColumns={@JoinColumn(name="group_id", referencedColumnName="id")}
* )
*/
public $groups;

public function __construct() {
$this->groups = new ArrayCollection;
}

public function addGroup(CmsGroup $group) {
$this->groups[] = $group;
$group->addUser($this);
}

public function getGroups() {
return $this->groups;
}
}

/**
* Description of CmsGroup
*
* @author robo
* @Entity
* @Table(name="cms_groups")
*/
class CmsGroup
{
/**
* @ManyToMany(targetEntity="CmsUser", mappedBy="groups")
*/
public $users;

public function addUser(CmsUser $user) {
$this->users[] = $user;
}

public function getUsers() {
return $this->users;
}
}

// One-to-many, unidirectional

/** @Entity */
class User
{
// ...

/**
* @ManyToMany(targetEntity="Phonenumber")
* @JoinTable(name="users_phonenumbers",
* joinColumns={@JoinColumn(name="user_id", referencedColumnName="id")},
* inverseJoinColumns={@JoinColumn(name="phonenumber_id", referencedColumnName="id", unique=true)}
* )
*/
private $phonenumbers;

// ...
}

/** @Entity */
class Phonenumber
{
// ...
}

Take a look at an Indigo.Design sample application to learn more about how apps are created with design to code software.

Topics:

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}