0

I'm creating a survey app and I don't know how to design database. I need surveys with multiple questions and multiple types of questions. There are 3 ways that I could think of:

a) Create a database table for surveys and every type of question and connect them with Survey_id column.

b) Create a database table for questions and surveys and have them stored in JSON like text field. Those Text fields could look like this:

{
    question: 'Who is the best student?'
    type: 'multiple-choices',
    choices: [
         'Bob',
         'Alice',
         'Alex',
    ],
}

c) Last option is to create a database table just for surveys and have them stored in JSON like format. Like this:

{
    heading: 'My survey'
    date: '2019-01-01',
    questions: [
         {...},
         {...},
         {...},
    ],
}

Thanks for any suggestions.

closed as unclear what you're asking by BobDalgleish, 8bittree, TZHX, Laiv, jwenting Mar 12 at 11:37

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

1

The most suitable approach will heavily depend on the kind of database you consider.

Relational database:

In the relational DB, you should have a table for each entity, so Survey, Question, QuestionChoice. You could have a table for QuestionType but you could also use a hard-coded property (especially if your program foresees specific behavior depending on the type of question):enter image description here

Question would have a field Survey_id to make the link to the Survey. And similarly, QuestionOption would have a field Question_id (or Question_id and Survey_id).

In this way, you could easily add an entity for users, an entity for keeping track of the user participation in survey (e.g. date of participation), and the answer to a question.

And it's then easy to combine information in different tables by using join.

NoSQL Document database

As you mention JSON, you could also make use of a NoSQL document database like MongoDB, which often have a tight relation with JSON.

In this case you could be tempted to have one document for Survey, which has a collection of Questions, which have a collection of QuestionChoice. With this scheme, it's easy to load a questionnaire. However, combining information (e.g. questions together with user answers) is much more difficult and needs programming.

Alternatively, you could also use a table like approach, with different documents and references from the main document to related documents. As it is more complex to maintain, you'd go for this approach only if performance issues would need it.

Not the answer you're looking for? Browse other questions tagged or ask your own question.