Architecting table with multiple paths to common parent tables

Question

I'm wondering how to architect a database when there are multiple paths (via foreign keys) to a common parent table.

For example I have the following tables:

Table: Students
PK StudentId

Table: Classes
PK: ClassId

Table: ClassStudents
PK: ClassStudentId:
FK: StudentId
FK: ClassId

Table: ClassAssignments
PK: ClassAssignmentId
FK: ClassId
FK: Assignment

Now I'm going to create a new table that store Assignments submissions from each student. What's the best way to structure it such that a student from another class can't get added to an assignment from this class?

TABLE: ClassStudentAssignments
FK: ClassAssignmentId
FK: ClassStudentId

Is there a way to handle this in the database design or would it just have to be done via the business logic?

Thanks in advance for any help.

Answer 1

Student (student_id) exists.

student {student_id}
     PK {student_id}

Class (class_id) exists.

class {class_id}
   PK {class_id}

Student (student_id) attends class (class_id).

student_class {student_id, class_id}
           PK {student_id, class_id}
          FK1 {student_id} REFERENCES student {student_id}
          FK2 {class_id}   REFERENCES class {class_id} 

Task (task_id) exists.

task {task_id}  
  PK {task_id}

Class (class_id) is assigned task (task_id).

assignment {class_id, task_id}
        PK {class_id, task_id}      
       FK1 {class_id} REFERENCES class {class_id}
       FK2 {task_id}  REFERENCES task {task_id}  

Student (student_id) attending class (class_id) submitted solution to the task (task_id), assigned to the class.

solution {student_id, class_id, task_id}
      PK {student_id, class_id, task_id}
     FK1 {student_id, class_id} REFERENCES student_class {student_id, class_id}
     FK2 {class_id, task_id}    REFERENCES assignment {class_id, task_id}

Note:

... when there are multiple paths (via foreign keys) to a common parent table ...

This is the root cause of the problem. There is no such thing in relational model; foreign keys are constraints, not navigation paths. You are dragging OO terminology into a relational DB and these two do not match -- paradigms are different.

Answer 2

You are thinking about this incorrectly. An assignment submission from a student not enrolled in the corresponding course, if it happens to get marked, is not fundamentally different from a properly submitted and marked submission from a student who subsequently drops the course. Allow the entry of the mark to exist in the database, by noting that when the final report of assignment marks for each course is generated, the (inner) join will fail for all submissions for students not enrolled in the course at exam time. That is sufficient to properly implement the business logic.