This is my first take at the sequence of DB design changes necessary to implement https://moodle.org/mod/forum/discuss.php?d=231180 / https://tracker.moodle.org/browse/MDL-40987. Comments welcome. This design came out of a very helpful discussion with Col Chambers and Mahmoud Kassaei. Revised to take into account feedback from Sam Marshal.

Quiz database changes

I am using BOOL for INT(X) where that is more descriptive.

- line removed
+ line added
* line changed

Unchanged tables omitted from each block.

All columns NOT NULL unless marked NULL.

The order of some of the TRANSFORMS could be varied. I was just guessing a likely order.

-----------------------------------------------------------------
CURRENT STRUCTURE (Moodle 2.6).

mdl_quiz
	id                   SEQUENCE
	...
	questionsperpage     INT(10)
	navmethod            CHAR(16)         'free' or 'seq'
	shufflequestions     BOOL
	questions            TEXT             e.g. 123,234,0,345,456,0 - list of question ids & breaks
	...

mdl_quiz_question_instances
	id                   SEQUENCE
	quiz                 INT(10) FK
	question             INT(10) FK
	grade                NUMBER(12.7)


-----------------------------------------------------------------
TRANSFORM 1 - fix obvious coding style failures.

mdl_quiz_question_instances
	id                   SEQUENCE
*	quizid               INT(10) FK
*	questionid           INT(10) FK
*	maxmark              NUMBER(12.7)


-----------------------------------------------------------------
TRANSFORM 2 - eliminate mdl_quiz.questions & rename question_instances -> slots

mdl_quiz
	id                   SEQUENCE
	...
	questionsperpage     INT(10)
	navmethod            CHAR(16)         'free' or 'seq'
	shufflequestions     BOOL
-
	...

*mdl_quiz_slots
	id                   SEQUENCE
	quizid               INT(10) FK
+	slot                 INT(10)          Similar to question_attempt.slot
+	page                 INT(10)          All slots on a page must have consecutive numbers. Page numbers must be increasing.
	questionid           INT(10) FK
	maxmark              NUMBER(12.7)  


-----------------------------------------------------------------
TRANSFORM 3 - add section headings.

+mdl_quiz_sections
+	id                   SEQUENCE
+	quizid               INT(10) FK
+	firstslot            INT(10) FK       Section runs from this slot, to just before the next section, or the end.
+	heading              CHAR(1333) NULL

QUESTION: do we need to make mdl_quiz_slot_questions.questionid NULLable to allow a new blank section/page to be added to the quiz?

-----------------------------------------------------------------
TRANSFORM 4 - make shuffling per-section, not per quiz.

mdl_quiz
	id                   SEQUENCE
	...
	questionsperpage     INT(10)
	navmethod            CHAR(16)         'free' or 'seq'
-
	...

mdl_quiz_sections
	id                   SEQUENCE
	quizid               INT(10) FK
	firstslot            INT(10) FK       Section runs from this slot, to just before the next section, or the end.
	heading              CHAR(1333) NULL
+	shuffle              BOOL


-----------------------------------------------------------------
TRANSFORM 5 - add the ability for a question to require the previous one to have been answered.

mdl_quiz_slots
	id                   SEQUENCE
	quizid               INT(10) FK
	sectionid            INT(10) FK       All qustions in a section must have consecutive slot numbers.
	slot                 INT(10)          Similar to question_attempt.slot
	page                 INT(10)          All slots on a page must have consecutive numbers. Page numbers must be increasing.
	questionid           INT(10) FK
	maxmark              NUMBER(12.7)  
+	requireprevious      BOOL

QUESTION: if we do this, does it render mdl_quiz.navmethod (which works on pages, not questions) obsolete?


-----------------------------------------------------------------
TRANSFORM 6 - unpick how random question from category works, to no longer require random Qs in the Q bank.

mdl_quiz_slots
	id                   SEQUENCE
	quizid               INT(10) FK
	sectionid            INT(10) FK       All qustions in a section must have consecutive slot numbers.
	slot                 INT(10)          Similar to question_attempt.slot
	page                 INT(10)          All slots on a page must have consecutive numbers. Page numbers must be increasing.
*	questionid           INT(10) FK NULL
+	questioncategoryid   INT(10) FK NULL  Either questionid is not NULL, or questioncategoryid/includesubcategories are not NULL.
+	includesubcategories BOOL    NULL
	maxmark              NUMBER(12.7)  
	requireprevious      BOOL

At this point we can also drop all random questions from the mdl_qusetion table, and even consider removing qtype_random, if we are sure no add-on is using it.


-----------------------------------------------------------------
TRANSFORM 7 - split mdl_quiz_slots in preparation for the next transform.

