create.sql

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

CREATE SCHEMA IF NOT EXISTS `survey_001_models_from_tables` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `survey_001_models_from_tables` ;

-- -----------------------------------------------------
-- Table `survey_001_models_from_tables`.`organizations`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `survey_001_models_from_tables`.`organizations` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `organization_name` VARCHAR(80) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `survey_name_UNIQUE` (`organization_name` ASC) )
ENGINE = InnoDB
COMMENT = 'This is our survey tool.';


-- -----------------------------------------------------
-- Table `survey_001_models_from_tables`.`survey_headers`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `survey_001_models_from_tables`.`survey_headers` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `organization_id` INT(11) NOT NULL ,
  `survey_name` VARCHAR(80) NULL ,
  `instructions` VARCHAR(4096) NULL ,
  `other_header_info` VARCHAR(255) NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `survey_name_UNIQUE` (`survey_name` ASC) ,
  INDEX `fk_surveys_organizations1` (`organization_id` ASC) ,
  CONSTRAINT `fk_surveys_organizations1`
    FOREIGN KEY (`organization_id` )
    REFERENCES `survey_001_models_from_tables`.`organizations` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'This is our survey tool.';


-- -----------------------------------------------------
-- Table `survey_001_models_from_tables`.`input_types`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `survey_001_models_from_tables`.`input_types` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `input_type_name` VARCHAR(80) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `survey_name_UNIQUE` (`input_type_name` ASC) )
ENGINE = InnoDB
COMMENT = 'This is our survey tool.';


-- -----------------------------------------------------
-- Table `survey_001_models_from_tables`.`survey_sections`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `survey_001_models_from_tables`.`survey_sections` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `survey_header_id` INT NULL ,
  `section_name` VARCHAR(80) NULL ,
  `section_title` VARCHAR(45) NULL ,
  `section_subheading` VARCHAR(45) NULL ,
  `section_required_yn` TINYINT(1) NOT NULL DEFAULT 1 ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `survey_name_UNIQUE` (`section_name` ASC) ,
  INDEX `fk_survey_sections_surveys1` (`survey_header_id` ASC) ,
  CONSTRAINT `fk_survey_sections_surveys1`
    FOREIGN KEY (`survey_header_id` )
    REFERENCES `survey_001_models_from_tables`.`survey_headers` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'This is our survey tool.';


-- -----------------------------------------------------
-- Table `survey_001_models_from_tables`.`option_groups`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `survey_001_models_from_tables`.`option_groups` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `option_group_name` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB
COMMENT = 'This is our survey tool.';


-- -----------------------------------------------------
-- Table `survey_001_models_from_tables`.`questions`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `survey_001_models_from_tables`.`questions` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `parent_id` INT NULL ,
  `survey_section_id` INT NOT NULL ,
  `input_type_id` INT NOT NULL ,
  `question_name` VARCHAR(255) NOT NULL ,
  `question_subtext` VARCHAR(500) NULL ,
  `question_required_yn` TINYINT(1) NULL ,
  `answer_required_yn` TINYINT(1) NULL DEFAULT 1 ,
  `option_group_id` INT NULL ,
  `allow_mutiple_option_answers_yn` TINYINT(1) NULL DEFAULT 0 ,
  `dependent_question_id` INT NULL ,
  `dependent_question_option_id` INT NULL ,
  `dependent_answer_id` INT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_questions_question_types1` (`input_type_id` ASC) ,
  INDEX `fk_questions_survey_sections1` (`survey_section_id` ASC) ,
  INDEX `fk_questions_option_type_group1` (`option_group_id` ASC) ,
  UNIQUE INDEX `allow_mutiple_option_answers_yn_UNIQUE` (`allow_mutiple_option_answers_yn` ASC) ,
  CONSTRAINT `fk_questions_question_types1`
    FOREIGN KEY (`input_type_id` )
    REFERENCES `survey_001_models_from_tables`.`input_types` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_questions_survey_sections1`
    FOREIGN KEY (`survey_section_id` )
    REFERENCES `survey_001_models_from_tables`.`survey_sections` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_questions_option_type_group1`
    FOREIGN KEY (`option_group_id` )
    REFERENCES `survey_001_models_from_tables`.`option_groups` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'This is are our questions';


-- -----------------------------------------------------
-- Table `survey_001_models_from_tables`.`users`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `survey_001_models_from_tables`.`users` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `username` VARCHAR(80) NOT NULL ,
  `password_hashed` VARCHAR(255) NULL ,
  `email` VARCHAR(45) NOT NULL ,
  `admin` TINYINT(1) NULL ,
  `invite_dt` DATETIME NULL ,
  `last_login_dt` DATETIME NULL ,
  `inviter_user_id` INT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `survey_name_UNIQUE` (`username` ASC) ,
  UNIQUE INDEX `email_UNIQUE` (`email` ASC) )
ENGINE = InnoDB
COMMENT = 'This is our survey tool.';


-- -----------------------------------------------------
-- Table `survey_001_models_from_tables`.`unit_of_measures`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `survey_001_models_from_tables`.`unit_of_measures` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `unit_of_measures_name` VARCHAR(80) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `survey_name_UNIQUE` (`unit_of_measures_name` ASC) )
ENGINE = InnoDB
COMMENT = 'This is our survey tool.';


-- -----------------------------------------------------
-- Table `survey_001_models_from_tables`.`option_choices`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `survey_001_models_from_tables`.`option_choices` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `option_group_id` INT NOT NULL ,
  `option_choice_name` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_option_type_choices_option_type_group1` (`option_group_id` ASC) ,
  CONSTRAINT `fk_option_type_choices_option_type_group1`
    FOREIGN KEY (`option_group_id` )
    REFERENCES `survey_001_models_from_tables`.`option_groups` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'This is our survey tool.';


