BELOW ARE MY PROPOSED CHANGES TO EXISTING TABLES ================================================ create table surveys ( survey_id integer constraint surveys_survey_id_fk references acs_objects (object_id) on delete cascade constraint surveys_pk primary key, name varchar(4000) constraint surveys_name_nn not null, description text constraint surveys_desc_nn not null, description_html_p boolean not null, enabled_p boolean not null, -- limit to one response per user single_response_p boolean not null, editable_p boolean not null, single_section_p boolean not null, type varchar(20), display_type varchar(20), package_id integer constraint surveys_package_id_nn not null constraint surveys_package_id_fk references apm_packages (package_id) on delete cascade, -- I ADDED BELOW THIS -- this lets you randomize the display of questions from various questions... and -- bypasses survey_questions sort order and groupings by section. this makes it -- possible in health surveys to ask 100 questions with each section dispersed -- throughout the survey, this helps people to not sub-consciously 'cheat' the -- test by knowing how questions relate to one another and knowing that question -- 73 actually relates to 74 and thus drawing different conclusions about -- the implied meaning then the question objectively asks for - this is much more -- difficult to do if the questions "jump" around in relation to various sections) order_type varchar(20) constraint surveys_order_type_nn not null default 'fixed' constraint surveys_order_type_ck check(order_type in ('fixed', 'random')) ); ======================================================== create table survey_sections ( section_id integer constraint survey_sections_section_id_fk references acs_objects (object_id) constraint survey_sections_pk primary key, survey_id integer constraint survey_sections_survey_id_nn not null constraint survey_sections_survey_id_fk references surveys, name varchar(4000) constraint survey_sections_name_nn not null, description text constraint survey_sections_desc_nn not null, description_html_p boolean, -- I ADDED BELOW THIS -- (if this is 't' the default value in survey_questions is 't' otherwise it is 'f') scored_section boolean default 'f', -- if this is 't' then the system will not just create a sum of points, but -- will convert that sum into a comment - like a "A" or "B+" or "you need to -- watch your diet more carefully" point_translation boolean default 'f' ); ========================================================= create table survey_questions ( question_id integer constraint survey_q_question_id_fk references acs_objects (object_id) on delete cascade constraint survey_q_question_id_pk primary key, section_id integer constraint survey_q_section_id_fk references survey_sections on delete cascade, sort_order integer constraint survey_q_sort_order_nn not null, question_text text constraint survey_q_question_text_nn not null, abstract_data_type varchar(30) constraint survey_q_abs_data_type_ck check (abstract_data_type in ('text', 'shorttext', 'boolean', 'number', 'integer', 'choice','date')), required_p boolean, active_p boolean, presentation_type varchar(20) constraint survey_q_pres_type_nn not null constraint survey_q_pres_type_ck 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) constraint survey_q_pres_alignment_ck check(presentation_alignment in ('below','beside')), -- I ADDED BELOW THIS -- this tells the system whether or not this is one of the questions to be -- scored - if it is, it will wait for input from the teacher on text -- and varchar responses but automatically input values from the -- scoring_points field in survey_question_choices) scoring_points_p boolean, -- this tells the system whether or not there comments are permitted, -- it would then look in the newly created survey_scoring_comments -- table and find the comment to use - or give the Admin a selection) scoring_feedback_p boolean, -- this says whether or not there are multiple pre-defined comments -- which can be selected by the teacher) scoring_feedback_one_response_p boolean ); =============================================================== create table survey_question_choices ( choice_id integer constraint survey_qc_choice_id_nn not null constraint survey_qc_choice_id_pk primary key, question_id integer constraint survey_qc_question_id_nn not null constraint survey_qc_question_id_fk references survey_questions on delete cascade, -- human readable label varchar(500) constraint survey_qc_label_nn not null, -- might be useful for averaging or whatever, generally null numeric_value numeric, -- lower is earlier sort_order integer, -- I ADDED BELOW THIS -- this would be the number of points a certain selection automatically gets - -- i.e. if there is only one right answer for a question that one would get -- a "1" and the rest would get "0") scoring_points integer ); =============================================================== -- this table stores the answers to each question for a survey -- we want to be able to hold different data types in one long skinny table -- but we also may want to do averages, etc., so we can't just use CLOBs create table survey_question_responses ( response_id integer constraint survey_qr_response_id_nn not null constraint survey_qr_response_id_fk references survey_responses on delete cascade, question_id integer constraint survey_qr_question_id_nn not null constraint survey_qr_question_id_fk references survey_questions on delete cascade, -- if the user picked a canned response choice_id integer constraint survey_qr_choice_id_fk references survey_question_choices on delete cascade, boolean_answer boolean, clob_answer text, number_answer numeric, varchar_answer text, date_answer timestamp, attachment_answer integer constraint survey_q_response_item_id_fk references cr_items(item_id) on delete cascade, -- I ADDED BELOW THIS -- this gets filled in from the survey_question_choices scoring_points field, -- but on text and varchar responses, can be manually entered by admin - i.e. -- when scoring a essay response to a test, etc.) scoring_points integer, -- if the admin set a canned comment -- this gets filled with pre-defined admin comments -i.e. when responding to a short -- essay - from the survey scoring_feedback_id integer constraint survey_qr_grading_comment_id_fk references survey_grading_comments on delete cascade, -- if the admin wrote a custom comment applicable only to this one participants response, -- this comment goes here scoring_custom_feedback text );