Skip to content

Instantly share code, notes, and snippets.

@durrantm

durrantm/create.sql

Created May 5, 2015 11:13
Show Gist options
  • Save durrantm/1e618164fd4acf91e372 to your computer and use it in GitHub Desktop.
Save durrantm/1e618164fd4acf91e372 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. durrantm created this gist May 5, 2015.
    284 changes: 284 additions & 0 deletions create.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,284 @@
    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;