-- -----------------------------------------------------
-- Table `survey_001_models_from_tables`.`question_options`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `survey_001_models_from_tables`.`question_options` (
  `id` INT NOT NULL ,
  `question_id` INT NOT NULL ,
  `option_choice_id` INT NOT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_question_options_questions1` (`question_id` ASC) ,
  INDEX `fk_question_options_option_choices1` (`option_choice_id` ASC) ,
  CONSTRAINT `fk_question_options_questions1`
    FOREIGN KEY (`question_id` )
    REFERENCES `survey_001_models_from_tables`.`questions` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_question_options_option_choices1`
    FOREIGN KEY (`option_choice_id` )
    REFERENCES `survey_001_models_from_tables`.`option_choices` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `survey_001_models_from_tables`.`answers`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `survey_001_models_from_tables`.`answers` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `user_id` INT NOT NULL ,
  `question_option_id` INT NOT NULL ,
  `answer_numeric` INT NULL ,
  `answer_text` VARCHAR(255) NULL ,
  `answer_yn` TINYINT(1) NULL ,
  `unit_of_measure_id` INT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_answers_surveyees1` (`user_id` ASC) ,
  INDEX `fk_answers_unit_of_measure1` (`unit_of_measure_id` ASC) ,
  INDEX `fk_answers_question_options1` (`question_option_id` ASC) ,
  CONSTRAINT `fk_answers_surveyees1`
    FOREIGN KEY (`user_id` )
    REFERENCES `survey_001_models_from_tables`.`users` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_answers_unit_of_measure1`
    FOREIGN KEY (`unit_of_measure_id` )
    REFERENCES `survey_001_models_from_tables`.`unit_of_measures` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_answers_question_options1`
    FOREIGN KEY (`question_option_id` )
    REFERENCES `survey_001_models_from_tables`.`question_options` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'This is are our answers';


-- -----------------------------------------------------
-- Table `survey_001_models_from_tables`.`survey_comments`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `survey_001_models_from_tables`.`survey_comments` (
  `id` INT NOT NULL ,
  `survey_header_id` INT NOT NULL ,
  `user_id` INT NOT NULL ,
  `comments` VARCHAR(4096) NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_survey_comments_users1` (`user_id` ASC) ,
  INDEX `fk_survey_comments_surveys1` (`survey_header_id` ASC) ,
  CONSTRAINT `fk_survey_comments_users1`
    FOREIGN KEY (`user_id` )
    REFERENCES `survey_001_models_from_tables`.`users` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_survey_comments_surveys1`
    FOREIGN KEY (`survey_header_id` )
    REFERENCES `survey_001_models_from_tables`.`survey_headers` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `survey_001_models_from_tables`.`user_survey_sections`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `survey_001_models_from_tables`.`user_survey_sections` (
  `id` INT NOT NULL ,
  `user_id` INT NOT NULL ,
  `survey_section_id` INT NOT NULL ,
  `completed_on` DATETIME NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_user_survey_sections_survey_sections1` (`survey_section_id` ASC) ,
  INDEX `fk_user_survey_sections_users1` (`user_id` ASC) ,
  CONSTRAINT `fk_user_survey_sections_survey_sections1`
    FOREIGN KEY (`survey_section_id` )
    REFERENCES `survey_001_models_from_tables`.`survey_sections` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_user_survey_sections_users1`
    FOREIGN KEY (`user_id` )
    REFERENCES `survey_001_models_from_tables`.`users` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;



SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

It seems as if line 15 has the wrong index name.

Hi Michael, how would you recommend the format for multiple response questions to be saved in SQL database? Dichotomos, e.g. option selected or not (0 or 1 in the data) for all options or the actual responses say A, B, C for that question (1,2,3 in the data)?

When I tried to add a second question_option, I notice that the primary key of that table is not AUTO_INCREMENT. Should it be?