Database Design – Architecting Table with Multiple Paths to Common Parent Tables

database-design

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.

Best Answer

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.

Related Question