Geeks Who Drink: Motherbrain Rounds and Questions Schema

Currently there are several changes to Motherbrain needed to accomodate the mobile and Quizmaster applications.

Round Types

During the course of a quiz, it's necessary for the mobile app and Quizmaster dashboard to be able to determine the type of Round being played.

These are the following round types:

Standard

1 question
1 answer

Currently referred to as "Theme Round" in various places in Motherbrain

Multiple choice

1 question
multiple possible answers
1 correct answer

Audio round

No question (an audio clip played IRL by quizmaster), answer usually consists of two parts: an Artist Name and a Song Title.
Audio rounds can also contain audio clips from multiple artists, in which case each answer would be 2 parts:
- Artist 1 Name
- Artist 2 Name

Visual Round

Question is an image that should be uploaded to Motherbrain (currently projected to the audience IRL.)
Each "question" in the round is related to an area of the image, but there are no actual question content in these rounds.

Speed Round / Multiple Answers

16 possible answers across 8 questions. Each question has 1 or many answers.

Questions are in the form of "Name 2 of the four members of Josie and the Pussycats"

Changes Needed

As you can see in the schema for the round table, Motherbrain has a roundType field. Unfortunately, this field is used inconsistently, often also holding round information (and even that's not consistent.)

The proposed change would be to add validation to the round type field, that would only allow it to a be one of several pre-set round types, which would in turn effect the format of its questions and answers.

Accepted values for `roundType`

The roundType field should only allow the following values.

standard
multiple_choice
audio
visual
multiple_answers

Alternate `question_answer` schema

Depending on the selected roundType, the question_answer records associated with that round should enforce the following schema changes:

standard

pointValue should be a required field

multiple_choice

question should contain the question content (if applicable)
answer_choices should be a serialized JSON array of answer choices to be presented

[
  "First answer choice",
  "Second answer choice"
]

answer should contain the one correct answer

audio

question should be null
answer should be a serialized JSON object that allows the following keys:

{
  "artist": "Artist name value",
  "title": "Artist title",
  "artist_2": "Second artist name",
  "title_2": "Second title"
}

NB: The keys artist_2 and title_2 are to allow for questions that take the form of 2 artist names or 2 song titles.

visual

Round should store a url for the round's associated image.

Allow for null values for questions

multiple_answers

question should contain question content
answer should be a serialized JSON array of acceptable answers
answer_count a number of allowed answers to the question

Example:

{
  "question": "Name 2 members of The Goonies",
  "answer": ["Mikey", "Mouth", "Data", "Chunk", "Brand", "Andy", "Steph", "Sloth"],
  "answer_count": 2
}

Current Schema

There are two tables used to store Quiz data: round and question_answer.

round

+---------------------+---------------+------+-----+---------+----------------+
| Field               | Type          | Null | Key | Default | Extra          |
+---------------------+---------------+------+-----+---------+----------------+
| round_id            | int(11)       | NO   | PRI | NULL    | auto_increment |
| state               | varchar(30)   | YES  |     | NULL    |                |
| roundType           | varchar(20)   | YES  |     | NULL    |                |
| roundTitle          | varchar(255)  | NO   | MUL | NULL    |                |
| roundNumber         | varchar(2)    | YES  |     | NULL    |                |
| user_id             | int(11)       | YES  | MUL | NULL    |                |
| notes               | varchar(1000) | YES  |     | NULL    |                |
| virtual_round_eight | varchar(1)    | YES  |     | NULL    |                |
+---------------------+---------------+------+-----+---------+----------------+

[
  {
    "notes": "Just name it.",
    "roundNumber": null,
    "roundTitle": "Four-Letter Words",
    "roundType": "Theme Round",
    "round_id": 11628,
    "user_id": 30,
    "virtual_round_eight": null
  },
  {
    "notes": "Each song title or artist name includes the name of a Major League Soccer team.",
    "roundNumber": null,
    "roundTitle": "The Other American Football",
    "roundType": "Audio Round 2",
    "round_id": 17473,
    "user_id": 148,
    "virtual_round_eight": null
  },
  {
    "notes": "Tell me if I'm talking about a Burt (spelled either way), or an Ernie.",
    "roundNumber": null,
    "roundTitle": "Berts and Ernies",
    "roundType": "R3 Multiple Choice",
    "round_id": 11496,
    "user_id": 61,
    "virtual_round_eight": null
  },
  ...
]

question_answer

+----------------------+---------------+------+-----+---------+----------------+
| Field                | Type          | Null | Key | Default | Extra          |
+----------------------+---------------+------+-----+---------+----------------+
| question_answer_id   | int(11)       | NO   | PRI | NULL    | auto_increment |
| question             | varchar(1000) | YES  | MUL | NULL    |                |
| answer               | varchar(1000) | YES  | MUL | NULL    |                |
| pointValue           | int(11)       | YES  |     | NULL    |                |
| roundPosition        | int(11)       | YES  |     | NULL    |                |
| canBeBonus           | char(1)       | YES  |     | NULL    |                |
| factChecked          | char(1)       | YES  |     | NULL    |                |
| round_id             | int(11)       | NO   | MUL | NULL    |                |
| outOfDate            | char(1)       | YES  |     | NULL    |                |
| assoc_round_eight_id | int(11)       | YES  | MUL | NULL    |                |
+----------------------+---------------+------+-----+---------+----------------+

[
  {
    "answer": "Ring",
    "assoc_round_eight_id": null,
    "canBeBonus": "N",
    "factChecked": "N",
    "outOfDate": "N",
    "pointValue": null,
    "question": "A 2002 horror movie, a symptom of tinnitus, an item you collect in Sonic the Hedgehog games.",
    "question_answer_id": 66010,
    "roundPosition": 1,
    "round_id": 11628
  },
  {
    "answer": "Hung",
    "assoc_round_eight_id": null,
    "canBeBonus": "N",
    "factChecked": "N",
    "outOfDate": "N",
    "pointValue": null,
    "question": "A TV show about male prostitution, a Dean Koontz novel, a former American Idol contestant. ",
    "question_answer_id": 66011,
    "roundPosition": 2,
    "round_id": 11628
  },
  {
    "answer": "Tube",
    "assoc_round_eight_id": null,
    "canBeBonus": "N",
    "factChecked": "N",
    "outOfDate": "N",
    "pointValue": null,
    "question": "A component in older TVs, a type of container for Yoplait yogurt, the building block of the Internet according to the late Ted Stevens.",
    "question_answer_id": 66006,
    "roundPosition": 3,
    "round_id": 11628
  },
  ...
]

wookiehangover/gist:39818205cc5ca26a5006