· 7 years ago · Oct 10, 2018, 03:36 PM
1INSERT INTO `mydb`.`clients` (`ClientID`, `ClientName`, `PrimaryAddress`, `PrimaryPhone`, `PrimaryEmail`) VALUES ('1', 'Blooms', '43 Indigo Road', '0793469543', 'admin@blooms.com');
2INSERT INTO `mydb`.`clients` (`ClientID`, `ClientName`, `PrimaryAddress`, `PrimaryPhone`, `PrimaryEmail`) VALUES ('2', 'Bloggs', '32 Long Street', '0799432213', 'faq@bloggs.com');
3
4-- MySQL Workbench Forward Engineering
5
6SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8SET @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';
9
10-- -----------------------------------------------------
11-- Schema mydb
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema mydb
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
18USE `mydb` ;
19
20-- -----------------------------------------------------
21-- Table `mydb`.`Clients`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `mydb`.`Clients` (
24 `ClientID` INT NOT NULL AUTO_INCREMENT,
25 `ClientName` VARCHAR(45) NOT NULL,
26 `PrimaryAddress` VARCHAR(45) NOT NULL,
27 `PrimaryPhone` INT NOT NULL,
28 `PrimaryEmail` VARCHAR(45) NOT NULL,
29 PRIMARY KEY (`ClientID`))
30ENGINE = InnoDB;
31
32CREATE UNIQUE INDEX `ClientID_UNIQUE` ON `mydb`.`Clients` (`ClientID` ASC) VISIBLE;
33
34
35-- -----------------------------------------------------
36-- Table `mydb`.`Employees`
37-- -----------------------------------------------------
38CREATE TABLE IF NOT EXISTS `mydb`.`Employees` (
39 `EmployeeID` INT NOT NULL AUTO_INCREMENT,
40 `EmployeeName` VARCHAR(45) NOT NULL,
41 `Position` VARCHAR(45) NOT NULL,
42 `PrimaryPhone` VARCHAR(45) NOT NULL,
43 `PrimaryEmail` VARCHAR(45) NOT NULL,
44 `Salary` DECIMAL NOT NULL,
45 `HolidayAllowance` DECIMAL NOT NULL,
46 PRIMARY KEY (`EmployeeID`))
47ENGINE = InnoDB;
48
49CREATE UNIQUE INDEX `EmployeeID_UNIQUE` ON `mydb`.`Employees` (`EmployeeID` ASC) VISIBLE;
50
51
52-- -----------------------------------------------------
53-- Table `mydb`.`Fees`
54-- -----------------------------------------------------
55CREATE TABLE IF NOT EXISTS `mydb`.`Fees` (
56 `FeeID` INT NOT NULL AUTO_INCREMENT,
57 `FeeName` VARCHAR(45) NOT NULL,
58 `FeeCost` DECIMAL NOT NULL,
59 PRIMARY KEY (`FeeID`))
60ENGINE = InnoDB;
61
62CREATE UNIQUE INDEX `FeeID_UNIQUE` ON `mydb`.`Fees` (`FeeID` ASC) VISIBLE;
63
64
65-- -----------------------------------------------------
66-- Table `mydb`.`Jobs`
67-- -----------------------------------------------------
68CREATE TABLE IF NOT EXISTS `mydb`.`Jobs` (
69 `JobID` INT NOT NULL AUTO_INCREMENT,
70 `JobName` VARCHAR(45) NOT NULL,
71 `ClientID` INT NOT NULL,
72 `ManagerID` INT NULL,
73 `StartTime` DATE NOT NULL,
74 `EndTime` DATE NULL,
75 `FeeID` INT NULL,
76 `Consultants` VARCHAR(45) NULL,
77 PRIMARY KEY (`JobID`),
78 CONSTRAINT `ClientID`
79 FOREIGN KEY (`ClientID`)
80 REFERENCES `mydb`.`Clients` (`ClientID`)
81 ON DELETE NO ACTION
82 ON UPDATE NO ACTION,
83 CONSTRAINT `ManagerID`
84 FOREIGN KEY (`ManagerID`)
85 REFERENCES `mydb`.`Employees` (`EmployeeID`)
86 ON DELETE NO ACTION
87 ON UPDATE NO ACTION,
88 CONSTRAINT `FeeID`
89 FOREIGN KEY (`FeeID`)
90 REFERENCES `mydb`.`Fees` (`FeeID`)
91 ON DELETE NO ACTION
92 ON UPDATE NO ACTION)
93ENGINE = InnoDB;
94
95CREATE UNIQUE INDEX `ClientID_UNIQUE` ON `mydb`.`Jobs` (`ClientID` ASC) VISIBLE;
96
97CREATE UNIQUE INDEX `FeeID_UNIQUE` ON `mydb`.`Jobs` (`FeeID` ASC) VISIBLE;
98
99CREATE INDEX `ManagerID_idx` ON `mydb`.`Jobs` (`ManagerID` ASC) VISIBLE;
100
101
102-- -----------------------------------------------------
103-- Table `mydb`.`Timesheets`
104-- -----------------------------------------------------
105CREATE TABLE IF NOT EXISTS `mydb`.`Timesheets` (
106 `EntryID` INT NOT NULL AUTO_INCREMENT,
107 `JobID` INT NOT NULL,
108 `WorkDesc` MEDIUMTEXT NOT NULL,
109 `EmployeeID` INT NOT NULL,
110 `Date` DATE NOT NULL,
111 `ClockedHours` DECIMAL NOT NULL,
112 `ClockType` TINYINT NOT NULL,
113 PRIMARY KEY (`EntryID`),
114 CONSTRAINT `JobID`
115 FOREIGN KEY (`JobID`)
116 REFERENCES `mydb`.`Jobs` (`JobID`)
117 ON DELETE NO ACTION
118 ON UPDATE NO ACTION,
119 CONSTRAINT `EmployeeID`
120 FOREIGN KEY (`EmployeeID`)
121 REFERENCES `mydb`.`Employees` (`EmployeeID`)
122 ON DELETE NO ACTION
123 ON UPDATE NO ACTION)
124ENGINE = InnoDB;
125
126CREATE UNIQUE INDEX `EntryID_UNIQUE` ON `mydb`.`Timesheets` (`EntryID` ASC) VISIBLE;
127
128CREATE INDEX `JobID_idx` ON `mydb`.`Timesheets` (`JobID` ASC) VISIBLE;
129
130CREATE INDEX `EmployeeID_idx` ON `mydb`.`Timesheets` (`EmployeeID` ASC) VISIBLE;
131
132
133-- -----------------------------------------------------
134-- Table `mydb`.`Expenses`
135-- -----------------------------------------------------
136CREATE TABLE IF NOT EXISTS `mydb`.`Expenses` (
137 `ExpenseID` INT NOT NULL AUTO_INCREMENT,
138 `ExpenseName` VARCHAR(45) NOT NULL,
139 `JobID` INT NOT NULL,
140 `EmployeeID` INT NOT NULL,
141 PRIMARY KEY (`ExpenseID`),
142 CONSTRAINT `JobID`
143 FOREIGN KEY (`JobID`)
144 REFERENCES `mydb`.`Jobs` (`JobID`)
145 ON DELETE NO ACTION
146 ON UPDATE NO ACTION,
147 CONSTRAINT `EmployeeID`
148 FOREIGN KEY (`EmployeeID`)
149 REFERENCES `mydb`.`Employees` (`EmployeeID`)
150 ON DELETE NO ACTION
151 ON UPDATE NO ACTION)
152ENGINE = InnoDB;
153
154CREATE UNIQUE INDEX `ExpenseID_UNIQUE` ON `mydb`.`Expenses` (`ExpenseID` ASC) VISIBLE;
155
156CREATE UNIQUE INDEX `JobID_UNIQUE` ON `mydb`.`Expenses` (`JobID` ASC) VISIBLE;
157
158CREATE UNIQUE INDEX `EmployeeID_UNIQUE` ON `mydb`.`Expenses` (`EmployeeID` ASC) VISIBLE;
159
160
161SET SQL_MODE=@OLD_SQL_MODE;
162SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
163SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;