Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Yes, add the EducationType column in the Student_Education table and modify the FK to be: (EducationType, EducationID) REFERENCES Education (EducationType, EducationID). Youll also need a respective unique index in the Education table as well. –  ypercube Jun 4 '13 at 18:09
    
@ypercube - But that still doesn't add the correct constraints on the <type>Education tables, does it? E.g. it would still be possible to create row in MasterEducation which references a row in Student_Education whose type is bachelor. I try to achieve a FK like ('master', EducationID) REFERENCES Education (EducationType, EducationID). Unfortunately such a foreign key does not work. –  Tiddo Jun 4 '13 at 21:05
    
The <type>Education tables 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. –  ypercube Jun 4 '13 at 21:19
    
I think this is the best solution so far, even though this is still not an ideal solution. Nullable columns is not an option because it simply does not solve the problem at all. This part of the database has to be done right, it is a crucial part of the application and inconsistencies are not acceptable here. Another option might be to use triggers, but I'm not sure yet which of these 2 options I like best. Anyway, thanks for your help! –  Tiddo Jun 4 '13 at 21:36
add comment

2 Answers

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.

share|improve this answer
    
I introduced the Student_Education table since many fields are common to each of the <type>Education tables. However, I just realize that I could also use inheritance to make sure each of the <type>Education tables would have those columns, without having to specify them more than once. Dropping the Student_Education table certainly makes things a lot simpler. I think I prefer your second solution most, it makes a lot of sense to create different tables for each education type. Thanks for your advice! –  Tiddo Jun 5 '13 at 17:45
add comment

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
);
share|improve this answer
    
This is exactly the situation I try to avoid. If I add all the details to the Student_Education table I will have quite a lot of unused columns for each row. Also, this still doesn't solve the problem at all: it is still possible to add rows which uses the details columns for type X and references to an education of type Y. Moreover, moving all detail columns to the Student_Education table makes it impossible to create NOT NULL details columns. –  Tiddo Jun 4 '13 at 21:14
1  
Why do you have a problem with columns having NULL values? In this case they seem to do the trick quite nicely, even though perhaps in some strict sense of the theory, the table isn't normalized. However that's only true if you regard NULLs as actual values. Anyway, by using the structure mentioned above, you can always place a check constraint on the educationId (getting the type) and detail columns, forcing your mentioned business rules about which details follow which education type –  druzin Jun 4 '13 at 22:15
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.