Redundant column to maintain data integrity

manpreet Best Answer 3 years ago

Consider the following example:

Syllabus
-id 
-course_id 
FOREIGN KEY (course_id) REFERENCES Course(id)   

SyllabusItem 
-id
-syllabus_id 
FOREIGN KEY (syllabus_id) REFERENCES Syllabus(id)   

Course 
-id   

Class 
-id 
-course_id 
-syllabus_item_id 
-syllabus_id <--- redundant column 
FOREIGN KEY (course_id, syllabus_id) REFERENCES Syllabus (course_id, id)
FOREIGN KEY (syllabus_item_id, syllabus_id) REFERENCES SyllabusItem (id, syllabus_id)

The second foreign key constraint is used to ensure the Syllabus Item belongs to the Syllabus, which belongs to the Course the Class belongs to. But in order to maintain this relationship, we have to add a redundant column syllabus_id to Class.

Is there a more efficient way to achieve data integrity in this example?