Search code examples
databaseentity-relationship

Ternary and binary ER relationships

I'm having trouble understanding the difference between a ternary relationship like this :

enter image description here

and multiple binary relationships like this (if that's right) : enter image description here

From what I've been reading I think it has to do something with constraints? If someone can explain the difference to me clearly I would greatly appreciate it.

Solution

  • A ternary relationship is a single relationship between 3 elements, e.g.:

    enrollment (student PK, subject PK, teacher PK)

    In this case, students who enroll for a particular subject must register with a teacher who teaches that subject. Teachers could teach multiple subjects and subjects could be taught by multiple teachers, so we can't derive either from the other.

    A different way of modeling this situation might be to define classes. Each class belongs to a particular subject and teacher and is used to enroll students, e.g.:

    class_teacher (class PK, teacher)
class_subject (class PK, subject)
class_student (class PK, student PK)

    Thus, the ternary relation has been decomposed into 3 binary relations. This isn't equivalent to the enrollment relation above - previously we couldn't relate subjects to teachers without including students.

    In some cases it may be useful to decompose a relation like that, in other cases not. It's up to you as data modeler to decide how best to describe a situation.