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:
- Create a separate
dept_assignmenttable with the following attributes:assignment_idfaculty_iddepartment_idassignment_date
- Add a foreign key column to the
Facultytable that references thedept_assignmenttable 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:
- Does this relationship model make sense?
- Is the relationship cardinality correct?
- 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