Can three tables share a relationship in a database?Here is the first set of relationships:+------------+ +------------+|Students | |StudentYears| +------------++------------+ +------------+ |Years ||*StudentID |(1)-----(M)|*StudentID | +------------+| StudentName| |*Year |(M)------(1)|*Year |+------------+ +------------+ +------------+*-Primary key
This allows me to assign a student to the years he or she attended the school. Only one student-year combination possible.Here's the second relationship: +------------++------------+ |ClassEvents ||Classes | +------------+ +------------++------------+ |*EventID | |Years ||*ClassID |(1)-----(M)| ClassID | +------------+| ClassName | | Year |(M)------(1)|*Year |+------------+ +------------+ +------------+*-Primary key
Able to set up different classes for different years and even have the same class held multiple times during the same year, etc.Finally, to allow for a class roster I have this relationship:+------------+ +------------+|ClassEvents | |ClassRoster | +------------++------------+ +------------+ |Students ||*EventID |(1)-----(M)|*EventID | +------------+| Year | |*StudentID |(M)-----(1)|*StudentID |+------------+ +------------+ | StudentName| +------------+*-Primary key
This does allow for the student to be enrolled in two of the same classes in the same year, but not the same class event. I want this.Now, the problem I am having right now is nothing prevents me from scheduling a student from 1967 in a class from 2001. Is there a way to link the year from the StudentYears table to the year in the ClassEvents table and also link the StudentID from the StudentYears table to the StudentID in the ClassRoster table? All I know of is binary relationships that link two tables, but not three.Any thoughts?Maxwell 
Database relationships question
Posted - 2001-11-13 : 23:42:38