CODEWITHSUNDEEP
sqlhibernatedatabase-designormdatabase-schema
parsecer
parsecer

Reputation: 5136

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

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?

Upvotes: -1

Views: 158

Answers (1)

user23781552
user23781552

Reputation: 116

Both of the schema you've sketched out looks reasonable, from a database perspective. I've fleshed out an interpretation of this, using slightly different naming conventions, that I think would be a slight improvement. See sqlfiddle.

As a word of caution, don't be tempted to contort the database schema in unnatural ways in the name of organizing your application code; a poorly designed database can become a real thorn in your side further down the line. I don't think you've gone down this route so far at all, but flagging it here as a consideration.

Inheritance is best in a situation where the child genuinely is a sub-type of the parent. In this case, I don't interpret a blank option as being a sub-type of an exercise question, it's more of an attribute of it. So they are related, but not in a set-subset way.

In this scenario, you could consider using composition, rather than inheritance. E.g. using the suggested schema you can pass in a List<QuestionBlank> to the class Question when you instantiate it. You are setting the attribute Question.QuestionBlanks to that list.

If you want to use inheritance to reflect the similarities between Question, QuestionPart and QuestionPartBlankOption, you could consider creating a single base class, from which they each inherit. They each share some properties, e.g. a main text field, will presumably require a display order of some kind, etc. If you felt the code re-use you could achieve with this justified this additional abstraction, then all three objects could inherit from a single parent that we could call "QuestionText" or whatever. This may be a convenient abstraction in your application code that is not coupled to a specific database table.

Upvotes: 1

Related Questions