1

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?

  • I would remove syllabus_item_id from Class. Class could join with Syllabus, and Syllabus join to SyllabusItem. This would be third-normal form (3NF), which is conventional design for transactional DBs. A de-normalized version would be to combine Syllabus and SyllabusItem. This is often used in data warehouses. – Victor Di Leo Aug 29 '18 at 21:12
  • If SyllabusItem applies to Class directly, I would remove syllabus_id from Class. You could add a trigger to check that the syllabus_id for your SyllabusItem matches the syllabus_id for your Course.. – RDFozz Aug 29 '18 at 21:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.