Associative entities (resolving M:N)

A relationship that needs to be an entity

Sometimes an association is more than a line — it has its own attributes and its own identity. That is an associative entity: *an entity type that associates the instances of one or more entity types and contains attributes peculiar to the relationship between those instances*. It is, literally, a relationship promoted to a box. The classic trigger is a many-to-many binary relationship that carries data of its own — STUDENT *Registers For* COURSE where each registration has a *Grade* and a *Date*; the Grade belongs to neither STUDENT nor COURSE alone, but to their *pairing*.

Why must M:N relationships in general become associative entities (or at least associative structures)? Because a pure many-to-many relationship cannot be implemented directly in relational tables — you resolve it in logical design into an intersection structure. When it also has attributes, modeling it as an associative entity up front is cleaner.

The four conditions for converting to an associative entity

Convert a relationship to an associative entity when all four hold:

1. All the participating relationships are "many." (You are resolving an M:N, not a 1:M.)
2. The resulting entity has independent meaning to users — ideally a single-attribute identifier of its own (a *gerund* noun derived from the verb: *Completes* → CERTIFICATE; *Registers* → REGISTRATION).
3. It has one or more attributes beyond its identifier. *(This is the most common real-world trigger — the moment you want to hang an attribute on a relationship line, convert it.)*
4. It participates in other relationships independently of the entities it links.

Identifier handling — and how the cardinality "moves"

An associative entity can take a single-attribute surrogate identifier of its own (CERTIFICATE → *Certificate Number*) or a composite identifier built from the identifiers of the entities it links (plus a discriminator like a date when the same pair can recur). When you convert, the crow's feet move: the original M:N between the two strong entities becomes two one-to-many relationships that both terminate at the *associative entity*.

Worked example — Completes → CERTIFICATE

Employees complete training courses, and the company issues a numbered certificate for each completion. Start with the M:N relationship EMPLOYEE *Completes* COURSE. Apply the four conditions: both ends are many ✔; *CERTIFICATE* has independent meaning (it is an issued document) with its own identifier *Certificate Number* ✔; it has the attribute *Date Completed* beyond its identifier ✔; it could participate in further relationships ✔. So promote *Completes* to the associative entity CERTIFICATE.

After conversion: EMPLOYEE has a one-to-many relationship to CERTIFICATE, and COURSE has a one-to-many relationship to CERTIFICATE — and each certificate is linked to exactly one employee and exactly one course (mandatory one on those ends). The crow's foot that used to sit on EMPLOYEE and COURSE now sits on CERTIFICATE: one employee may hold many certificates, one course may generate many certificates, but each certificate ties one employee to one course and carries the Date Completed. The relationship became a box because it grew an identity and an attribute of its own.