Designing a table with multiple null FKs

Posted on

Question :

Let’s suppose that I have a table Student and a table Professor.
I would like to add a 3rd table Evaluation that holds evaluations both for Students and Professors. Would it be considered a bad practice, to create an Evaluations table with 2 foreign keys, point to StudentID and ProfessorID and on each evaluation record to have only one of them set and the other to be null?

Now this is an oversimplified example of something different, so I don’t want answers on how to redesign the previous example.

I just want an opinion on the logic behind the previous example. Does having a table with multiple FKs, from which only one is set and the others are null, sound like a bad design choice?


Answer :

You might want to add a course table? A student could conceivably take more than one course with the same professor (lecturer might be a better term, all professors are lecturers but not all those who lecture are tenured professors! Also, you might need to add a semester field as well?

CREATE TABLE evaluation 
  eval_student_id INTEGER NOT NULL,
  eval_lecturer_id INTEGER NOT NULL,
  eval_course_id   INTEGER NOT NULL,
  eval_semester_id INTEGER NOT NULL,
  CONSTRAINT evaluation_uq UNIQUE (eval_id, eval_student_id, eval_lecturer_id, eval_course_id, eval_semester_id) 
  -- the UNIQUE constraint could be your PRIMARY KEY, but JOINs would be horrible
  CONSTRAINT eval_student_fk FOREIGN KEY (eval_student_id) REFERENCES student (student_id),

  -- &c... for the other fields FOREIGN KEYs


(I can’t be more specific about data types since you haven’t given your particular RDBMS – in the systems that I’ve work with, a FOREIGN KEY field must be a PRIMARY KEY field in the referenced table.

I’m reluctant to say never, but I would avoid NULLs whenever possible and particularly in this case! p.s. welcome to the forum!

Leave a Reply

Your email address will not be published. Required fields are marked *