Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <blockquote> <h1>Survey Database Design</h1> </blockquote> <p>Last Update: 5/3/2015<br> Diagram and SQL files now available at <a href="https://github.com/durrantm/survey">https://github.com/durrantm/survey</a></p> <p><img src="https://i.stack.imgur.com/BO7b9.png" alt="enter image description here"></p> <p><strong>If you use this (top) answer or any element, please add feedback on improvements !!!</strong></p> <p>This is a real classic, done by thousands. They always seems 'fairly simple' to start with but to be good it's actually pretty complex. To do this in Rails I would use the model shown in the attached diagram. I'm sure it seems way over complicated for some, but once you've built a few of these, over the years, you realize that most of the design decisions are very classic patterns, best addressed by a dynamic flexible data structure at the outset.<br> More details below:</p> <blockquote> <h1>Table details for key tables</h1> </blockquote> <h2>answers</h2> <p>The <strong>answers</strong> table is critical as it captures the actual responses by users. You'll notice that answers links to <strong>question_options</strong>, not <strong>questions</strong>. This is intentional.</p> <h2>input_types</h2> <p><strong>input_types</strong> are the types of questions. Each question can only be of 1 type, e.g. all radio dials, all text field(s), etc. Use additional questions for when there are (say) 5 radio-dials and 1 check box for an "include?" option or some such combination. Label the two questions in the users view as one but internally have two questions, one for the radio-dials, one for the check box. The checkbox will have a group of 1 in this case.</p> <h2>option_groups</h2> <p><strong>option_groups</strong> and <strong>option_choices</strong> let you build 'common' groups. One example, in a real estate application there might be the question 'How old is the property?'. The answers might be desired in the ranges: 1-5 6-10 10-25 25-100 100+</p> <p>Then, for example, if there is a question about the adjoining property age, then the survey will want to 'reuse' the above ranges, so that same option_group and options get used.</p> <h2>units_of_measure</h2> <p><strong>units_of_measure</strong> is as it sounds. Whether it's inches, cups, pixels, bricks or whatever, you can define it once here.</p> <p>FYI: Although generic in nature, one can create an application on top of this, and this schema is well-suited to the <strong>Ruby On Rails</strong> framework with conventions such as "id" for the primary key for each table. Also the relationships are all simple one_to_many's with no many_to_many or has_many throughs needed. I would probably add has_many :throughs and/or :delegates though to get things like survey_name from an individual answer easily without.multiple.chaining. </p>
    singulars
    1. This table or related slice is empty.
    1. POWhat mysql database tables and relationships would support a Q&A survey with conditional questions?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USMichael Durrant
    1. USMichael Durrant
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POWhat mysql database tables and relationships would support a Q&A survey with conditional questions?
      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. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COwish I could upvote more than once :)
      singulars
      1. PO
      1. USWil
    2. COHow do you handle conditional questions in this schema? I only see required flags, no flow control... perhaps I am missing something.
      singulars
      1. PO
      1. USGeorges
    3. COGreat question, George! hmmm, like "ask question 3 if answer 2 is option c"? I assume you mean dependent on another questions answer then? For instance only ask about roof rack if vehicle is suv, or only ask for spouse name if user age > 18 ? So base it on the answer to another question? So what do you (or anyone) think is the right structure for that? Have two fields in the question table that are 'depends_on_answer_id' and 'depends_on_answer_value' ?
      singulars
      1. PO
      1. USMichael Durrant
 

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