How should I model department assignment history for faculty members in a relational database?

I have two entities, Faculty and Department, with primary keys faculty_id and department_id, respectively.

My database needs to satisfy the following requirements:

  • Every faculty member belongs to exactly one department.
  • Faculty members may change departments during their careers.
  • The university wants to preserve the history of department assignments.

To implement this, I came up with the following approach:

  1. Create a separate dept_assignment table with the following attributes:
    • assignment_id
    • faculty_id
    • department_id
    • assignment_date
  2. Add a foreign key column to the Faculty table that references the dept_assignment table to indicate the faculty member's current department assignment.

My understanding is that this introduces a ternary relationship, although I am not sure if that is correct.

Faculty --n-- belongs to --1-- Department
                  |
                  |
                  | n
          Dept_Assignment

My questions are:

  1. Does this relationship model make sense?
  2. Is the relationship cardinality correct?
  3. Is this a good approach for preserving department assignment history, or is there a better way to model it in a relational database?

Posted Jul 29, 2026, 6:58 AM UTC

03
Loading comments…