Ternary Relationships and Relationship Attributes

Relationship attributes

Relationships can carry attributes of their own — properties that belong to the association, not to either participating entity. In an ER diagram, relationship attributes are drawn as ovals connected to the relationship diamond.

Consider the Acted_In relationship between Person and Movie:

The attribute role describes the character an actor played — it belongs to the Acted_In relationship, not to the Person (a person can play many roles) nor to the Movie (a movie has many roles).

The modelling problem

A binary relationship with a simple attribute like role works well when an actor plays a single role in a given film. But what about Jennifer Lawrence playing both Raven and Mystique in X-Men: First Class?

The straightforward solution — storing multiple roles as a comma-separated string — is a multi-valued attribute. This violates the First Normal Form (1NF) rule of atomicity: each attribute value should be a single, indivisible value. Text processing at that level (parsing comma-separated lists in SQL) is an unspeakable data modelling sin. Queries become fragile, indexing is impossible, and aggregate operations collapse.

Ternary relationships

One solution is to promote role to a full entity and make Acted_In a ternary relationship:

This cleanly models the situation: Jennifer Lawrence has two distinct Acted_In instances — one linking her to X-Men: First Class via the role “Raven”, another via the role “Mystique”.

But this raises a philosophical question: is a “role” a real-world entity in its own right? The answer depends on the application. A casting agency might reasonably model Role as an independent entity with its own attributes (character name, age range, gender, whether it requires a specific accent). A simple film database might prefer to keep the model lighter.

Decomposition into binary relationships

An alternative is to decompose the ternary relationship into binary relationships using an artificial entity (sometimes called a reifying entity or an associative entity):

Casting is an artificial entity created purely to hold the relationship data. It has a synthetic key and carries the role attribute. This approach avoids ternary relationships altogether, at the cost of introducing an extra entity.

The textbook (Lemahieu 3.2.6) discusses a risk with this decomposition: data loss. When you decompose an $n$-ary relationship into binary relationships, you may lose the ability to enforce the constraint that a particular combination of entities must be unique. Two binary relationships can independently record the same pairings without detecting a duplicate ternary instance.

Attribute or entity?

Whether to model a concept as an attribute or as an entity connected by a new relationship depends on the scope of the data model. Consider ReleaseDate:

• If every movie has at most one release date (e.g., a local cinema database), a simple attribute on Movie works well.
• If the scope is global, movies have different release dates in different countries (UK: 24 November, US: 22 November, Japan: 15 December). In that case, ReleaseDate might need to be an entity related to both Movie and Country:

The guiding principle: when a property can have multiple values per entity, or when it has meaningful relationships of its own, it should be modelled as an entity. When it is a simple, single-valued fact about an entity in the current scope, an attribute suffices.

Summary

• Relationships can carry attributes that belong to the association, not to either participant.
• Multi-valued attributes violate 1NF and lead to fragile, unindexable queries.
• Ternary relationships (or decomposed binary relationships with artificial entities) solve the multi-valued attribute problem.
• Decomposing an $n$-ary relationship into binary relationships may introduce data loss — the inability to enforce uniqueness of combined entity instances.
• The choice between “attribute” and “entity” depends on scope: single-valued and simple is an attribute; multi-valued or independently meaningful is an entity.