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?
EducationTypecolumn in the
Student_Educationtable and modify the FK to be:
(EducationType, EducationID) REFERENCES Education (EducationType, EducationID). Youll also need a respective unique index in the
Educationtable as well.
<type>Educationtables, does it? E.g. it would still be possible to create row in
MasterEducationwhich references a row in
Student_Educationwhose type is bachelor. I try to achieve a FK like
('master', EducationID) REFERENCES Education (EducationType, EducationID). Unfortunately such a foreign key does not work.
<type>Educationtables would also have to be altered, each one with an addition of an
EducationType DEFAULT 'Master'column and a
CHECK (EducationType = 'Master) constraint. It seems cumbersome (and you would probably use a smallint for the type column instead of varchar, for efficiency.) Whether it has value to use this, depends on how complex the underlying structure you have is (and how much different the structure of the tables under the various types is.) For simple design, it may be better to have common tables, with nullable columns, as @druzin's answer.