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.