I'm in the midst of learning SQL and have a question regarding storing previous many-to-many relations.
I have a student entity and a tutor entity that are coupled together with a associative table. If a student were to switch tutors, I want to store the previous tutors of the student.
My current solution is with another associative table, however I'm wondering if there are better solutions to this problem?
It depends on the complete situation. Typically it's simpler and more efficient to keep all associations in a single associative table. Like this:
CREATE TABLE works_with (
student_id int NOT NULL REFERENCES students
, tutor_id int NOT NULL REFERENCES tutors
, start_date date NOT NULL
, end_date date
, termination_reason text
, PRIMARY KEY (student_id, tutor_id, start_date)
, CONSTRAINT end_not_before_start
CHECK (end_date >= start_date)
, CONSTRAINT
CHECK (end_date IS NOT NULL OR termination_reason IS NULL)
, CONSTRAINT no_overlapping_date_range
EXCLUDE USING gist (student_id with =, tutor_id with =, daterange(start_date, end_date) WITH &&)
);
The PK includes start_date now, as the same student & tutor might work together repeatedly over time. Since the exclusion constraint (see below) prevents overlapping entries anyway, you might as well use an additional surrogate PK column (like a serial or IDENTITY column) as well. May be more efficient.
The check constraint end_not_before_start forbids end before start (while still allowing end_date IS NULL.
The check constraint termination_reason_only_if_terminated only allows a value in termination_reason where the association has been terminated.
The exclusion constraint no_overlapping_date_range disallows overlapping date ranges per student-tutor combination. (You may or may not want that.)
Requires the additional module btree_gist. Install once per database:
CREATE EXTENSION btree_gist; -- once per db
See:
