---
# System prepended metadata

title: DB Design for K-aA-hoot!

---

# DB Design for K-aA-hoot!

![](https://i.imgur.com/cUxXfkh.png)

## Users Table
- id, integer, primary key 
  - used to identify user accounts
- GitHub, varchar 
  - user's GitHub handle
- email, email 
  - user's GitHub email (in case they update the handle)
- cohort, varchar, allow null
  - user's current cohort, editable by user
- created_at, timestamp 
  - when this account was created
- updated_at, timestamp 
  - when this account was last updated
- access_level, integer
  - determines permissions for account
  - default value should be 0, a/A Staff will be 1 or higher

## Questions Table
- id, integer, primary key
  - used to identify questions
- question_content, varchar
  - The body of the question itself
- question_supplemental, varchar, allow null
  - link to screenshots or clarifying data for the prompt (code blocks, etc)
  - these links will point to resources in an AWS bucket

## Answers Table
- id, integer, primary key
  - used to identify answer records
- question_id, integer, foreign key 
  - references question these answers are linked to
- choices, jsonb
  - the choices for responses to a question, ordered such as {1: {content: "content goes here", correct: true}, 2: {content: "content goes here", correct: false}
  - all choices labeled with correct: true will award points, those will false will not

## Scoreboards Table
- user_id, integer, foreign key
  - links to the user this score belongs to
- week, integer
  - the week this score is linked to
- created_at, timestamp
  - when the game this score is for happened
- session_id, varchar
  - the "lobby" or "session" that was played to completion
  - this will be used to find all scores for specific games
- score, int
  - the score the player earned from this game sesson
