Database Design for Online Survey Systems

In this article, we review the steps for creating a database design for an online survey system using Vertabelo Data Modeler. Learn how to create a logical database model, add all the entities and relationships required, and then transform it into a physical database model by generating the SQL scripts to implement it on your database of choice.

This article explains the tasks to create a database model for an online survey system, step by step. We assume you have basic knowledge of data modeling. You may want to review the article "Data Modeling Basics in 10 Minutes" on this blog for a refresher on the basics of data modeling.

Creating a Logical Data Model Using Vertabelo

Let's start from the beginning. Log in to Vertabelo Data Modeler (create a free trial account if you are not already a user). Then create a new database logical diagram by clicking on the "Create new document" icon in the left section of the taskbar:

Select the type of document to create. Here, we are creating a logical data model, so click "Create" there:

The last step of this task consists of entering a name for the data model and selecting "START MODELING" as shown below:

Adding Entities

With this now created, we need to start adding the entities required for our model. To keep things organized, I have decided to "split" the model into two sets of entities. The first set is called "Survey Information" and contains all entities required to define surveys. The second set is called "Response Information" with all entities related to responses to the surveys.

Let's review the steps to create entities in Vertabelo Data Modeler. We will use Survey as a demo entity to help us understand how to create an entity. Then we will discuss and explain the remaining entities in our design.

Survey

This is the starting point for our system. This entity contains core information about each survey generated in the system. It has the following attributes:

• SurveyID: A system-generated unique identifier to identify and reference each survey. It is mandatory and is the primary identifier (primary key) of the entity.
• Name: The name of each survey stored on the system. It is mandatory. Since it must be unique, we make it an alternate key.
• Description: The description of the purpose of the survey. It is mandatory.
• StartDate: The date and time when the survey is made available on the system. It is a mandatory attribute.
• EndDate: The date and time when the survey is automatically closed by the system. It is not mandatory.
• MinResponses: The minimum number of responses required for the survey. If that minimum is defined but has not yet been reached, the survey stays open even if the EndDate has passed. It is not mandatory.
• MaxResponses: The maximum number of responses accepted for the survey. If this value is reached, the survey closes automatically even if the EndDate has not yet been reached. It is not mandatory.

To create an entity in Vertabelo, follow these steps:

1. Select the "Add new entity" icon in the tools list and click on any empty section of the diagram:

2. Use the "ENTITY PROPERTIES" section located on the right side of the screen to enter the name and the description of the entity in the "General" subsection, as shown below:

3. Scroll down in the "ENTITY PROPERTIES" section until you reach the "Attributes" subsection. Add all the entity attributes by clicking on the "+ Add Attribute" button. Specify the name and the data type for each attribute. Use the small gearbox icon to select the data type from a list. You can also set each attribute to be mandatory and define it as part of the primary identifier as appropriate by selecting the checkbox under the "M" and "PI", respectively:

4. Below the "Attributes" subsection, you will find the "Primary identifier" subsection. Add the primary identifier here if you did not do so in the previous step. If you have created it in the "Attributes" subsection, you can edit its name here.

5. We now move down to the "Identifiers" subsection. Here, you can create other unique identifiers (also known as alternate keys) for the entity. In our case, we create one for the Name attribute to avoid having multiple surveys with the same name:

These are the basic steps for creating an entity in a logical data model using Vertabelo. Now, we continue with the rest of the entities required in our model.

SurveyStatus

This entity contains the different statuses for a survey. They usually start with "Planned" then change to "Open" and finally "Closed" when any of the closing conditions are met. You may have additional statuses (like "Suspended") if required. The entity has the following attributes:

• SurveyStatusID: A system-generated unique identifier. It is mandatory and is the primary identifier (primary key) of the entity.
• SurveyStatus: The name for each possible status of a survey. It is mandatory. Since it must be unique, we make it an alternate key.

Question

This entity contains the questions available for a survey. The entity has the following attributes:

• QuestionID: A system-generated unique identifier. It is mandatory and is the primary identifier (primary key) of the entity.
• Order: The order of the question among the questions in the survey. It is mandatory. Since it must be unique within each survey, we make it an alternate key.
• QuestionText: The text of the question. It is mandatory. Since it must be unique within each survey, we make it an alternate key.
• IsMandatory: A Boolean value that indicates whether the question must have a valid answer or may be left blank. It is mandatory.

QuestionType

This entity contains the different question types available in the system. Some possible values are "Open" (the respondent enters an answer), "Dropdown" (the respondent selects exactly one option from a list), "Multiple Choice" (the respondent selects one or more options from a list), or "Logical" (the respondent selects yes/no or true/false options). The entity has the following attributes:

• QuestionTypeID: A system-generated unique identifier. It is mandatory and is the primary identifier (primary key) of the entity.
• QuestionType: The type of question. It is mandatory. Since it must be unique, we make it an alternate key.

QuestionOption

This entity contains the pre-defined values from which respondents select answers to questions whose QuestionType is not "Open". The entity has the following attributes:

