User Table:
This table contains the information about users and login password. We have three types of users: students(those completeing the survey),
faculty(those accessing statistics and other data submitted), and
administrators(the user who can edit the survey year to year). Once
a student completed the survey his information is deleted from this
table, so we know that a student has not completed the survey if their
name is in the table. By deleting a student once they have completed
the survey entirely we are able to keep their answers anonymous. This
table also stores passwords which we will encrypt in some manner.
+----------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------------+------+-----+---------+-------+ | userID | bigint(20) unsigned | NO | PRI | | | | first | varchar(25) | NO | | | | | last | varchar(25) | NO | | | | | type | varchar(25) | NO | | | | | password | varchar(255) | NO | | | | +----------+---------------------+------+-----+---------+-------+
Student Table:
This table contains the information about user type student.
+----------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+---------------------+------+-----+---------+-------+ | studentID | bigint(20) unsigned | NO | PRI | | | | graduationYear | smallint(6) | NO | | | | | advisorID | bigint(20) | NO | | | | | numCourses | tinyint(4) | YES | | NULL | | +----------------+---------------------+------+-----+---------+-------+
Question Table:
This table contains the text of each question for the survey and specifies how each question will be displayed, e.g., short answers, check boxes, multiple-choice, or headers.
+--------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+---------------------+------+-----+---------+----------------+ | questionID | bigint(20) unsigned | NO | PRI | NULL | auto_increment | | questionText | varchar(255) | YES | | NULL | | | category | varchar(255) | YES | | NULL | | +--------------+---------------------+------+-----+---------+----------------+
Survey Form Table:
This table contains the list of questions included in each survey and the order in which they appear. It also lets us know the header under which each question should appear.
+-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | surveyID | varchar(25) | NO | PRI | | | | questionID | bigint(20) | NO | PRI | | | | sectionNum | bigint(20) | YES | | NULL | | | sequenceNum | bigint(20) | YES | | NULL | | +-------------+-------------+------+-----+---------+-------+
Survey Answers Table:
This table contains the text of the answers provided by each student and specifies which question was answered on which survey by which student.
+------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+-------+ | surveyID | varchar(25) | NO | PRI | | | | questionID | bigint(20) | YES | | NULL | | | studentID | bigint(20) | YES | | NULL | | | answerText | varchar(255) | YES | | NULL | | +------------+--------------+------+-----+---------+-------+