Dynamic Survey Project: Database Relational Schemata

Users/Groups

groups (id, name, char_ref, description)

users (id, first_name, last_name, username, password, email)

users_to_groups (group_id, user_id)

FK group_id -> groups (not null) (not unique)

FK user_id -> users (not null) (not unique)

Surveys

surveys (id, name, char_ref, start_date, end_date, description)

sections (id, survey_id, title, description, seq_num)

FK survey_id -> surveys (not null) (not unique)

questions (id, survey_id, q_type, q_text, instructions, required, seq_num)

FK survey_id -> surveys (not null) (not unique)

answers (id, question_id, ans_text, description, seq_num)

FK question_id -> questions (not null) (not unique)

surveys_to_groups (group_id, survey_id)

FK group_id -> groups (not null) (not unique)

FK survey_id -> surveys (not null) (not unique)

Survey Responses

survey_responses (id, survey_id, user_id, date)

FK survey_id -> surveys (not null) (not unique)

FK user_id -> users (not null) (not unique)

question_responses (id, question_id, response_id, free_response, comments)

FK question_id -> questions (not null) (not unique)

FK response_id -> survey_responses (not null) (not unique)

qresponses_to_answers (qresponse_id, answer_id)

FK qresponse_id -> question_responses (not null) (not unique)

FK answer_id -> answers (not null) (not unique)