Figure 1 - Survey/Questionnaire Schema
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.
This is the database schema used by this application showing all the entities (tables) and the relationships between them.
Figure 1 - Survey/Questionnaire Schema
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_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_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_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
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_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 |
|
| 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
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.
Each question can be comprised of a number of different variables. These are obtained from the following tables:
Figure 6 - A Survey Question
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.
| 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_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_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_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
| 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
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_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
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_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
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.
| 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
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
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_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
Note that each type can have its own icon so that it can be distinguished in the organisation structure.
| 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
Note that each type can have its own icon so that it can be distinguished in the organisation structure.
| 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
Note that each type can have its own icon so that it can be distinguished in the organisation structure.
| 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 |
|
| 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)
Figure 20 - Tree Node (Location)
The additional data required for addresses is stored on the LOCATION_ADDRESS table.
Figure 21 - Tree Node (Asset)
| 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".