1

I have a table of "interviews" and "interview reviews" for both the interviewer and the interviewee. When the project was started there was only the need for one type of review, which is shown below

enter image description here

But now we have added a new type of interview which would require different types of reviews based in the interview type. I have added a many to many (M:N) relationship to store the interview type, but I am stumped as to the best way to go about adding multiple review types...

enter image description here

One solution I thought of was to add another table for reviews with the many to one (M:1) relationship which would contain all the data for the new review type. This would mean that the application would have to conditionally look for the review based on the type of interview, and it would lead to blank columns for all the unused review types...

How would you go about structuring this?

|improve this question
  • How can one interview have multiple types? To me a type describes what a thing is, and it cannot be two different things at once. The policeman either interviews the suspect or the witness? – Caius Jard Sep 13 '18 at 3:52
  • (A better example might be "HR either conduct a phone interview or a facetoface interview) – Caius Jard Sep 13 '18 at 4:01
  • You have unjustifiably divided one entity 'review' into two tables I think. I'd recommend rethink your structure and maybe join two tables back into one table. – Akina Sep 13 '18 at 5:27
  • @Caius Jard you are right, I hastily made that a M2M relation when the interview should just have a relation back to its type... But the question about how to handle the review relation (with different review types dependent on interview type) still stands. How would you go about that? – deltaskelta Sep 13 '18 at 5:53
  • So, Interview has a TypeID, decoded elsewhere. One Interview has many Reviews?. Review has TypeID. ReviewType is restricted according to InterviewType. Do Many InterviewTypes have Many ReviewTypes ? – Caius Jard Sep 13 '18 at 9:34
0

My gut says that the schema is rapidly getting too complex.

Let's back up and think about "Entities" and "Relationships".

There are 2 main Entities: People and Reviews. See if it is not too clumsy to put Interviewers and Interviewees into the same People table. If the columns are too different, then make 2 tables. Ditto for Reviews.

Then provide the minimum number of Relationship tables -- but only when there are many:many relationships. (Many:1 does not need an extra table.) The "Interview" is likely to be a Relationship table, but have a few extra columns for when/where/how-long/type/etc.

Do not "over normalize". For example, an Interview Type is probably one of a few keywords. These could be implemented in an ENUM without needing an extra table.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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