Back to articles list
When we design a database, we draw an entity relationship diagram (ERD). It helps us understand what kind of information we want to store and what kind of relationships there are. It is imperative that this diagram is easy to read and understand.
The number of entities in a relationship is the arity of this relationship. The aim of this article is to give some examples and show how big an impact the arity of relationships has on not only the readability of the diagram, but also the database itself.
In many cases it’s up to the developer to determine what kind of relationships to use to model real-life situations.
The most common types of relationships are:
- Unary (one entity is invloved in the relationship).
- Binary (two entities are involved in the relationship).
- Ternary (three entities are involved in the relationship)
- N-ary (n entities involved in the relationship)
Let’s discuss some examples of each type.
Unary relationship type
A Unary relationship between entities in a single entity type is presented on the picture below.
As we see, a person can be in the relationship with another person, such as:
- A woman who can be someone’s mother
- A person that is a someone’s child
- A teacher who teaches student
Here is how it can be modelled in the entity relationship diagram:
↑ Click on a logo to open the model in Vertabelo | Download the model as a png file
Binary relationship type
A Binary relationship type is shown in the simple example below. This is definetly the most used relationship type.
Journalist writes an article.
↑ Click on a logo to open the model in Vertabelo | Download the model as a png file
Additional simple examples: employee manages department, teacher conduct class, and many more.
Ternary relationship type
Let’s present some information in a database diagram that shows how we want to store the time and date and in which classroom a group takes classess.
This example can be implemented very easily.
In the diagram below, we represent our ternary relationship with an extra table, which can be modelled in Vertabelo very quickly.
As you see, in the intersection table class_in_classroom
- Primary key doesn’t contain all foreign keys. Primary key consists only of group_id and class_id. Therefore, it’s possible to specify only one classrom for a pair: group and course. In other words, a group can have specific classess only at one classrom.
- The same classes for different groups could be conducted in different time
- Primary key provides that each group has classess only once and only in one classroom
- Unique key (group_id, meeting_date) provides that the group in the specific day and time has only one class in one classroom
- Unique key (classroom_id, meeting_date) provides that in the specific time, only one class could be conducted in the specific classroom
↑ Click on a logo to open the model in Vertabelo | Download the model as a png file
It is important to note that meeting_date in an Oracle database has the ‘date’ type, which describes both date and time.
Sometimes it is possible to replace a ternary or n-ary relationship by a collection of binary relationship connecting pairs of the original entities. However, in many cases it is hard to replace ternary relationship with two or more binary relationships because some information could be lost.
Another ternary relationship presents a different situation – Teacher recommends a book for a class:
Let’s take a closer look at the primary key. In the example with groups and classes, the primary key consisted only of two foreign keys. This meant that there could be only one classroom for a specific group and class. In this situation the primary key consists of all three foreign keys. It makes a significant difference, because in this case it is possible to recommend more than one book for a specific course by one teacher.
↑ Click on a logo to open the model in Vertabelo | Download the model as a png file
First attempt to replace a ternary relationship
As you can see in the diagram above, the teacher recommends a book and a book is used in a specific class. However, it doesn’t imply that the teacher recommended that book for that course. It is possible that the teacher recommended the book for another class.
↑ Click on a logo to open the model in Vertabelo | Download the model as a png file
Second attempt to replace a ternary relationship
Now we know which book the teacher recommend and which class he conducts. It still doesn’t imply that he recommends the book for this class.
↑ Click on a logo to open the model in Vertabelo | Download the model as a png file
Third attempt to replace a ternary relationship
It is possible to identify which teacher conducts a specific class and the books recommended for that class. Still, we don’t have information on who recommends the book for that class.
↑ Click on a logo to open the model in Vertabelo | Download the model as a png file
Fourth attempt to replace a ternary relationship
This situation is similar to the previous one. We can tell who recommends the book and which book is used in which class. Unfortunately, we lack information on who exactly recommended that book for that class; it could have been done recommended by another teacher.
↑ Click on a logo to open the model in Vertabelo | Download the model as a png file
As we see, sometimes it is hard to replace a ternary relationship. The appropiate diagram is ternary, where it is possible to tell who reccomended a book for a specific class.
