Prototype Survey/Questionnaire Application

By Tony Marston

18th July 2006

Introduction
Database Schema - Survey/Questionnaire
Surveys and Questions
Question Options
Survey Answers
Organisation Structure

Introduction

The download of the Radicore framework includes several prototype applications which demonstrate the kind of application which the framework is designed to support. One of these is the SURVEY/QUESTIONNAIRE application, and the purpose of this article is to provide a description of that application so that you can see what it does as well as how it does it. This may prove useful in the event that you should ever want to build your own version of the prototype.

Database Schema - Survey/Questionnaire

This is the database schema used by this application showing all the entities (tables) and the relationships between them.

Figure 1 - Survey/Questionnaire Schema

answer_option table asset_type table default_prompt table location_address table location_type table number_option table org_type table question_prompt table risk_status table risk_weighting table survey_answer_dtl table survey_answer_hdr table survey_hdr table survey_question table survey_section table survey_type table tree_node table survey-prototype-001 (3K)

Surveys and Questions

The first part of any Surveys/Questionnaire system is the definition of the questions which need to be answered. This is satisfied with the following entities:

SURVEY_TYPE table
survey_type_id This is the unique identity for this entry.
survey_type_desc This is the description for this entry.

This identifies the different types of survey which may exist.

Figure 2 - Survey Type

survey-prototype-002 (9K)

SURVEY_HDR table
survey_id This is the unique identity for the entry which is generated by the system.
survey_name This is a short name for this entry.
survey_long_name This is a long name for this entry.
survey_type_id This is a foreign key to the SURVEY_TYPE table.

This identifies individual surveys, which consist of one or more sections each of which contains one or more questions.

Figure 3 - Survey Header

survey-prototype-003 (8K)

SURVEY_SECTION table
survey_id This is a foreign key to the SURVEY_HDR table.
section_id This is a value generated by the system which makes the primary key unique.
section_seq This allows the user to change the sequence in which the sections in this survey will be displayed.
section_name This is the description for this entry.

This identifies the various sections (question groups) which exist within a particular survey.

Figure 4 - Survey Section

survey-prototype-004 (12K)

It is possible to alter the sequence of sections by first selecting a section then pressing one of the "Move Up" or "Move Down" buttons. This will change the sequence number of the selected entry and also the next/previous entry (as appropriate) in order to swap them around. After the swap the selected entry will remain selected in case it needs to be moved again.


SURVEY_QUESTION table
survey_id This is a foreign key to the SURVEY_SECTION table.
section_id This is a foreign key to the SURVEY_SECTION table.
question_id This is a value generated by the system which makes the primary key unique.
question_seq This allows the user to change the sequence in which the questions in this section will be displayed.
question_text This is the question which is to be answered.
answer_type
  • Text - the answer is entered as free format text
  • Multiple Choice - the answer must be chosen from those entries defined on the ANSWER_OPTION table.
  • Number - the answer must be a number which falls within the min/max values defined on the NUMBER_OPTION table.
advice_text This gives advice which may be useful when constructing the answer.
risk_status_id This is a foreign key to the RISK_STATUS table.

Figure 5 - Survey Questions

survey-prototype-005 (16K)

It is possible to alter the sequence of questions in a section by first selecting a question then pressing one of the "Move Up" or "Move Down" buttons. This will change the sequence number of the selected entry and also the next/previous entry (as appropriate) in order to swap them around. After the swap the selected entry will remain selected in case it needs to be moved again.

Question Options

Each question can be comprised of a number of different variables. These are obtained from the following tables:

Figure 6 - A Survey Question

survey-prototype-006 (15K)

The question_text is free format.

If the answer_type is "multiple choice" the user is taken to the Answer Options screen to define the choices which apply to this question. The available choices will then be displayed as plain text.

If the answer_type is "number" the user is taken to the Number Options screen to define the choices which apply to this question. The available choices will then be displayed as plain text.

