Schema

Schema Template

table_name

Field Names	Attributes	Type	Description
first_field	PK	text
second_field	FK: table_name.value	int
third_field

Schema

campaigns

Field Names	Attributes	Type	Description
title		varchar	Title that describes campaign
html		text	Educational component in HTML format
active		boolean	Active/enforcing campaign status
grace_period		time	Period of time where skip-mercy is granted
start_date		datetime	Campaign start time
stop_date		datetime	Campaign stop time
pass_percent		smallint	Quiz score percentage considered passing
questions_weighted		boolean	Question weight (order) is followed

questions

Field Names	Attributes	Type	Description
campaign_ID	FK: campaigns.ID	integer	Campaign associated with question
answers_weighted		boolean	Answer weight (order) is followed
weight		integer	Weight of question for ordering purposes
html		text	Question content in HTML format
multiple_answers		boolean	Multiple answers are accepted

answers

Field Names	Attributes	Type	Description
question_ID	FK: questions.ID	integer	Question associated with answer
weight		integer	Weight of answer for ordering purposes
html		text	Answer content in HTML format
correct		boolean	Answer is correct
explanation		text	Explains why an answer is or isn't correct

user_roles

Field Names	Attributes	Type	Description
local_name		varchar	Human-readable role name
external_name		varchar	System-readable role name

A user role would have a local_name (human-readable) like "Staff" and an external_name that would match up with the response from the external authentication system (CAS), such as "staff@ship.edu".

campaign_roles

Field Names	Attributes	Type	Description
campaign_ID	FK: campaigns.ID	integer	Campaign associated with the campaign role
user_role_ID	FK: user_roles.ID	integer	User role associated with the campaign role

This table associates campaigns with the relevant user roles in order to facilitate the logic involved with, for example, "Staff must complete campaign Z."