Schema

Postgres Database Schema

users

column name	data type	details
id	integer	not null, primary key
username	string	not null, indexed, unique
first_name	string	not null
last_name	string	not null
email	string	not null, indexed, unique
password_digest	string	not null
session_token	string	not null, indexed, unique
created_at	datetime	not null
updated_at	datetime	not null

• index on username, unique: true

• index on email, unique: true

• index on session_token, unique: true

• belongs to for responses(polymorphic)

questions

column name	data type	details
id	integer	not null, primary key, indexed
title	string	not null
type	string	not null
closed	boolean	not null default false
response_limit	integer	not null default 1
allow_unregistered	boolean	not null default false
group_id	integer	not null, indexed, foreign key
author_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• author_id references users
• group_id references groups
• index on author_id
• type: single answer, multiple answers, text response, true/false

question_options

column name	data type	details
id	integer	not null, primary key, indexed
label	string	not null
question_id	integer	not_null, indexed, foreign_key
created_at	datetime	not null
updated_at	datetime	not null

• question_id links to the questions table

mult_responses

column name	data type	details
id	integer	not null, primary key, indexed
title	string	not null
question_options_id	integer	not null, indexed, foreign_key
correct	boolean	not null, default false
registerable_id	integer	not null, indexed, foreign_key
registerable_type	string	not null
created_at	datetime	not null
updated_at	datetime	not null

• polymorphic association with visitors and users
• question_options_id links with question_options table

text_responses

column name	data type	details
id	integer	not null, primary key
body	string	not null
question_id	integer	not null, indexed, foreign_key
registerable_id	integer	not null, indexed, foreign_key
registerable_type	string	not null
created_at	datetime	not null
updated_at	datetime	not null

• responder_id references users

• index on responder_id

• question_id references question

• index on question_id

• registerable_id and registerable_type create a polymorphic association with visitors and users

groups

column name	data type	details
id	integer	not null, primary key
name	string	not null
user_id	integer	not null, indexed, foreign_key
created_at	datetime	not null
updated_at	datetime	not null

• association with questions
• belongs to user

visitors

column name	data type	details
id	integer	not null, primary key
visitor_username	string	not null
password_digest	string	not null
session_token	string	not null, indexed, unique
created_at	datetime	not null
updated_at	datetime	not null

• belongs to for responses(polymorphic)

unregistered_visitors

column name	data type	details
id	integer	not null, primary key
session_token	string	not null, indexed, unique

• allows user without username/password to respond to a question

reports (bonus)

column name	data type	details
id	integer	not null, primary key
response_id	integer	not null, indexed, foreign key
question_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• response_id references responses
• question_id references questions

question_reports (bonus)

column name	data type	details
id	integer	not null, primary key
question_id	integer	not null, indexed, foreign key
report_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• report_id references reports
• question_id references questions

Quizzes

• Feels redundant to reports for now, will think on another bonus that excites me