• QuestionOptionID: A system-generated unique identifier. It is mandatory and is the primary identifier (primary key) of the entity.
• Order: The display order of the option in the options It is mandatory. Since it must be unique for each question, we make it an alternate key.
• Value: The value in the options It is mandatory. Since it must be unique for each question, we make it an alternate key.

At this point, we have completed the "Survey Information" section of our design. Now, we start designing the data model to store responses to the surveys.

Response

This entity contains information about the respondent and the moment he/she starts and finishes responding to a survey. The entity has the following attributes:

• ResponseID: A system-generated unique identifier. It is mandatory and is the primary identifier (primary key) of the entity.
• BeginDate: The date and time when the respondent starts responding to the survey. It is mandatory.
• EndDate: The date and time when the respondent finishes responding to the survey. It is not mandatory.

Answer

This entity contains the respondent's answer for each question in the survey. If the QuestionType is "Open", then the respondent uses the Answer field to enter a written answer; otherwise the field is left empty. The entity has the following attributes:

• AnswerID: A system-generated unique identifier. It is mandatory and is the primary identifier (primary key) of the entity.
• Answer: The answer provided by the respondent. It is not mandatory.

AnswerOption

This entity contains the selected option(s) that a respondent has chosen as the answer for each question in the survey when the QuestionType is not "Open". One or more rows can be generated to reflect the option(s) selected as an answer to a specific question. The entity has the following attribute:

• AnswerOptionID: System generated unique identifier. It is mandatory and is the primary identifier (primary key) of the entity.

Respondent

This entity contains basic information about each respondent who responds to our surveys. The entity has the following attributes:

• RespondentID: A system-generated unique identifier. It is mandatory and is the primary identifier (primary key) of the entity.
• LoginName: The name used to log in to the system. It is mandatory. Since it must be unique for each respondent, we make it an alternate k
• Name: The full name of the respondent. It is mandatory.
• EmailAddress: The email address of the respondent. It is mandatory.

We are not including any additional information on how to authenticate the respondents here. Refer to the article "How to Store Login Data in a Database" on this blog to get some ideas for how to do that.

We are now done with the entities. The model looks like this:

Adding Relationships

Now that we have added all the entities in our model, we need to establish the relationships among them. To do this in Vertabelo, follow these steps:

1. Select the "Add new one to many relationship" icon in the tools list:

2. Once the tool is selected, click on the parent entity (we will use SurveyStatus as an example here). Then click on the child entity (in this example, Survey):

3. Once the relationship is created, use the "RELATIONSHIP PROPERTIES" section on the right side of the screen to modify the relationship name, add comments, and change its type as needed:

We now add the remaining relationships between the following entity pairs:

• Survey (parent) and Question (child) since a survey may include multiple questions.
• QuestionType (parent) and Question (child) since each question belongs to a single type, and a question type may be assigned to multiple questions.
• Question (parent) and QuestionOption (child) since a question not of the "Open" type may have multiple choices.
• Survey (parent) and Response (child) since a survey may be answered by multiple respondents.
• Response (parent) and Answer (child) since a response has answers to each question in the survey.
• Question (parent) and Answer (child) since a question in a survey has as many answers as there are responses to the survey.
• Answer (parent) and AnswerOption (child) since an answer may include multiple options (e.g., if the QuestionType is "Multiple Choice").
• Respondent (parent) and Response (child) since a respondent may submit multiple responses (e.g., to different surveys).
• QuestionOption (parent) and AnswerOption (child) since a question option may be included as an answer in multiple responses.

After adding these relationships, we now have the logical data model completed:

Converting the Logical Data Model Into a Physical Data Model

Once we have our logical data model, convert it into a physical one easily with just a couple of clicks.

1. Go to the Vertabelo navigation pane, right-click on the desired logical model, and then select "Generate physical model":

2. A dialog box appears and asks for some details such as the name of the model and the target database engine. Complete as appropriate, then click on "GENERATE PHYSICAL MODEL":

Vertabelo generates a physical model based on the selected target database engine. It adds all the columns required to convert the relationships among the entities in the logical model into foreign keys. You may want to do some minor adjustments, for example:

• Renaming the newly added columns or the foreign keys to comply with the naming standards of your organization.
• Adding IDENTITY or similar specifications to the columns selected as primary k
• Adjusting any data type that cannot be mapped from the logical model directly to the available data types on the target database engine.

Read the article "8 Things to Consider When Creating a Physical Data Model" on this blog to review the most important aspects to consider when creating a physical data model.

After applying those adjustments as appropriate, you now have a physical data model ready to be implemented, as shown below:

Now, simply select "Generate SQL Script" on the model toolbar to generate an SQL script for implementing the model:

Use This as a Starting Point for Your Online Survey Database Model

Although this is a relatively simple design, it serves as a starting point for any database design of an online survey system. Read the article "A Database Model for an Online Survey. Part 1" and its sequels "Part 2", "Part 3", and "Part 4" for more advanced features like conditional questions and survey administration.