I am trying to design a (part of a) database which has to accomplish the following:
- There is a
studentstable, containing a bunch of students.
- There are educations in the database.
- Each student can have 0..n educations.
- There are x different types of educations, in which x is small (<10) and known in advance.
- The different types of education all have the same type of data associated with it (location, name etc.)
- Some more data is associated with a student-education connection (e.g. grades, start date etc.).
- The type of data which is associated with a student-education connection depends on the type of education (e.g. a masters degree has a specialization, a course does not).
I try to create a good database design to represent this data, however there are quite a few difficulties. A design I came up with is as followed:
Studenttable, which contains student data
Educationtable, which contains data of educations. There is a type column to specify the type.
Student_Educationjunction table, which links students with educations.
<type>Educationtables which will contain data associated with student-education connections (thus a FK to
Student_Educationis always present). Each education type will have it's own table.
However, there is a problem with this design: a
<type>Education row should only be allowed to reference a
Student_Education connection when the education type matches. E.g. a
MasterEducation row can only reference a row in the
Student_Education table that references a row in the
Education table with type == master.
Would it be possible to add a constraint which can check exactly that?
If not, what other options are available?