1

I hope you understand my problem. An excerpt of my MySQL database looks like that:

enter image description here

To ensure data integrity two constraints must be implemented:

  1. Given an answer record, both "referencial paths" have to lead to the same exam record.
  2. Every exam_student must answer every questions. In other words: There should be an exam_question_result for each pair of exam_question and exam_student related to the same exam.

How can I realize these constraints in my database?

Solution 1 (referring to comments under this post):

I updated the schema using composite keys.

enter image description here

smirk_mirkin
  • 25
  • 6
  • I'd start by using composite keys instead of / in addition to a surrogate key. – reaanb Dec 08 '15 at 03:43
  • I cannot see how that would help. Moreover, my ORM does not support composite keys. – smirk_mirkin Dec 09 '15 at 14:02
  • With composite keys, your exam_question_result would contain exam_id, segment_id, question_id and student_id, and setting up foreign key constraints to enforce requirement 1 would be simple. Requirement 2 effectively means you can't add an exam question or exam student without adding all required answers - is that what you want? It may be possible to enforce depending on your DBMS. Can't help with the ORM, sorry. – reaanb Dec 09 '15 at 14:57
  • Thanks, I really appreciate your answer. – smirk_mirkin Dec 09 '15 at 15:30
  • But especially setting up foreign key constraints is still a bit cloudy to me. In exam_question table, (exam_id, segment_id) references exam_segment(exam_id, segment_id) right? But in exam_question_result table, to which table would you constrain exam_id? PS: I will add an updated diagram. – smirk_mirkin Dec 09 '15 at 15:47
  • I would constrain (exam_id, segment_no, question_no) in exam_question_result to be in exam_question, and (exam_id, student_id) to be in exam_student. No need to constrain exam_id on its own. – reaanb Dec 09 '15 at 16:11
  • Oh, I always thought a column (here exam_id) can only used in one foreign key constraint. – smirk_mirkin Dec 09 '15 at 16:21
  • Referring requirement 1: It seems that using composite keys is the only way to ensure referential integrity even in simple schemata like above. But also it seems that a lot of programmers prefer the surrogate way. Several mappers like Laravel Eloquent ORM require an id in each table. How does that fit together? – smirk_mirkin Dec 09 '15 at 17:46
  • I don't use ORMs, but I do often use surrogate keys in addition to composite keys. I'll usually set the surrogate as PK and create any other candidate keys as unique keys, MySQL is ok with foreign key constraints based on unique keys. Sometimes a surrogate key can do double duty as part of a composite unique key, like your segment_id and question_id could be autonumber PK and also part of the composite key. – reaanb Dec 09 '15 at 17:57
  • Thank you for your great advices reaanb :) – smirk_mirkin Dec 09 '15 at 18:55
  • If I may offer a last bit of unrequited advice, I suggest you ditch the ORMs and learn the relational model. Anyway, good luck with your project! – reaanb Dec 09 '15 at 19:49

0 Answers0