How to Model a Ternary Relationship in an ER Diagram

When you design a conceptual data model, a ternary relationship can be useful to represent how three entities are related. But eventually, you will have to decompose it into a series of binary relationships, or you won’t be able to implement it in a database.

When we model data, we usually draw rectangles that represent entities and then establish relationships between them, drawing lines that go from one entity to another. The drawings that result are nothing less than entity-relationship diagrams, or ERDs. Those diagrams, which start as a conceptual model and then become a logical model, end up being translated into a physical model. The physical model eventually becomes a database. That’s the “happy path” of data modeling. Unfortunately, the data modeler’s path is not always that happy.

There are situations where a conceptual model cannot be translated directly into a logical representation that effortlessly turns into a database. One situation that commonly complicates the conceptual-logical-physical model transformation is when there are relationships in the conceptual model that cannot be expressed as simple lines linking pairs of entities –e.g. in the case of ternary relationships.

What Is a Ternary Relationship?

We tend to believe that the relationships in an ERD always link pairs of entities – particularly when we spend more time working directly on databases than on ERDs. But the reality is different: there are also relationships between three entities, and even between more than three entities (these are called n-ary relationship types). They are not the most common relationships, but they do exist – so much so that in database theory we talk about the degree of a relationship.

The degree of a relationship indicates the number of entities that are associated with that relationship. If, for example, we have two related entities (such as a relationship between the Book and Author entities) the degree of the relationship is 2.

A relationship of degree 3, where there are three entities involved, is called a ternary relationship. In a data model for an educational institution, for example, we could define a single relationship that associates the entities Subject, Teacher, and Student. The relationship between these three entities provides information about the teaching of certain subjects, making it possible to answer questions such as which students study each subject and which teachers teach it.

Representing a Ternary Relationship in an ERD

The basic way to visually represent a ternary relationship in ERD is to use the classical Chen notation, in which relationships are symbolized by diamonds. (Read this database diagram symbols guide for information on other notations.) Using Chen’s notation, an ERD for the above example of subjects, students, and teachers would look something like this:

A basic conceptual Chen diagram showing a ternary relationship.

To complete the above diagram, we could add attributes to each entity based on a minimal set of functional dependencies. The result would look something like this:

The same diagram, extended with some of the attributes of each entity.

But no matter how complete the diagram is, it is still a conceptual sketch. It will serve to communicate and transmit the ideas that guided the creation of the model. But to give it practical use, we will eventually have to transform it into a physical diagram that can become an operational database.

Implementing Ternary Relationships

The drawback of physical diagrams is that they do not support the occurrence of a ternary relationship in an ER diagram. Therefore, if we want to create a physical diagram from a conceptual diagram with a ternary relationship, we have to do a ternary decomposition – i.e. to transform that ternary relationship and the entities involved in it into a set of entities and binary relationships. For more information on this, read this article on how (and how not) to decompose relations in a data model.

Let’s see how we can carry out the ternary relationship implementation process.

To begin with, we replace the diamond that symbolizes the ternary relationship with another entity. We relate it to the three entities we already had. We must name the new entity in a way that represents a combination of the three related entities. In this case, the combination of a teacher, a subject, and a set of students represents a class. Therefore, we will call the new entity Class.

Now we must think about the cardinality of the relationships of Class with the three remaining entities in our diagram:

• Teacher-Class: A teacher can teach different classes, but each class has only one teacher. Therefore, there is a one-to-many relationship between Teacher and Class.
• Subject-Class: A subject can be taught in several classes, but each class deals with a single subject. Thus, between Subject and Class there is a one-to-many relationship.
• Class-Student: A class can have several students, and each student can attend different classes. So, between Class and Student there is a many-to-many relationship.

The Ternary Decomposition: How to Transform a Ternary Relationship in a Logical Diagram

Based on the previous considerations, we are able to draw a logical ERD that includes the three entities of the ternary relationship and the new entity that relates them.

The logical model derived from a conceptual one with a ternary relationship.

An important detail to keep in mind is that the Class entity has no attributes or unique identifiers. The Vertabelo data modeler warns us of this situation with an error and a warning. The fact of not having attributes is not problematic. The purpose of this entity is only to relate to the other three entities; it does not need its own attributes. But lacking a unique identifier is a flaw that we must solve to avoid integrity or consistency problems once our model becomes a database. To fix this, we must review the requirements that guided our model.

For our example, we assume that there cannot be different classes with the same teacher and the same subject. In other situations, it could happen that the same teacher teaches different classes of the same subject; if that were the case, the Class entity would need an additional attribute that acts as a surrogate key. To avoid making the example too complex, we will not take this situation into account.

For the above, we assume that Class is univocally identified by the combination of Teacher and Subject. Then we must make both relationships dependent, selecting the Dependant property in the Teacher-Class and Subject-Class relationships.

The Ternary Relationship Translated into a Physical Diagram

When we derive a physical diagram from a previously-created logical model, the model undergoes some transformations:

• In the Class table, which is derived from the Class entity, two attributes appear that are both primary and foreign key. These result from the Class-Teacher and Class-Subject
• A new table called Student_Class appears, which is derived from the many-to-many relationship between Class and Student.

The physical model, derived automatically from our logical implementation.

We could change the attribute names automatically generated by Vertabelo to more synthetic and meaningful names,  making our model neater. I leave that to you as a homework assignment.

We are now in a position to ask Vertabelo for another small favor: to generate the SQL scripts that will allow us to create a database that meets all the requirements of our data model.

The Ternary Conversion Process at a Glance

Let’s review the process we went through to convert a model with a ternary relationship into a working database:

1. We needed to build a data model to store information about an educational institution's courses. We discovered that in this data model there were three entities (Subject, Teacher and Student) and that between these three entities there was a ternary relationship.
2. We designed an ERD using Chen's notation, which allows drawing ternary relationships.
3. We created a logical diagram converting the ternary relationship into an entity related to the other three entities. We defined the Dependant property on two of these relationships so that the new entity would use them as the primary identifier.
4. We automatically created a physical diagram. The Class entity became a table with two attributes that form a primary key and are at the same time foreign keys. Instead of the many-to-many relationship, a table with one-to-many relationships with the two related tables appeared.

From the moment we converted the conceptual model into a logical model and the ternary relationship became an entity, we had to take into account functional requirements when making certain design decisions. In our example, the new Class entity is identified through its relationships with Teacher and Subject; it has a many-to-many relationship with Student. This is because we considered that a teacher can teach different subjects. But if the requirements were different – for example, if each teacher taught a single subject – then the primary identifier of Class would be given only by the relationship with Teacher. Class would have a non-identifying one-to-many relationship with Subject.

In conclusion, the key to building the ideal data model for a ternary relationship is to correctly decide the properties and cardinality of each of the three relationships that are established in the central entity and the three related entities.

The Real Usefulness of Ternary Relationships

As we have seen in the example developed throughout this article, ternary relationships are useful to delineate a conceptual ERD when we have three entities that are somehow related to each other. But three-way relations always hide some complication; as the saying goes, two’s company and three’s a crowd. These complications arise when the conceptual ERD is downgraded to the detail of a logical ERD and then to that of a physical ERD, since ternary relationships must necessarily be decomposed into binary relationships.