An associative entity is an element of the entity–relationship model. The database relational model does not offer direct support to many-to-many relationships, even though such relationships happen frequently in normal usage. The solution to this problem is the creation of another table to hold the necessary information for this relationship. This new table is called an associative entity.
To create a relationship, a "child" entity must inherit the primary key of a "parent" entity. However, in a many-to-many relationship, neither entity is the "parent" or the "child"; the relationship is "unresolved". In order to work, these databases require an additional construct to "resolve" the relationship (which is why associative entities are also referred to as "resolving entities").
An associative entity can be thought of as both an entity and a relationship since it encapsulates properties from both. It is a relationship since it is serving to join two or more entities together, but it is also an entity since it may have its own properties. The associative entity must have the primary keys of both adjoining tables as identifiers, but may also contain its own unique identifier and other information about the relationship.
The following guidelines may be used when considering the use of an associative entity:
- All relationships for the associative entity should be many.
- The associative entity could have meaning independent of the other entities.
- The associative entity should not have an additional surrogate key (identity column). The primary key should be a composite of the primary keys from the referenced entities. If the association is temporal, the transaction time may also be part of this composite key. If other entities will be referencing the associative entity and the composite of the referenced primary keys creates a large byte width for the associative entity's primary key, an exception may be made for this rule and an identity column may be added and used as the primary key. In these cases, an alternate key constraint should be implemented on the entity which would be the composite of the referenced primary keys.
- The associative entity may participate in relationships other than the entities of the associated relationship.
- Modern Database Management - 7th Edition - Jeffrey A. Hoffer, Mary B. Prescott, Fred R. McFadden