Page 1 of 1

Computer Repair tracking system DB design critique please!

Posted: Sat Nov 12, 2011 6:12 pm
by mikeashfield

Code: Select all

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 `computer` DEFAULT CHARACTER SET latin1 ;
USE `computer` ;

-- -----------------------------------------------------
-- Table `computer`.`repair_actions`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `computer`.`repair_actions` (
  `idrepair_actions` INT NOT NULL AUTO_INCREMENT ,
  `repair_id` INT(5) NOT NULL ,
  `actioned_by` VARCHAR(20) NOT NULL ,
  `repair_actions` TEXT(200) NOT NULL ,
  `completes_repair` CHAR(1) NOT NULL COMMENT 'Will store a Y or N for completes repair?' ,
  PRIMARY KEY (`idrepair_actions`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `computer`.`repairs`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `computer`.`repairs` (
  `computer_id` INT(5) NULL DEFAULT NULL ,
  `repair_id` INT(5) NOT NULL ,
  `client_id` INT(5) NULL DEFAULT NULL ,
  `date_processed` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  `issue_cat_id` INT(2) NULL DEFAULT NULL ,
  `issue_desc` TEXT NULL DEFAULT NULL ,
  `notes` VARCHAR(300) NULL DEFAULT NULL ,
  `date_completed` DATETIME NULL DEFAULT NULL ,
  `repair_actions_idrepair_actions` INT NOT NULL ,
  PRIMARY KEY (`repair_id`) ,
  INDEX `computer_id` (`computer_id` ASC) ,
  INDEX `fk_repairs_repair_actions1` (`repair_actions_idrepair_actions` ASC) ,
  CONSTRAINT `fk_repairs_repair_actions1`
    FOREIGN KEY (`repair_actions_idrepair_actions` )
    REFERENCES `computer`.`repair_actions` (`idrepair_actions` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `computer`.`computers`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `computer`.`computers` (
  `machine_serial` VARCHAR(30) NULL DEFAULT NULL ,
  `computer_id` INT(5) NOT NULL ,
  `machine_type` ENUM('L','D','O') NOT NULL ,
  `machine_OS` VARCHAR(50) NULL DEFAULT NULL ,
  `repairs_repair_id` INT(5) NOT NULL ,
  PRIMARY KEY (`computer_id`) ,
  UNIQUE INDEX `machine_serial` (`machine_serial` ASC) ,
  INDEX `fk_computers_repairs` (`repairs_repair_id` ASC) ,
  CONSTRAINT `fk_computers_repairs`
    FOREIGN KEY (`repairs_repair_id` )
    REFERENCES `computer`.`repairs` (`repair_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `computer`.`clients`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `computer`.`clients` (
  `client_id` INT(5) NOT NULL AUTO_INCREMENT ,
  `fname` VARCHAR(30) NOT NULL ,
  `sname` VARCHAR(30) NOT NULL ,
  `tel1` INT(15) NULL ,
  `e-mail` VARCHAR(60) NULL ,
  `street1` TEXT(50) NOT NULL ,
  `postcode` VARCHAR(10) NOT NULL ,
  `repairs_repair_id` INT(5) NOT NULL ,
  PRIMARY KEY (`client_id`) ,
  INDEX `fk_clients_repairs1` (`repairs_repair_id` ASC) ,
  CONSTRAINT `fk_clients_repairs1`
    FOREIGN KEY (`repairs_repair_id` )
    REFERENCES `computer`.`repairs` (`repair_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `computer`.`issue_categories`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `computer`.`issue_categories` (
  `issue_cat_id` INT(2) NOT NULL AUTO_INCREMENT ,
  `cat_short_title` VARCHAR(20) NULL ,
  `repairs_repair_id` INT(5) NOT NULL ,
  PRIMARY KEY (`issue_cat_id`) ,
  INDEX `fk_issue_categories_repairs1` (`repairs_repair_id` ASC) ,
  CONSTRAINT `fk_issue_categories_repairs1`
    FOREIGN KEY (`repairs_repair_id` )
    REFERENCES `computer`.`repairs` (`repair_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;