The prompt_list is taken from the QUESTION_PROMPT table.

The advice_text is free format.

The risk_status is chosen from the available entries on the RISK_STATUS table.


DEFAULT_PROMPT table
survey_id This is a foreign key to the SURVEY_HDR table.
prompt_id This is a system generated number which makes this entry unique.
prompt_desc This is the description for this entry.

This holds a range of possible values which may be linked to any question in the survey.

Figure 7 - Default Prompt

survey-prototype-007 (10K)

QUESTION_PROMPT table
survey_id This is a foreign key to the SURVEY_QUESTION table.
section_id This is a foreign key to the SURVEY_QUESTION table.
question_id This is a foreign key to the SURVEY_QUESTION table.
prompt_id This is a foreign key to the DEFAULT_PROMPT table.

This is used to identify those values on the DEFAULT_PROMPT table which apply to a particular question.

Figure 8 - Question Prompt

survey-prototype-008 (11K)

ANSWER_OPTION table
survey_id This is a foreign key to the SURVEY_QUESTION table.
section_id This is a foreign key to the SURVEY_QUESTION table.
question_id This is a foreign key to the SURVEY_QUESTION table.
answer_id This is a system generated number which makes this entry unique.
answer_seq This allows the user to change the sequence in which the answers to this question will be displayed.
answer_text This is the text that will be displayed to the user.
jumpto_section_seq This is used to cause the system to jump to the start of another section of questions, thus skipping all intermediate questions. This should always be to a later section and not a earlier section.

This table is only used where answer_type is "Multiple Choice".

Figure 9 - Answer Options

survey-prototype-009 (10K)

NUMBER_OPTION table
survey_id This is a foreign key to the SURVEY_QUESTION table.
section_id This is a foreign key to the SURVEY_QUESTION table.
question_id This is a foreign key to the SURVEY_QUESTION table.
min_value This is the minimum value.
max_value This is the maximum value.

This table is only used where answer_type is "Number".

Figure 10 - Number Option

survey-prototype-010 (7K)

RISK_STATUS table
risk_status_id This is the unique identity for the entry which is generated by the system.
risk_status_desc This is the description for this entry.

Figure 11 - Risk Status

survey-prototype-011 (7K)

Survey Answers

The same survey may need to be answered by several different people, or the same person may need to repeat the survey at regular intervals, so each set of answers needs to be given its own identity.

SURVEY_ANSWER_HDR table
survey_answer_id This is the unique identity for the entry which is generated by the system.
user_id This is a foreign key to the USER table on the MENU database.
answer_date This is the date on which this series of answers was first started.
survey_id This is a foreign key to the SURVEY_HDR table.
node_id This is a foreign key to a location entry on the TREE_NODE table.
is_complete A YES/NO flag which indicates if all the questions have been answered. This excludes any questions which were skipped due to a jump which was specified on the ANSWER_OPTION table.

Figure 12 - Survey Answer Header

survey-prototype-012 (9K)

The "Update Status" button is used to mark the survey as complete, but this can only be done when there are no outstanding questions. Once the survey is marked as complete it is not possible to modify any answers.


SURVEY_ANSWER_DTL table
survey_answer_id This is a foreign key to the SURVEY_ANSWER_HDR table.
survey_id This is a foreign key to the SURVEY_QUESTION table.
section_id This is a foreign key to the SURVEY_QUESTION table
question_id This is a foreign key to the SURVEY_QUESTION table.
answer_text This is the answer to this question. It may be a string of text, a number, or a link to and entry in the ANSWER_OPTION table.
weighting_id This is a foreign key to the RISK_WEIGHTING table.
measure_adequate This records a value of YES or NO.

Figure 13 - Survey Answer Detail

survey-prototype-013 (13K)

