how should I design a questionnaire database?

Question

I'm sort of new to creating databases and this is the most complex one I have yet to make and I would like to make sure I am following the best practice in my design. (This is just a private project I'm doing to learn as fun)

Basically I will have questionnaires stored which can have multiple questions. Questions can be reused on multiple questionnaires if already created so I have a smart search sort of interface which will check the database.

Then I have an answer for each question linked to a question on a questionnaire and a user who answered it on a date.

I would also have multiple question types from: select, text, text_area, number , date, radio.

For select types I would have a choices table indicating the choices available by the select.

User table would link to answers when a questionnaire is being used.

I would also have questionnaire dependencies for when a question depends on another like: do you smoke? if yes -> how much do you smoke?

Things I'm not really sure about are the many to many relationship using a junction table and self referencing the questions from the junction table to form dependencies. Would this be considered a correct design and if not what am I doing wrong?

[database-design-for-a-survey](https://stackoverflow.com/questions/1764435/database-design-for-a-survey) or [survey-qa-db-design](https://stackoverflow.com/questions/540885/what-mysql-database-tables-and-relationships-would-support-a-qa-survey-with-con/5858666#5858666) SQL based answer — buncis, Sep 08 '22 at 16:05

dmfay · Accepted Answer · 2019-03-16T15:31:03.287

In a relational schema, you would represent question dependency through a self-joining foreign key; you don't need to go back to the junction table, since the relationship between the two questions is independent from each question's relationship to the questionnaire.

However, as you've probably noticed, representing a branching set of questions in a relational schema is more than a little awkward. If the questionnaire is most of what you're storing, you might want to look into alternatives like MongoDB (or using JSONB fields in Postgres), which would let you represent a questionnaire as a document containing nested questions. A single user's responses to a questionnaire comprise a second (type of) document; all collected questionnaire results are easily searchable by user or by questionnaire, and you can slice and dig into individual question responses using the aggregation tools. The only thing it doesn't make easier is question reuse -- but odds are that's less than critical.

Treating questionnaires and questions as documents also gives you some much more flexible tools for representing dependency: instead of a hard link between "do you smoke?" and "how much?", you might apply a validator to a conditions object, if present, and only ask the question if any conditions are met:

[..., {
  "name": "smoker",
  "text": "Do you smoke?",
  "values": [true, false]
}, {
  "name": "how-much",
  "text": "How much do you smoke?",
  "conditions": {
    "smoker": true
  },
  "values": ["Socially", "< 1 pack/day", "1 pack/day", "2 packs/day", ...]
}, ...]

"more than a little awkward" There is no difficulty representing or querying hierarchical relation(ship)s/associations in a relational DBMS. — philipxy, Mar 16 '19 at 17:03

how should I design a questionnaire database?

1 Answers1