Each survey is presented as a form that contains a set of questions. Each individual question can accept one of several different types of responses.
Real-world examples of online surveys usage include:
create table survsimp_surveys (
survey_id integer primary key,
name varchar(100) not null,
-- short, non-editable name we can identify this survey by
short_name varchar(20) unique not null,
description varchar(4000) not null,
description_html_p char(1) default 'f'
constraint survsimp_sur_desc_html_p_ck
check(description_html_p in ('t','f')),
creation_user not null references users(user_id),
creation_date date default sysdate,
enabled_p char(1) default 'f' check(enabled_p in ('t','f')),
-- limit to one response per user
single_response_p char(1) default 'f' check(single_response_p in ('t','f')),
single_editable_p char(1) default 't' check(single_editable_p in ('t','f'))
);
Any number of surveys can be enabled at a given time. A short name is used
to identify surveys in a human-readable format. The description is a longer
text that contains, for instance, instructions on how to fill the survey
and is presented to the user taking the survey. Each survey can be configured
to allow multiple responses or a single response. In the latter case, the
single allowable response can be either editable or non-editable.
Each survey consist of a set of questions, stored in the survsimp_questions table:
create table survsimp_questions (
question_id integer primary key,
survey_id not null references survsimp_surveys,
sort_key integer not null,
question_text clob not null,
-- can be 'text', 'shorttext', 'boolean', 'number', 'integer', 'choice'
abstract_data_type varchar(30) not null,
required_p char(1) check (required_p in ('t','f')),
active_p char(1) check (active_p in ('t','f')),
presentation_type varchar(20) not null
check(presentation_type in ('textbox','textarea','select','radio', 'checkbox', 'date', 'upload_file')),
-- for text, "small", "medium", "large" sizes
-- for textarea, "rows=X cols=X"
presentation_options varchar(50),
presentation_alignment varchar(15) default 'below'
check(presentation_alignment in ('below','beside')),
creation_user references users not null,
creation_date date default sysdate
);
Each question has a sort key that defines the relative order of a question
within a survey. The question text contains the question itself.
Questions are typed, by means of an abstract data type, and each question is presented according to a presentation type that matches their abstract data type. Question presentation can be individually tailored by using type-dependent presentation options. An alignment option is provided for all questions, to allow flexibility in positioning the response field relative to the question text.
Questions can be categorized, by being mapped to categories. This is done using the site-wide category system, namely by storing categories in the global categories table, with category_type as "survsimp", and by using the site_wide_category_map table to map categories to individual questions.
Some questions can only allow the user to pick from a limited set of valid responses. Such question choices are kept in the survsimp_question_choices table:
create table survsimp_question_choices ( choice_id integer not null primary key, question_id not null references survsimp_questions, -- human readable label varchar(500) not null, -- might be useful for averaging or whatever, generally null numeric_value number, -- lower is earlier sort_order integer );Each question choice belongs to one question. A label, which is the value that is to be presented to the user for selection, is stored along with a numerical value that can be used for weighing or averaging purposes.
As with questions, question choices also have a sort key that defines their relative order within the set of choices for a given question.
The survsimp_responses records responses to surveys:
create table survsimp_responses (
response_id integer primary key,
survey_id not null references survsimp_surveys,
-- scope is user, public or group
scope varchar(20),
user_id references users,
group_id references user_groups,
constraint survsimp_responses_scopecheck check
((scope='group' and group_id is not null)
or (scope='public' and group_id is null)
or (scope='user' and group_id is null)),
title varchar(100),
submission_date date default sysdate not null,
ip_address varchar(50),
-- do we sent email if
notify_on_comment_p char(1) default 'f'
check(notify_on_comment_p in ('t','f')),
-- proposal can be public, private, or deleted
proposal_state varchar(30) default 'private'
check(proposal_state in ('public','private', 'deleted'))
);
Each response is scoped and constraints are set appropriately. A response
can be also interpreted as a proposal, in which case additional information
is maintained, such as the proposal title, proposal visibility and a flag
to enable or disable notification upon comments.
The survsimp_question_responses table stores the actual answers in a response, for each question:
create table survsimp_question_responses (
response_id not null references survsimp_responses,
question_id not null references survsimp_questions,
-- if the user picked a canned response
choice_id references survsimp_question_choices,
boolean_answer char(1) check(boolean_answer in ('t','f')),
clob_answer clob,
number_answer number,
varchar_answer varchar(4000),
date_answer date,
-- columns useful for attachments, column names
-- lifted from file-storage.sql and bboard.sql
-- this is where the actual content is stored
attachment_answer blob,
-- file name including extension but not path
attachment_file_name varchar(500),
attachment_file_type varchar(100), -- this is a MIME type (e.g., image/jpeg)
attachment_file_extension varchar(50) -- e.g., "jpg"
);
Depending on the type of the question, one of several different fields
is used to store the value of the user's answer. Multiple fields are used
instead of a single generic (e.g., clob) field so that statistics can be
more easily calculated.
The only views defined in the Survey-Simple data model are used to extract only the latest response from each user to each survey :
create or replace view survsimp_responses_unique as
select r1.* from survsimp_responses r1
where r1.response_id=(select max(r2.response_id)
from survsimp_responses r2
where r1.survey_id=r2.survey_id
and r1.user_id=r2.user_id);
create or replace view survsimp_question_responses_un as
select qr.*
from survsimp_question_responses qr, survsimp_responses_unique r
where qr.response_id=r.response_id;
There is a single index defined:
create index survsimp_response_index on survsimp_question_responses (response_id, question_id);The following sequences are used to generate unique values for the primary keys:
create sequence survsimp_survey_id_sequence start with 1; create sequence survsimp_question_id_sequence start with 1; create sequence survsimp_choice_id_sequence start with 1; create sequence survsimp_response_id_sequence start with 1;The Survey-Simple administrator group also gets created:
administration_group_add ('Simple Survey System Staff', short_name_from_group_name('survsimp'),
'survsimp', NULL, 'f', '/survsimp/admin/');
.ini configuration
file under /parameters.
You should test creating a new survey, editing a survey, enabling a survey, responding to a survey and viewing responses.
Original authors were Philip Greenspun (philg@mit.edu) and Rajeev Surati (raj@alum.mit.edu). The original data model is dated February 9, 2000.
This documentation was written by Nuno Santos (nuno@arsdigita.com), who is also the module maintainer.
| Document Revision # | Action Taken, Notes | When? | By Whom? |
|---|---|---|---|
| 0.1 | Creation | 08/29/2000 | Nuno Santos |
Last modified: $Date $