Schema

campaigns

Field Names	Attributes	Type	Description
id	PK	integer
title		varchar	Title that describes campaign
html		text	Educational component in HTML format
active		boolean	Active/enforcing campaign status
start_time		datetime	Campaign start time
stop_time		datetime	Campaign stop time
pass_percent		smallint	Quiz score percentage considered passing
failure_threshold		integer	Number of times a user can fail a campaign
questions_weighted		boolean	Question weight (order) is followed

questions

Field Names	Attributes	Type	Description
id	PK	integer
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
id	PK	integer
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
id	PK	integer
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@organization".

campaign_roles

Field Names	Attributes	Type	Description
id	PK	integer
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
grace_period		varchar	Period of time where skip-mercy is granted
reminder_interval		varchar	Reminder time interval when skipped

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

attempts

Field Names	Attributes	Type	Description
id	PK	integer
user_identifier		integer	Unique user identifier
campaign_id	FK: campaigns.id	integer	User role associated with the campaign role
start_time		datetime	Time of first contact with campaign
last_skip		datetime	Last time skipped
score		smallint	Highest score achieved for the campaign
attempts		smallint	Number of campaign completion attempts