Create tables for quiz system in hasura

[zerubeus]

 * Question
   - question_id   auto integer
   - question      varchar
   - is_active     enum(0,1)
 * Question_choices
   - choice_id        auto integer
   - question_id      integer
   - is_right_choice  enum(0,1)
   - choice           varchar
 * User_question_answer
   - user_id      integer
   - question_id  integer
   - choice_id    integer
   - is_right     enum(0,1)
   - answer_time  datetime

• table Question is for storing all your questions. It has is_active so that you can selectively display only active questions (using WHERE is_active = '1')
• table question_choices is for storing all available options. It has is_right_choice which defines what choice is the right answer for particular question.
• Table User_question_answer is for storing answer from your user. It has is_right for faster lookup, to see whether that particular question and answer choice is right (based on is_right_choice previously defined). It also has answer_time just to note when that particular user answer the question.

We can extend this with another table "exam" and let it take few questions and a score for each

[abumalick]

This schema will work only for multiple choice questions. We have 5 different sorts of quizzes, so it means we would have to add one or two tables for each quiz type if we use this solution. We will need a translation table for each type also. It is a lot of problems. it is why we used a JSON field for the quiz data, so that we only need one table for each type of quiz. It is true that it brings some complexity but in this case, I don't really feat good about having more 10 tables only for quiz.

Here is the structure of the quizzes:

witch (type_slug) {
    case 'choose': // {text, values: [ch1, ch2, ch3, ...]}
      return <Choose {...props} />
    case 'choose_a_category': // {values: [{name: '', items: ''}, {name: '', items: ''}]}
      return <ChooseACategory {...props} />
    case 'fill_in_the_blank': // {textWithPlaceholders, values: [val1, val2, val3, ...]}
      return <FillInTheBlank {...props} />
    case 'link_the_sentences': // {title, values: [{a, b}, {a, b}, ...]}
      return <LinkTheSentences {...props} />
    // // case 'reorder': // [p1, p2, p3, ...]
    // //   return Reorder
    case 'true_or_false': // {text, isTrue}
      return <TrueOrFalse {...props} />

I really feel that it would be better that we keep something simple, even if we really need to document and enforce the content field to respect specific schemas.

Do you understand what I mean?

[zerubeus]

It is good to normalize, having enough tables to represent our relations still better that doing workarounds, for these 5 types all we need for this data representation is quiz_type table

[zerubeus]

And even with 5 categories of quizzes they can still fit in the Question_choices table, we have to normalize even if we end up with 9 tables for the quiz system, this will allow us levering the power of sql if we decide work with the db outside hasura.