Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>You've faced a common problem: Trying to use something static (database with predefined structure) for something dynamic (bunch of individual data sets which only have one thing in common: they come from forms). What you want is <em>doable</em> with databases but would be significantly easier to do without, however since I assume you really do want to use a database for this, here's what I'd do:</p> <ul> <li>You have a <code>document</code> (or <em>questionnaire</em>) which contains multiple <code>questions</code>. These both are generic enough and require their own tables, just as you've done so far.</li> <li>Each question has a <code>type</code> which defines what kind of question it is (<em>multiple select, freeform, select one...</em>) and of course the question also has <code>options</code>. So that's two tables more. The reasoning here is that decoupling these from the actual question allows for certain level of abstraction exist and your queries will still be somewhat simple although possibly loooooong.</li> </ul> <p>So, each document has 1..n to questions, each question has 1 type and 1..n options. Skipping a bit, here's what I'm thinking of with link tables etc.</p> <pre><code>Document bigint id DocumentQuestions bigint document_id bigint question_id Question bigint id varchar question QuestionType bigint question_id bigint type_id Type [pre-filled table with id:type pairs, such as 1=freeform, 2=select one etc.] QuestionOptions bigint id bigint question_id varchar description varchar value Answers bigint id bigint document_id [etc. such as user_id] QuestionAnswers bigint answer_id bigint question_id bigint questionoptions_id </code></pre> <p>This sort of design permits several things:</p> <ul> <li>Questions themselves are reusable, very handy if you're making a generic "<em>answer these x random questions from a pool of y questions</em>".</li> <li>New types can be added easily without breaking existing ones.</li> <li>You can always navigate through the structure quite easily, for example "<em>What was the name of the document for this single question answer I have?</em>" or "how many people have answered incorrectly to this one question?"</li> <li>Because types are separated, you can create a (web) UI which reflects the state in the database easily - better yet, if the type changes you may don't even have to touch your UI code at all.</li> <li>Since each possibly option for a question is its own row in the <code>QuestionOptions</code> table, you can get the actual value very easily.</li> </ul> <p>The obvious problem with this is that it requires quite strict coupling between the tables for integrity and will be a pain to get running properly at start. Also since <code>value</code> in the <code>QuestionOptions</code> is varchar, you need to be able to parse stuff a lot and you may even want to introduce another field for conversion hints.</p> <p>Hope this helps even though you wouldn't agree with my solution at all.</p>
    singulars
    1. This table or related slice is empty.
    1. POStoring dynamic form data in DBMS, looking for the optimal approach
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USEsko
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POStoring dynamic form data in DBMS, looking for the optimal approach
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks a bunch for your quick input and I'm happy to read your interpretation of my question, which should've been quite a bit more specific no my end. The decoupling the answers and 'type of answer' is indeed a great suggestion, sadly I should've mentioned it's safe to assume here that all answers are simple varchars in this case, no other types will exist. Upvoted for the obvious amount of thought you've given this issue, will accept it as solution unless someone else comes around - thanks a lot.
      singulars
      1. PO
      1. USLeftblank
    2. COThanks. Wait for a while before accepting though, that way others may still answer since I'm not entirely sure all the DB wizards of SO are awake at this hour. Oh and I've done something similar before and this is basically how I did it back then - that's also the source of the warning in the first paragraph, by scrapping the database with a completely proprietary solution we saw a **28 000%** performance increase in this system that used a similar thing (*not for question but something similar structurally*).
      singulars
      1. PO
      1. USEsko
    3. COProbably not completely related indeed, but I'm curious what the solution was, would you mind sharing the name/type of proprietary solution used? Thanks!
      singulars
      1. PO
      1. USLeftblank
 

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