Postgresql constrains on FK

Question

I am trying to design a (part of a) database which has to accomplish the following:

• There is a students table, 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:

• Student table, which contains student data
• Education table, which contains data of educations. There is a type column to specify the type.
• Student_Education junction table, which links students with educations.
• x <type>Education tables which will contain data associated with student-education connections (thus a FK to Student_Education is 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?

Answer 1

From a design perspective, why would the Student_Education table be needed at all?

The information that a specific student follows a certain type of education is implied by the existence of the corresponding row in the <type>Education table.

If Student_Education's reason for being is for SQL joins, it could be a view returning the union of all the <type>Education tables.

As for the foreign keys to Education, to be sure that each entry in <type>Education can only refer to its corresponding type, I can see two options:

1. As @hypercube suggests in the comments, add a <type>Education.educ_type column with a fixed value and a table-level foreign key: FOREIGN KEY (educ_id,educ_type) REFERENCES Education(unique_id,type)

2. Split Education into as many subtables as they are education types, and have column-level foreign keys from each <type>Education.educ_id to the corresponding subtable. It's also the proper design choice if the Education table should be expanded in the future with columns that are specific to certain education types. Education could also be turned into a view as the union of the subtables, at least for the common columns.

Answer 2

Why not place the data that is relevant to the Student_education connection into that table ? Is there vast difference in the data being kept for each education type ? I'd think it would be mostly the same. Won't the following model work ?

CREATE TABLE Education_Types 
    (
     id integer primary key, 
     name varchar(20), 
     details varchar(30)
    );

CREATE TABLE Educations
    (
     id integer primary key, 
     name varchar(20), 
     education_type_id integer references Education_Types(id),
     details varchar(30)
    );

CREATE TABLE Students
(
 id integer primary key,
 first_name Varchar(1000),
 last_name varchar(1000),
 gender char(1),
 SSN varchar(100)
);


CREATE TABLE Student_Education
(
 id integer primary key,
 student_id integer references Students (id),
 educationId integer references Educations (id),
 grade_point_avg decimal,
 detail_1 varchar(1000) NULL,
 detail_2 varchar(1000) NULL,
 detail_3 varchar(1000) NULL,
 detail_4 varchar(1000) NULL,
 detail_5 varchar(1000) NULL,
 start_date date,
 end_date date
);