Back to articles list
I need to create the design for a new database which will be the data layer for an application; the application will be an online survey or polling like Survey Monkey. My challenge is that the functionality that I require is not supported by existing survey sites, so I need to build my own. What I need is a conditional survey (if the answer to question 4 is “yes,” then we ask question 5 and skip question 6; but if the answer to question 4 is “no,” then we skip question 5 and ask question 6).
I will be writing a series of articles about this design and extend the design each time, so this article is mostly about setting the scene for the basic survey database. In future articles, I will work on the more advanced features related to a conditional order of questions in a survey and the administration of the surveys.
Domain
The domain for an online survey is based on a questionnaire or poll that consists of a series of questions. Respondents will take the survey and we need to store their responses.
For now, we will start with open-ended questions that consist of a question and an answer.
In future versions of the model, we will want to define the order of the questions in the survey, whether or not the questions are conditional, and the different types of questions (such as multiple choice).
Functionality
As mentioned, we will be asking respondents to respond to the survey (it will not be open to everyone) and we need to store their responses. In addition, the same participant can return and update their responses. Storing responses can be somewhat challenging as we will need tables that are linked to the original questions, but have responses for each of the respondents that completes the survey.
In future versions of the model, we will add administrators of the online surveys.
Finally, there will need to be reports and analytics: What were the responses? How many responses were received from different sub-groups? Which questions were not responded to most frequently (to identify badly worded questions)?
Entities and Relationships
Let’s see: we have a survey (or questionnaire) consisting of a series of questions. For now, we start only with open-ended questions so there is no need for possible responses linked to the question, as would be necessary for multiple choice questions. The order of questions in the survey will be fixed (defined when the survey is created), but in future versions of the model we would like to add the option for a conditional path through the survey.
Obviously, we will need respondents and their responses. As mentioned, we will add user administration in the future versions of the model.
Formal Design
Now that we know the entities and relations, we are ready to build our Entity Relationship Diagram (ERD). To do this, we will use Vertabelo, an online tool for database modeling. If you don’t have your Vertabelo account yet, you can sign up here for a free trial plan.
For our online survey, we will start with the survey, its associated questions, and the order in which the questions are displayed. We add in the respondents and then link the surveys to which a respondent provided responses and their responses for a survey.
For now, let’s use that as the basis and add further complexity in future versions of the model.
Conclusion
With our basic design defined, we can now start thinking about improvements. In future articles, I will work on the more advanced features such as:
- Different types of questions such as multiple choice questions
- Conditional order of questions in a survey
- Administration of the surveys.
