fancy_survey.sql

-- -----------------------------------------------------
-- FancySurvey Schema
-- 02/26/15 14:53:47
-- Author: @c1v0
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Table `users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `surveys`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `surveys` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_id` INT(11) UNSIGNED NULL DEFAULT NULL,
  `user_id` INT(11) UNSIGNED NOT NULL COMMENT 'Might want to alias as owner_id.',
  `name` VARCHAR(255) NOT NULL COMMENT 'The name or title of the survey.',
  `created` DATETIME NULL DEFAULT NULL COMMENT 'Survey creation timestamp.',
  `modified` DATETIME NULL DEFAULT NULL COMMENT 'Last modified timestamp.',
  PRIMARY KEY (`id`),
  INDEX `fk_surveys_user_idx` (`user_id` ASC),
  CONSTRAINT `fk_surveys_user`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `submissions`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `submissions` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) UNSIGNED NOT NULL,
  `survey_id` INT(11) UNSIGNED NOT NULL,
  `created` DATETIME NULL DEFAULT NULL COMMENT 'Timestamp of the survey.',
  PRIMARY KEY (`id`),
  INDEX `survey_id_idx` (`survey_id` ASC),
  INDEX `fk_submissions_user_idx` (`user_id` ASC),
  CONSTRAINT `fk_submissions_survey`
    FOREIGN KEY (`survey_id`)
    REFERENCES `surveys` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_submissions_user`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nodes`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nodes` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `key` VARCHAR(255) NOT NULL COMMENT 'Question or prompt, you might wish to alias this to `name`.',
  `options` TEXT NULL COMMENT 'Optional multiple choice answers, store as serialized data or refactor this.',
  `created` DATETIME NULL COMMENT 'Creation date of the node.',
  `modified` DATETIME NULL COMMENT 'Date the node was last modified.',
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nodes_surveys`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nodes_surveys` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `node_id` INT(11) UNSIGNED NOT NULL,
  `survey_id` INT(11) UNSIGNED NOT NULL,
  `weight` INT(3) NULL DEFAULT NULL COMMENT 'Weight of the node within a survey.',
  `created` DATETIME NULL DEFAULT NULL COMMENT 'Timestamp of when node was associated with survey.',
  `modified` DATETIME NULL DEFAULT NULL COMMENT 'Time the node-survey relationship was last modified.',
  PRIMARY KEY (`node_id`, `survey_id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  INDEX `fk_nodes_surveys_survey_idx` (`survey_id` ASC),
  CONSTRAINT `fk_nodes_surveys_node`
    FOREIGN KEY (`node_id`)
    REFERENCES `nodes` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_nodes_surveys_survey`
    FOREIGN KEY (`survey_id`)
    REFERENCES `surveys` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `metrics`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `metrics` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(150) NOT NULL,
  `uid` CHAR(36) NULL DEFAULT NULL COMMENT 'Associates the metric with that in another system. ',
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `results`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `results` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `metric_id` INT(11) UNSIGNED NOT NULL,
  `submission_id` INT(11) UNSIGNED NOT NULL,
  `value` FLOAT NOT NULL COMMENT 'You will probably want to customize the data type here.',
  PRIMARY KEY (`id`),
  INDEX `submission_id_idx` (`submission_id` ASC),
  INDEX `metric_id_idx` (`metric_id` ASC),
  CONSTRAINT `fk_results_submission`
    FOREIGN KEY (`submission_id`)
    REFERENCES `submissions` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_results_result`
    FOREIGN KEY (`metric_id`)
    REFERENCES `metrics` (`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;

-- -----------------------------------------------------
-- Data for table `users`
-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO `users` (`id`, `name`) VALUES (1, 'Farah Fawcett');

COMMIT;


-- -----------------------------------------------------
-- Data for table `surveys`
-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO `surveys` (`id`, `category_id`, `user_id`, `name`, `created`, `modified`) VALUES (1, 1, 1, 'American Idol', '2015-02-25 23:59:59', '2015-02-25 23:59:59');

COMMIT;


-- -----------------------------------------------------
-- Data for table `submissions`
-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO `submissions` (`id`, `user_id`, `survey_id`, `created`) VALUES (1, 1, 1, '2015-02-25 23:59:59');

COMMIT;


-- -----------------------------------------------------
-- Data for table `nodes`
-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO `nodes` (`id`, `key`, `options`, `created`, `modified`) VALUES (1, 'Best Singer', NULL, '2015-02-25 23:59:59', '2015-02-25 23:59:59');
INSERT INTO `nodes` (`id`, `key`, `options`, `created`, `modified`) VALUES (2, 'Hottest Singer', NULL, '2015-02-25 23:59:59', '2015-02-25 23:59:59');
INSERT INTO `nodes` (`id`, `key`, `options`, `created`, `modified`) VALUES (3, 'Do you like Ryan Seacrest?', '{\"yes\", \"no\"}', '2015-02-25 23:59:59', '2015-02-25 23:59:59');

COMMIT;


-- -----------------------------------------------------
-- Data for table `nodes_surveys`
-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO `nodes_surveys` (`id`, `node_id`, `survey_id`, `weight`, `created`, `modified`) VALUES (1, 1, 1, 2, '2015-02-25 23:59:59', '2015-02-25 23:59:59');
INSERT INTO `nodes_surveys` (`id`, `node_id`, `survey_id`, `weight`, `created`, `modified`) VALUES (2, 2, 1, 1, '2015-02-25 23:59:59', '2015-02-25 23:59:59');
INSERT INTO `nodes_surveys` (`id`, `node_id`, `survey_id`, `weight`, `created`, `modified`) VALUES (3, 3, 1, 3, '2015-02-25 23:59:59', '2015-02-25 23:59:59');

COMMIT;


-- -----------------------------------------------------
-- Data for table `metrics`
-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO `metrics` (`id`, `name`, `uid`) VALUES (1, 'Survey Duration in Seconds', '64fdbbe6-bdfc-11e4-8dfc-aa07a5b093db');
INSERT INTO `metrics` (`id`, `name`, `uid`) VALUES (2, 'Calculated Engagement Score', '64fdbbe6-bdfc-11e4-8dfc-aa07a5b093db');

COMMIT;


-- -----------------------------------------------------
-- Data for table `results`
-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO `results` (`id`, `metric_id`, `submission_id`, `value`) VALUES (1, 1, 1, 504);
INSERT INTO `results` (`id`, `metric_id`, `submission_id`, `value`) VALUES (2, 2, 1, 97.02);

COMMIT;