"fill in the blanks"

Question

I'm trying to make a simple "fill in the blanks" type of exam in django and would like to know what is the best way to design the database.

Example: "9 is the sum of 4 and 5, or 3 and 6."

During the exam, the above sentence would appear as "__ is the sum of __ and _, or _ and __."

Obviously there are unlimited number of answers to this question, but assume that the above numbers are the only answers. But the catch is that you can switch the places of 4 and 5, or the places of 3 and 6 and still get the right answer. Besides, the number of blanks is not known, so it can be 1 or more.

Answer 1

I would go with something like. First define a Question table:

Question
--------------------------
Id     Text
1      9 is the sum of 4 and 5, or 3 and 6
...

Then save the position of the hidden substrings, let's call them fields, in another table:

QuestionField
--------------------------
Id  QuestionId  StartsAt    EndsAt      Set
1   1           0           1           1
2   1           16          17          2
3   1           22          23          2  # NOTE: Is in the same set as QuestionField #2
...

This table lets you retrieve the actual value of the field by querying the Question table (e.g. entry one refers to the value '9' in the first question).

The "Set" column contains an identifier of the "set" in which this field is, where fields in the same set can be replaced by each other. When you populate it, you would have to ensure that all questions that can be replaced by each other are in the same set. The actual number of the set doesn't matter, as long as it's unique. But it makes sense to have it equal to the ID of one of the elements of the set.