The Big Idea

We want to be able to survey users. We want a non-technical person to be able to design surveys from HTML forms. We want someone who is not a site-admin to be able to review answers (we just give them admin over the survey package.)

Data-Model

We use the following tables:

• surveys General Information about a survey, like the name of the survey, display options etc.
• survey-sections A Survey is made up of one or more sections, which are displayed each on one page, when taking the survey. In addition to structuring the survey this allows for branching. Each section is associated to a survey.
• survey_branches This table links sections and conditions. A branch is a section that is not always displayed but only if some condition is met, that is, if a user has given some answer to a specific question earlier on in the survey.
• survey_conditions A condition for executing a certain branch of a survey. That is a question (which must be boolean or multipe choice) and one answer to that question.
• survey_questions Contains data about questions. Each question is associated to a section.
• survey_block_questions Block sections are sections that contain only questions that have the same set of answers and are arranged as a block, for example having answers like "Agree fully, agree, unsure, disagree, disagree heavily" and ask for the attitude of a user concerning a list of statements.
• survey_question_choices For multiple choice questions, the different choices are held in this table
• survey_predefined_questions Holds questions in the question catalogue. These questions are treated in a special way.
• survey_predef_question_choices Like survey_question_choices, only forpredefined questions.
• survey_templates Holds information about survey templates. This includes a template_id, that is referenced in surveys, a file_name (under /packages/acs-templating/resources/forms/surveys) and a descriptive name.
• survey_responses Describes a user's response to a survey. The flag "finished_p" allows to look for unfinished responses of a user.
• survey_question_responses The answer of a user to an individual question. It is possible, that a user answers multiple times to the same survey, so the answers are identified by the response_id.

The philosophy about storing the users responses is to use one single table to store all responses, i.e., we do not create a new table when we create a new survey. In order to make it possible to store all kinds of data in one table, we give the survey_question_responses table five columns.

	-- if the user picked a canned response
	choice_id		references survey_question_choices,
	boolean_answer		char(1) check(boolean_answer in ('t','f')),
	clob_answer		clob,
	number_answer		number,
	attachment_answer       integer references cr_revisions(revision_id) 
	varchar_answer		varchar(4000),

Only one of the columns will be not-null.

Why the CLOB in addition to the varchar? The CLOB is necessary when people are using this system for proposal solicitation (people might type more than 4000 characters).

NOTE: Postgresql uses a text column in place of CLOB.

Attachment_answer allows for uploaded files to be stored in the content-repository

.

• User Interface
  
• Administration Interface