When this screen is activated it will enable the user to access all the questions within the selected survey, with their current answers. It will start at question #1, with a scrolling area at the bottom of the screen which will allow navigation to other questions. Note that answers to some questions may cause other questions to be skipped because they are no longer applicable, in which case the scrolling mechanism will jump over them and display only those questions for which answers are required.

Present Measure (the answer to the question) may be supplied as a string of text, a number, or a choice from a dropdown list. This is determined by answer_type in the SURVEY_QUESTION table.

Risk Weighting must be chosen from one of the available options.

Present Measure Adequate must be set to either YES or NO.

Note that the answers do not have to be supplied all in one go. It is possible for the user to leave any answers for another time. It is possible to supply or even change any answers up until the time the status of the answers is changed to COMPLETE.


RISK_WEIGHTING table
weighting_id This is the unique identity for the entry which is generated by the system.
weighting_desc This is the description for this entry.

Figure 14 - Risk Weighting

survey-prototype-014 (8K)

Organisation Structure

An organisation may have several locations, and it would therefore be necessary to keep a track of completed questionnaires by location. The most flexible way to represent an organisation's structure is in a hierarchical or "tree structure", as shown in Figure 15.

Figure 15 - Organisation Structure

survey-prototype-015 (10K)

This function allows the user to view the current organisational structure as well as make adjustments to it by using the buttons which are available in the navigation bar. The rules for adding nodes are as follows:


ORG_TYPE table
org_type_id This is the unique identity for the entry which is generated by the system.
org_type_desc This is the description for this entry.
image_fname This is the name of the image file which will be used as the icon in the display of the organisational tree.

Figure 16 - Organisation Type

survey-prototype-016 (8K)

Note that each type can have its own icon so that it can be distinguished in the organisation structure.


LOCATION_TYPE table
location_type_id This is the unique identity for the entry which is generated by the system.
location_type_desc This is the description for this entry.
image_fname This is the name of the image file which will be used as the icon in the display of the organisational tree.

Figure 17 - Location Type

survey-prototype-017 (9K)

Note that each type can have its own icon so that it can be distinguished in the organisation structure.


ASSET_TYPE table
asset_type_id This is the unique identity for the entry which is generated by the system.
asset_type_desc This is the description for this entry.
image_fname This is the name of the image file which will be used as the icon in the display of the organisational tree.

Figure 18 - Asset Type

survey-prototype-018 (8K)

Note that each type can have its own icon so that it can be distinguished in the organisation structure.


TREE_NODE table
node_id This is the unique identity for the entry which is generated by the system.
node_desc This is the description for this entry.
node_type
  • Asset
  • Location
  • Organisation
node_depth This indicates the depth of the current node in the hierarchy, with ORGANISATIONS always at depth 1. All child nodes have a value for depth which is 1 greater than that of their parent node.
node_id_snr This is a foreign key to the same table which identifies the parent node in the hierarchy.
org_type_id This is a foreign key to the ORG_TYPE table.
location_type_id This is a foreign key to the LOCATION_TYPE table.
asset_type_id This is a foreign key to the ASSET_TYPE table.

Note that this table has a different maintenance screen depending on the node_type.

Figure 19 - Tree Node (Organisation)

survey-prototype-019 (8K)

Figure 20 - Tree Node (Location)

survey-prototype-020 (10K)

The additional data required for addresses is stored on the LOCATION_ADDRESS table.

Figure 21 - Tree Node (Asset)

survey-prototype-021 (8K)
LOCATION_ADDRESS table
node_id This is a foreign key to the TREE_NODE table.
address_line1 This is the 1st line of the address.
address_line2 This is the 2nd line of the address.
town This is the address town.
county This is the address county.
postcode This is the address postcode.
country This is the address country.
telephone This is the main telephone number for this address.
fax This is the main fax number for this address.

This table is only used when node_type on the TREE_NODE table is set to "location".


© Tony Marston
18th July 2006

http://www.tonymarston.net
http://www.radicore.org

counter