mdl_quiz_slots
	id                   SEQUENCE
	quizid               INT(10) FK
	sectionid            INT(10) FK       All qustions in a section must have consecutive slot numbers.
	slot                 INT(10)          Similar to question_attempt.slot
	page                 INT(10)          All slots on a page must have consecutive numbers. Page numbers must be increasing.
---
	maxmark              NUMBER(12.7)  
	requireprevious      BOOL

+mdl_quiz_slot_questions
+	id                   SEQUENCE
+	quizid               INT(10) FK       I think a 2-col foreign key is more natural that a single quizslotid.
+	slot                 INT(10) FK
	questionid           INT(10) FK NULL
	questioncategoryid   INT(10) FK NULL  Either questionid is not NULL, or questioncategoryid/includesubcategories are not NULL.
	includesubcategories BOOL    NULL


-----------------------------------------------------------------
TRANSFORM 8 - allow for sequences on linked questions.

mdl_quiz_slot_questions
	id                   SEQUENCE
	quizid               INT(10) FK       I think a 2-col foreign key is more natural that a single quizslotid.
	slot                 INT(10)
+	variantnumber        INT(10)          Between 1 and variantcount for this slot. (Or start at 0?)
	questionid           INT(10) FK NULL
	questioncategoryid   INT(10) FK NULL  Either questionid is not NULL, or questioncategoryid/includesubcategories are not NULL.
	includesubcategories BOOL    NULL

+mdl_quiz_variant_sets
+	id                   SEQUENCE
+	quizid               INT(10)
+	firstslot            INT(10)          firstslot / lastslot ranges must not overlap.
+	lastslot             INT(10)
+	variantcount         INT(10)

I don't like the use of the word variant here. It is used in the question engine to mean something different. We need to find another word. 'strand'? ???

-----------------------------------------------------------------
TRANSFORM 9 - allow repeat in place.

mdl_quiz_slots
	id                   SEQUENCE
	quizid               INT(10) FK
	sectionid            INT(10) FK       All qustions in a section must have consecutive slot numbers.
	slot                 INT(10)          Similar to question_attempt.slot
	page                 INT(10)          All slots on a page must have consecutive numbers. Page numbers must be increasing.
	maxmark              NUMBER(12.7)  
	requireprevious      BOOL
+	repeatinplace        BOOL             For questions with variants, or random questions.


-----------------------------------------------------------------
PROPOSED FINAL STRUCTURE (Moodle 2.7?).

mdl_quiz
	id                   SEQUENCE
	...
	questionsperpage     INT(10)
	navmethod            CHAR(16)         'free' or 'seq'
	...

mdl_quiz_slots
	id                   SEQUENCE
	quizid               INT(10) FK
	slot                 INT(10)          Similar to question_attempt.slot
	page                 INT(10)          All slots on a page must have consecutive numbers. Page numbers must be increasing.
	maxmark              NUMBER(12.7)  
	requireprevious      BOOL
	repeatinplace        BOOL             For questions with variants, or random questions.

mdl_quiz_slot_questions
	id                   SEQUENCE
	quizid               INT(10) FK       I think a 2-col foreign key is more natural that a single quizslotid.
	slot                 INT(10) FK
	variantnumber        INT(10)          Between 1 and variantcount for this slot. (Or start at 0?)
	questionid           INT(10) FK NULL
	questioncategoryid   INT(10) FK NULL  Either questionid is not NULL, or questioncategoryid/includesubcategories are not NULL.
	includesubcategories BOOL    NULL

mdl_quiz_sections
	id                   SEQUENCE
	quizid               INT(10) FK
	firstslot            INT(10) FK       Section runs from this slot, to just before the next section, or the end.
	heading              CHAR(1333) NULL
	shuffle              BOOL

mdl_quiz_variant_sets
	id                   SEQUENCE
	quizid               INT(10)
	firstslot            INT(10)          firstslot / lastslot ranges must not overlap.
	lastslot             INT(10)
	variantcount         INT(10)

QUESTION: does questionsperpage need to be kept? Or is it enough to make make re-paginating easy in the UI? I think I have a slight preference for keeping it.

NOTE: I can spot aspects of this design that are not properly normalised. Suggested inprovements welcome.

NOTE: Adding mdl_quiz_slots.gradecategory, or something like that, would be possible in future, which is an oft-requested feature.


-----------------------------------------------------------------
Once we are done, the various editing actions are:

Add page break after slot X: UPDATE mdl_quiz_slot_questions SET page = page + 1 WHERE slot > X AND quizid = Q
Remove the page break after page X: UPDATE mdl_quiz_slot_questions SET page = page - 1 WHERE page > X AND quizid = Q
Toggle whether slot X depends on the previous: UPDATE mdl_quiz_slot_questions SET requireprevious = (0/1) WHERE slot = X AND quizid = Q
Similarly for setting maxmark.