Schema - wtg/elections GitHub Wiki

The schema is v2.1. For older versions, view the history of this wiki page.

SQL Creation Script

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,ALLOW_INVALID_DATES';

CREATE SCHEMA IF NOT EXISTS `rpielections` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `rpielections`;

CREATE TABLE IF NOT EXISTS `rpielections`.`elections` (
`election_id` INT NOT NULL AUTO_INCREMENT,
`election_name` VARCHAR(45) NOT NULL,
`primary_date` DATE NOT NULL,
`final_date` DATE NOT NULL,
`runoff_date` DATE NOT NULL,
PRIMARY KEY (`election_id`) )
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `rpielections`.`offices` (
`office_id` INT NOT NULL AUTO_INCREMENT,
`election_id` INT NOT NULL,
`name` VARCHAR(255) NOT NULL,
`description` VARCHAR(1000) NOT NULL,
`openings` INT NOT NULL,
`nominations_required` INT NOT NULL,
`type` VARCHAR(45) NOT NULL,
`disabled` TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`office_id`) ,
INDEX `offices_fk1_idx` (`election_id` ASC) ,
CONSTRAINT `offices_fk1`
  FOREIGN KEY (`election_id`)
  REFERENCES `rpielections`.`elections` (`election_id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION);

CREATE TABLE IF NOT EXISTS `rpielections`.`parties` (
`party_id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(45) NOT NULL,
`platform` TEXT NOT NULL,
PRIMARY KEY (`party_id`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `rpielections`.`candidates` (
`rcs_id` VARCHAR(45) NOT NULL,
`office_id` INT NOT NULL,
`election_id` INT NOT NULL,
`party_id` INT NULL,
`nominations` INT NOT NULL DEFAULT 0,
PRIMARY KEY (`rcs_id`, `office_id`) ,
INDEX `candidates_fk2_idx` (`election_id` ASC) ,
CONSTRAINT `candidates_fk1`
  FOREIGN KEY (`office_id`)
  REFERENCES `rpielections`.`offices` (`office_id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
CONSTRAINT `candidates_fk2`
  FOREIGN KEY (`election_id`)
  REFERENCES `rpielections`.`elections` (`election_id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
CONSTRAINT `candidates_fk3`
  FOREIGN KEY (`party_id`)
  REFERENCES `rpielections`.`parties` (`party_id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `rpielections`.`candidate_data` (
`rcs_id` VARCHAR(45) NOT NULL,
`preferred_name` VARCHAR(45) NULL,
`first_name` VARCHAR(45) NULL,
`middle_name` VARCHAR(45) NULL,
`last_name` VARCHAR(45) NULL,
`greek_affiliated` TINYINT(1) NOT NULL DEFAULT 0,
`entry_date` VARCHAR(45) NOT NULL,
`class_by_credit` VARCHAR(45) NOT NULL,
`grad_date` VARCHAR(45) NOT NULL,
`rin` VARCHAR(9) NOT NULL,
`major` VARCHAR(45) NOT NULL,
`about` TEXT DEFAULT NULL,
`platform` TEXT DEFAULT NULL,
`video_url` VARCHAR(2000) DEFAULT NULL,
PRIMARY KEY (`rcs_id`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `rpielections`.`assistants` (
  `assistant_id` INT NOT NULL AUTO_INCREMENT,
  `rcs_id` VARCHAR(45) NOT NULL,
  `candidate_rcs_id` VARCHAR(45) NOT NULL,
  `first_name` VARCHAR(45) NOT NULL,
  `middle_name` VARCHAR(45) NOT NULL,
  `last_name` VARCHAR(45) NOT NULL,
  `preferred_name` VARCHAR(45) NULL,
  `rin` VARCHAR(9) NOT NULL,
  PRIMARY KEY (`assistant_id`),
  INDEX `assistant_fk1_idx` (`rcs_id` ASC),
  CONSTRAINT `assistant_fk1`
    FOREIGN KEY (`rcs_id`)
    REFERENCES `rpielections`.`candidates` (`rcs_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `rpielections`.`nominations` (
`nomination_id` INT NOT NULL AUTO_INCREMENT,
`election_id` INT NOT NULL,
`rcs_id` VARCHAR(45) NOT NULL,
`office_id` INT NOT NULL,
`nomination_rin` VARCHAR(9) NOT NULL,
`date` DATETIME NOT NULL,
INDEX `nominations_fk2_idx` (`office_id` ASC) ,
PRIMARY KEY (`nomination_id`) ,
INDEX `nominations_fk3_idx` (`election_id` ASC) ,
CONSTRAINT `nominations_fk1`
  FOREIGN KEY (`rcs_id` , `office_id`)
  REFERENCES `rpielections`.`candidates` (`rcs_id` , `office_id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
CONSTRAINT `nominations_fk2`
  FOREIGN KEY (`office_id`)
  REFERENCES `rpielections`.`offices` (`office_id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
CONSTRAINT `nominations_fk3`
  FOREIGN KEY (`election_id`)
  REFERENCES `rpielections`.`elections` (`election_id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `rpielections`.`party_officers` (
`party_officer_id` INT NOT NULL AUTO_INCREMENT,
`party_id` INT NOT NULL,
`rcs_id` VARCHAR(45) NOT NULL,
`position` VARCHAR(45) NOT NULL,
`is_highest` INT NOT NULL DEFAULT 0,
PRIMARY KEY (`party_officer_id`),
INDEX `officer_fk1_idx` (`party_id` ASC),
CONSTRAINT `officer_fk1`
  FOREIGN KEY (`party_id`)
  REFERENCES `rpielections`.`parties` (`party_id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `rpielections`.`log` (
`log_id` INT NOT NULL AUTO_INCREMENT,
`rcs_id` VARCHAR(45) NOT NULL,
`type` VARCHAR(45) NOT NULL,
`description` VARCHAR(255) NOT NULL,
`time` DATETIME NOT NULL,
PRIMARY KEY (`log_id`) )
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `rpielections`.`administrators` (
`rcs_id` VARCHAR(45) NOT NULL,
`admin_type` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0 = no admin\n1 = RnE\n2 = WTG\n3 = Full Admin',
PRIMARY KEY (`rcs_id`) )
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `rpielections`.`pages` (
`page_id` INT NOT NULL AUTO_INCREMENT,
`title` VARCHAR(45) NOT NULL,
`slug` VARCHAR(45) NOT NULL,
`content` TEXT NOT NULL,
`created_by` VARCHAR(45) NOT NULL,
`creation_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`last_updated_by` VARCHAR(45) NOT NULL,
`last_updated_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`hidden` TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`page_id`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `rpielections`.`configurations` (
`configuration_id` INT NOT NULL AUTO_INCREMENT,
`key` varchar(45) NOT NULL,
`value` varchar(200) NOT NULL,
PRIMARY KEY (`configuration_id`)
) ENGINE=InnoDB;

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

Diagram