TaskTerriersDB.sql

-- MySQL Script generated by MySQL Workbench
-- Tue Nov 21 03:43:37 2023
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

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='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema TaskTerriers
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema TaskTerriers
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `TaskTerriers` DEFAULT CHARACTER SET utf8 ;
USE `TaskTerriers` ;

-- -----------------------------------------------------
-- Table `TaskTerriers`.`Users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TaskTerriers`.`Users` (
  `userId` INT NOT NULL AUTO_INCREMENT,
  `userName` VARCHAR(45) NOT NULL,
  `description` VARCHAR(200) NULL,
  `email` VARCHAR(45) NOT NULL COMMENT 'bu email',
  `status` CHAR(1) NOT NULL DEFAULT 'A' COMMENT 'If user is active(account).\none character.\nif active: A\nif deleted: D\n',
  `createdAt` TIMESTAMP NOT NULL DEFAULT current_timestamp COMMENT 'when the user first signed up',
  `updatedAt` TIMESTAMP NULL,
  `profileURL` VARCHAR(2083) NULL COMMENT 'save the profile url that is saved in other storage(s3 bucket).',
  `major` VARCHAR(45) NULL,
  `minor` VARCHAR(45) NULL,
  `classesTaken` VARCHAR(100) NULL COMMENT 'up to 5 classes divided by spaces. \nin the form of CAS CSXXX.\nvarchar currently, may need to change type later(json).',
  PRIMARY KEY (`userId`),
  UNIQUE INDEX `userId_UNIQUE` (`userId` ASC) VISIBLE,
  UNIQUE INDEX `email_UNIQUE` (`email` ASC) VISIBLE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `TaskTerriers`.`Services`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TaskTerriers`.`Services` (
  `serviceId` INT NOT NULL AUTO_INCREMENT,
  `createdAt` TIMESTAMP NOT NULL DEFAULT current_timestamp,
  `updatedAt` TIMESTAMP NULL,
  `price` DECIMAL ZEROFILL NOT NULL COMMENT 'price per hour',
  `location` VARCHAR(100) NULL COMMENT 'where the service is provided.',
  `serviceType` VARCHAR(30) NULL,
  `review` DECIMAL NULL COMMENT 'only the review in decimals for now.',
  `deleted` TINYINT ZEROFILL NOT NULL COMMENT 'for soft delete.\n0 for default(not deleted).\n1 for deleted.',
  `serviceName` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`serviceId`),
  CONSTRAINT `userId`
    FOREIGN KEY ()
    REFERENCES `TaskTerriers`.`Users` ()
    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;