Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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;
@kevincmann

This comment has been minimized.

Copy link

@kevincmann kevincmann commented May 9, 2015

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

@cattoo

This comment has been minimized.

Copy link

@cattoo cattoo commented Mar 8, 2016

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)?

@dgleba

This comment has been minimized.

Copy link

@dgleba dgleba commented Apr 18, 2016

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?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment