Databases
StackOverflow2013
Postsdbo
POWhich database design for complex quiz app?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhich database design for complex quiz app?
    primarykey
    data
    text
    <p>at the moment I am designing a database (mysql) schema for a complex quiz app. I am at the question stage at the moment, and struggle a little with the table design. Here's what I got so far:</p> <p>question</p> <pre> task id type etc. 1 mpc 2 text-input 3 matching 4 fill-gaps task_to_question task_id question_id 1 10 2 20 3 30 3 31 3 32 4 40 answer_set ->(task_to_question) id question_id answer_id correct 1 10 100 1 2 10 101 0 3 10 102 0 4 10 103 0 5 20 100 1 6 20 200 1 7 30 100 (1) 8 31 300 (1) 9 32 301 (1) 10 40 100 1 11 40 400 1 12 40 401 1 addon answer_set_id gap_number 10 1 11 1 12 2 </pre> <p>The idea is that the task-table has just some basic info and the type-field which decides how to handle the task. All tasks are in the same table; every task has one or more questions (task_to_question), depending on the type, e.g. mpc has one question, but a task where you have to match questions and answers has 4 questions. In the same vein, every question can have multiple answers (answer_set) - especially important for one or two-word text-input. So even within a task type, the number of questions and answers is fluid. This can of course be restricted later, as well as subtypes added. The last table is only joined when the task type cannot be handled by the simple field types. In this example, I need to define the order of gaps in a "fill in the gaps in the text" task. This could be one addon-table, or even multiple tables for distinct task types (to reduce overhead)</p> <p>My intention for this was to reuse as many text-strings as possible. So I can have the same question-text and the same answer-strings for mpc, input, fill-the-gap, and partly even matching tasks. Similarly, I think it will be easy to collect suggestions for autocomplete fields, without having to sort through duplicates.</p> <p>But the more think about it, the more I think it is actually too convulted. Problems I already see are e.g. that I already have some redundancy when a question has only right or no answers - correct column becomes redundant. And my answers must be varchar, even if I might add other question types. And then I may have to join more tables when I have vastly different task types anyway, so why not use separate tables from the beginning? This is basically how it's done in Ilias. Is it even a big advantage in getting rid of the tables for every type? Then again, I will only have task model that has to have all the handlers for all the task types, instead of separate models. </p> <p>Wow that was a lot of text ... well, I needed to write it down to see it in full. Still not quite sure how to handle it. input gladly appreciated!</p> <p>edit: Another thing ... is it just me, or is it kinda weird that the answer_set table references question_id? Is that the right way to do it?</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USWill
    1. USNarretz
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POWhich database design for complex quiz app?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POWhich database design for complex quiz app?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POWhich database design for complex quiz app?
      1. USAbundnce10
      1. VTFavorite
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload