Multiple-choice DB Schema

example.sql

CREATE TABLE users (
  phone_number TEXT
    UNIQUE
    NOT NULL,
  first_name TEXT,
  last_name TEXT,
  username TEXT
    NOT NULL,

  PRIMARY KEY (phone_number)
);

CREATE TABLE tests (
  name TEXT
    UNIQUE
    NOT NULL,

  PRIMARY KEY (name)
);

CREATE TABLE questions (
  test_name TEXT
    NOT NULL
    REFERENCES tests (name),
  index INTEGER
    NOT NULL,
  question TEXT NOT NULL,

  PRIMARY KEY (test_name, index)
);

CREATE TABLE answers (
  test_name TEXT
    NOT NULL
    REFERENCES questions (test_name),
  question_index INTEGER
    NOT NULL
    REFERENCES questions (index), 
  index INTEGER
    UNIQUE
    NOT NULL,
  response TEXT NOT NULL,
  correct BOOLEAN
    NOT NULL
    DEFAULT FALSE

  PRIMARY KEY (test_name, question_index, index)
);

CREATE TABLE responses (
  user_phone_number
    NOT NULL
    REFERENCES users (phone_number),
  test_name TEXT
    NOT NULL
    REFERENCES answers (test_name),
  question_index INTEGER
    NOT NULL
    REFERENCES answers (question_index),
  answer_index INTEGER
    NOT NULL
    REFERENCES answers (index)

  PRIMARY KEY (user_phone_number, test_name, question_index, answer_index)
);