SQL schema for a fill-in-the-blank exercise

46 Views Asked by At

I'd like to create an online quiz application. I have in mind several types of exercises, including those for filling in the blank:

An exercise can require a user to enter the text:

Chemical energy produced by the ____ is stored in a small molecule called ____

Or to pick an option from the given list:

Mitochondria contain their own small (genes/cells/chromosomes)

I've been wondering about the best way to represent it in SQL schema. The blanks can appear in any part of the sentence, and there can be any number of blanks.

So I thought to represent it like this:

class BlankExercise  {
  @ManyToOne
  private List<Part> parts;
}

Where a Part can either be normal text or can be the blank. Thus each exercise is a sum of its parts:

Chemical energy produced by the ____ is stored in a small molecule called ____

would have 4 parts:

1. Chemical energy produced by the
2. (Mitochondria)
3. is stored in a small molecule called
4. (adenosine)

But that would require using a form of inheritance strategy - the Part would be a common parent class, which would be extended by NormalPart and BlankEmptyPart and BlankOptionsPart. But I've read that one should abstain from using inheritance with database entities, if at all possible, but it's difficult for me to see a solution that wouldn't involve inheritance.

The only other solution I could think of would use several tables a separate table for 'normal' text and separate tables for each type of blank:

exercise_texts
exercise_id text

and for the blanks where the user would have to type the whole word:

exerice_blanks_empty
exercise_id solution_text blank_index

and for the blanks where the user would pick an existing option:

exerice_blanks_options blank_index
exercise_id blank_id

with

exercise_blanks_options_list
blank_id option_text is_correct

Each blank table would have blank_index column, which would correspond to its position in the sentence: for instance, Mitochondria would have an index of 1 and adenosine would have an index of 3.

I was wondering, which design is better, or neither is feasible and there is a better one?

0

There are 0 best solutions below