· 8 years ago · Mar 04, 2018, 01:58 PM
1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
6
7-- -----------------------------------------------------
8-- Schema mydb
9-- -----------------------------------------------------
10-- -----------------------------------------------------
11-- Schema sys
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema sys
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `sys` DEFAULT CHARACTER SET utf8 ;
18-- -----------------------------------------------------
19-- Schema new_schema
20-- -----------------------------------------------------
21
22-- -----------------------------------------------------
23-- Schema new_schema
24-- -----------------------------------------------------
25CREATE SCHEMA IF NOT EXISTS `new_schema` DEFAULT CHARACTER SET utf8 ;
26USE `sys` ;
27
28-- -----------------------------------------------------
29-- Table `sys`.`band`
30-- -----------------------------------------------------
31CREATE TABLE IF NOT EXISTS `sys`.`band` (
32 `BandID` INT(10) NOT NULL AUTO_INCREMENT,
33 `lowerBound` DOUBLE NOT NULL,
34 `upperBound` DOUBLE NOT NULL,
35 `discount` DOUBLE NOT NULL,
36 PRIMARY KEY (`BandID`))
37ENGINE = InnoDB
38DEFAULT CHARACTER SET = utf8;
39
40
41-- -----------------------------------------------------
42-- Table `sys`.`basetask`
43-- -----------------------------------------------------
44CREATE TABLE IF NOT EXISTS `sys`.`basetask` (
45 `taskName` VARCHAR(255) NOT NULL,
46 `department` VARCHAR(255) NOT NULL,
47 `description` VARCHAR(255) NOT NULL,
48 `price` DOUBLE NOT NULL,
49 `baseTaskID` INT(10) NOT NULL AUTO_INCREMENT,
50 PRIMARY KEY (`baseTaskID`))
51ENGINE = InnoDB
52AUTO_INCREMENT = 9
53DEFAULT CHARACTER SET = utf8;
54
55
56-- -----------------------------------------------------
57-- Table `sys`.`card_information`
58-- -----------------------------------------------------
59CREATE TABLE IF NOT EXISTS `sys`.`card_information` (
60 `PaymentpaymentId` INT(10) NOT NULL,
61 `cardNumber` INT(16) NOT NULL,
62 `cardType` VARCHAR(255) NOT NULL,
63 `expiryDate` DATE NOT NULL)
64ENGINE = InnoDB
65DEFAULT CHARACTER SET = utf8;
66
67
68-- -----------------------------------------------------
69-- Table `sys`.`customer`
70-- -----------------------------------------------------
71CREATE TABLE IF NOT EXISTS `sys`.`customer` (
72 `customerID` INT(10) NOT NULL AUTO_INCREMENT,
73 `name` VARCHAR(255) NOT NULL,
74 `surname` VARCHAR(255) NOT NULL,
75 `phoneNo` VARCHAR(255) NOT NULL,
76 `email` VARCHAR(255) NOT NULL,
77 `address` VARCHAR(255) NOT NULL,
78 `postcode` VARCHAR(255) NOT NULL,
79 PRIMARY KEY (`customerID`))
80ENGINE = InnoDB
81AUTO_INCREMENT = 9
82DEFAULT CHARACTER SET = utf8;
83
84
85-- -----------------------------------------------------
86-- Table `sys`.`job`
87-- -----------------------------------------------------
88CREATE TABLE IF NOT EXISTS `sys`.`job` (
89 `JobID` INT(10) NOT NULL AUTO_INCREMENT,
90 `CustomercustomerID` INT(10) NOT NULL,
91 `orderDate` DATE NOT NULL,
92 `collectionDate` DATE NULL DEFAULT NULL,
93 `specInstructions` VARCHAR(255) NOT NULL,
94 `deadline` DATE NOT NULL,
95 `surcharge` DOUBLE NOT NULL,
96 `value` DOUBLE NOT NULL,
97 PRIMARY KEY (`JobID`),
98 INDEX `FKJob102033` (`CustomercustomerID` ASC),
99 CONSTRAINT `FKJob102033`
100 FOREIGN KEY (`CustomercustomerID`)
101 REFERENCES `sys`.`customer` (`customerID`))
102ENGINE = InnoDB
103AUTO_INCREMENT = 12
104DEFAULT CHARACTER SET = utf8;
105
106
107-- -----------------------------------------------------
108-- Table `sys`.`payment`
109-- -----------------------------------------------------
110CREATE TABLE IF NOT EXISTS `sys`.`payment` (
111 `JobJobID` INT(10) NOT NULL,
112 `paymentDate` INT(10) NOT NULL,
113 PRIMARY KEY (`JobJobID`),
114 INDEX `FKPayment170102` (`JobJobID` ASC),
115 CONSTRAINT `FKPayment170102`
116 FOREIGN KEY (`JobJobID`)
117 REFERENCES `sys`.`job` (`JobID`))
118ENGINE = InnoDB
119DEFAULT CHARACTER SET = utf8;
120
121
122-- -----------------------------------------------------
123-- Table `sys`.`cardpayment`
124-- -----------------------------------------------------
125CREATE TABLE IF NOT EXISTS `sys`.`cardpayment` (
126 `PaymentJobJobID` INT(10) NOT NULL,
127 `cardNo` INT(10) NOT NULL,
128 `expDate` DATE NOT NULL,
129 `type` INT(10) NOT NULL,
130 PRIMARY KEY (`PaymentJobJobID`),
131 INDEX `FKcardPaymen338903` (`PaymentJobJobID` ASC),
132 CONSTRAINT `FKcardPaymen338903`
133 FOREIGN KEY (`PaymentJobJobID`)
134 REFERENCES `sys`.`payment` (`JobJobID`))
135ENGINE = InnoDB
136DEFAULT CHARACTER SET = utf8;
137
138
139-- -----------------------------------------------------
140-- Table `sys`.`discount`
141-- -----------------------------------------------------
142CREATE TABLE IF NOT EXISTS `sys`.`discount` (
143 `discountID` INT(10) NOT NULL AUTO_INCREMENT,
144 `discountType` VARCHAR(255) NOT NULL,
145 PRIMARY KEY (`discountID`))
146ENGINE = InnoDB
147AUTO_INCREMENT = 12
148DEFAULT CHARACTER SET = utf8;
149
150
151-- -----------------------------------------------------
152-- Table `sys`.`valuedcustomer`
153-- -----------------------------------------------------
154CREATE TABLE IF NOT EXISTS `sys`.`valuedcustomer` (
155 `CustomercustomerID` INT(10) NOT NULL,
156 `DiscountdiscountID` INT(10) NULL DEFAULT NULL,
157 PRIMARY KEY (`CustomercustomerID`),
158 INDEX `FKValuedCust496434` (`DiscountdiscountID` ASC),
159 INDEX `FKValuedCust741192` (`CustomercustomerID` ASC),
160 CONSTRAINT `FKValuedCust496434`
161 FOREIGN KEY (`DiscountdiscountID`)
162 REFERENCES `sys`.`discount` (`discountID`),
163 CONSTRAINT `FKValuedCust741192`
164 FOREIGN KEY (`CustomercustomerID`)
165 REFERENCES `sys`.`customer` (`customerID`))
166ENGINE = InnoDB
167DEFAULT CHARACTER SET = utf8;
168
169
170-- -----------------------------------------------------
171-- Table `sys`.`suspendedcustomer`
172-- -----------------------------------------------------
173CREATE TABLE IF NOT EXISTS `sys`.`suspendedcustomer` (
174 `ValuedCustomerCustomercustomerID` INT(10) NOT NULL,
175 PRIMARY KEY (`ValuedCustomerCustomercustomerID`),
176 INDEX `FKsuspendedC671399` (`ValuedCustomerCustomercustomerID` ASC),
177 CONSTRAINT `FKsuspendedC671399`
178 FOREIGN KEY (`ValuedCustomerCustomercustomerID`)
179 REFERENCES `sys`.`valuedcustomer` (`CustomercustomerID`))
180ENGINE = InnoDB
181DEFAULT CHARACTER SET = utf8;
182
183
184-- -----------------------------------------------------
185-- Table `sys`.`defaultcustomer`
186-- -----------------------------------------------------
187CREATE TABLE IF NOT EXISTS `sys`.`defaultcustomer` (
188 `suspendedCustomerValuedCustomerCustomercustomerID` INT(10) NOT NULL,
189 PRIMARY KEY (`suspendedCustomerValuedCustomerCustomercustomerID`),
190 INDEX `FKdefaultCus575149` (`suspendedCustomerValuedCustomerCustomercustomerID` ASC),
191 CONSTRAINT `FKdefaultCus575149`
192 FOREIGN KEY (`suspendedCustomerValuedCustomerCustomercustomerID`)
193 REFERENCES `sys`.`suspendedcustomer` (`ValuedCustomerCustomercustomerID`))
194ENGINE = InnoDB
195DEFAULT CHARACTER SET = utf8;
196
197
198-- -----------------------------------------------------
199-- Table `sys`.`staff`
200-- -----------------------------------------------------
201CREATE TABLE IF NOT EXISTS `sys`.`staff` (
202 `staffID` INT(10) NOT NULL AUTO_INCREMENT,
203 `firstName` VARCHAR(255) NOT NULL,
204 `surName` VARCHAR(255) NOT NULL,
205 `employeeType` VARCHAR(255) NOT NULL,
206 PRIMARY KEY (`staffID`))
207ENGINE = InnoDB
208AUTO_INCREMENT = 8
209DEFAULT CHARACTER SET = utf8;
210
211
212-- -----------------------------------------------------
213-- Table `sys`.`task`
214-- -----------------------------------------------------
215CREATE TABLE IF NOT EXISTS `sys`.`task` (
216 `taskID` INT(10) NOT NULL AUTO_INCREMENT,
217 `shelf` VARCHAR(10) NULL DEFAULT NULL,
218 `startDate` DATETIME NULL DEFAULT NULL,
219 `endDate` DATETIME NULL DEFAULT NULL,
220 `StaffstaffID` INT(10) NULL DEFAULT NULL,
221 `JobJobID` INT(10) NOT NULL,
222 `baseTaskbaseTaskID` INT(10) NOT NULL,
223 PRIMARY KEY (`taskID`),
224 INDEX `FKTask954031` (`StaffstaffID` ASC),
225 INDEX `FKTask543586` (`JobJobID` ASC),
226 INDEX `FKTask168614` (`baseTaskbaseTaskID` ASC),
227 CONSTRAINT `FKTask168614`
228 FOREIGN KEY (`baseTaskbaseTaskID`)
229 REFERENCES `sys`.`basetask` (`baseTaskID`),
230 CONSTRAINT `FKTask543586`
231 FOREIGN KEY (`JobJobID`)
232 REFERENCES `sys`.`job` (`JobID`),
233 CONSTRAINT `FKTask954031`
234 FOREIGN KEY (`StaffstaffID`)
235 REFERENCES `sys`.`staff` (`staffID`))
236ENGINE = InnoDB
237AUTO_INCREMENT = 13
238DEFAULT CHARACTER SET = utf8;
239
240
241-- -----------------------------------------------------
242-- Table `sys`.`enquire`
243-- -----------------------------------------------------
244CREATE TABLE IF NOT EXISTS `sys`.`enquire` (
245 `TasktaskID` INT(10) NOT NULL,
246 `enquireNumber` INT(11) NOT NULL,
247 `information` VARCHAR(255) NOT NULL,
248 PRIMARY KEY (`TasktaskID`, `enquireNumber`),
249 INDEX `FKenquire217603` (`TasktaskID` ASC),
250 CONSTRAINT `FKenquire217603`
251 FOREIGN KEY (`TasktaskID`)
252 REFERENCES `sys`.`task` (`taskID`))
253ENGINE = InnoDB
254DEFAULT CHARACTER SET = utf8;
255
256
257-- -----------------------------------------------------
258-- Table `sys`.`fixeddiscount`
259-- -----------------------------------------------------
260CREATE TABLE IF NOT EXISTS `sys`.`fixeddiscount` (
261 `DiscountdiscountID` INT(10) NOT NULL,
262 `percentDiscount` INT(10) NULL DEFAULT NULL,
263 PRIMARY KEY (`DiscountdiscountID`),
264 INDEX `FKFixedDisco316205` (`DiscountdiscountID` ASC),
265 CONSTRAINT `FKFixedDisco316205`
266 FOREIGN KEY (`DiscountdiscountID`)
267 REFERENCES `sys`.`discount` (`discountID`))
268ENGINE = InnoDB
269DEFAULT CHARACTER SET = utf8;
270
271
272-- -----------------------------------------------------
273-- Table `sys`.`flexiblediscount`
274-- -----------------------------------------------------
275CREATE TABLE IF NOT EXISTS `sys`.`flexiblediscount` (
276 `DiscountdiscountID` INT(10) NOT NULL,
277 `aquiredValue` INT(10) NOT NULL,
278 `bandBandID` INT(10) NOT NULL,
279 PRIMARY KEY (`DiscountdiscountID`),
280 INDEX `FKflexibleDi276331` (`DiscountdiscountID` ASC),
281 INDEX `FKflexibleDi397436` (`bandBandID` ASC),
282 CONSTRAINT `FKflexibleDi276331`
283 FOREIGN KEY (`DiscountdiscountID`)
284 REFERENCES `sys`.`discount` (`discountID`),
285 CONSTRAINT `FKflexibleDi397436`
286 FOREIGN KEY (`bandBandID`)
287 REFERENCES `sys`.`band` (`BandID`))
288ENGINE = InnoDB
289DEFAULT CHARACTER SET = utf8;
290
291
292-- -----------------------------------------------------
293-- Table `sys`.`variablediscount`
294-- -----------------------------------------------------
295CREATE TABLE IF NOT EXISTS `sys`.`variablediscount` (
296 `DiscountdiscountID` INT(10) NOT NULL,
297 `TasktaskID` INT(10) NOT NULL,
298 `amount` DOUBLE NOT NULL,
299 PRIMARY KEY (`DiscountdiscountID`, `TasktaskID`),
300 UNIQUE INDEX `DiscountdiscountID` (`DiscountdiscountID` ASC),
301 UNIQUE INDEX `TasktaskID` (`TasktaskID` ASC),
302 INDEX `FKvariableDi1347` (`DiscountdiscountID` ASC),
303 INDEX `FKvariableDi557826` (`TasktaskID` ASC),
304 CONSTRAINT `FKvariableDi1347`
305 FOREIGN KEY (`DiscountdiscountID`)
306 REFERENCES `sys`.`discount` (`discountID`),
307 CONSTRAINT `FKvariableDi557826`
308 FOREIGN KEY (`TasktaskID`)
309 REFERENCES `sys`.`task` (`taskID`))
310ENGINE = InnoDB
311DEFAULT CHARACTER SET = utf8;
312
313USE `new_schema` ;
314
315-- -----------------------------------------------------
316-- Table `new_schema`.`customer`
317-- -----------------------------------------------------
318CREATE TABLE IF NOT EXISTS `new_schema`.`customer` (
319 `customerId` INT(10) NOT NULL AUTO_INCREMENT,
320 `firstName` VARCHAR(255) NOT NULL,
321 `lastName` VARCHAR(255) NOT NULL,
322 `email` VARCHAR(255) NOT NULL,
323 `address` INT(10) NOT NULL,
324 `phoneNumber` VARCHAR(11) NOT NULL,
325 `accountStatus` VARCHAR(255) NOT NULL,
326 PRIMARY KEY (`customerId`))
327ENGINE = InnoDB
328DEFAULT CHARACTER SET = utf8;
329
330
331-- -----------------------------------------------------
332-- Table `new_schema`.`job`
333-- -----------------------------------------------------
334CREATE TABLE IF NOT EXISTS `new_schema`.`job` (
335 `jobId` INT(10) NOT NULL AUTO_INCREMENT,
336 `deadline` DATE NOT NULL,
337 `status` VARCHAR(255) NOT NULL,
338 `description` VARCHAR(255) NULL DEFAULT NULL,
339 `dateCreated` DATE NULL DEFAULT NULL,
340 `CustomercustomerId` INT(10) NOT NULL,
341 PRIMARY KEY (`jobId`),
342 INDEX `FKJob101969` (`CustomercustomerId` ASC),
343 CONSTRAINT `FKJob101969`
344 FOREIGN KEY (`CustomercustomerId`)
345 REFERENCES `new_schema`.`customer` (`customerId`))
346ENGINE = InnoDB
347DEFAULT CHARACTER SET = utf8;
348
349
350-- -----------------------------------------------------
351-- Table `new_schema`.`payment`
352-- -----------------------------------------------------
353CREATE TABLE IF NOT EXISTS `new_schema`.`payment` (
354 `paymentId` INT(10) NOT NULL AUTO_INCREMENT,
355 `amount` DECIMAL(2,0) NULL DEFAULT NULL,
356 `JobjobID` INT(10) NOT NULL,
357 `hasBeenPaid` VARCHAR(255) NULL DEFAULT NULL,
358 PRIMARY KEY (`paymentId`),
359 INDEX `FKPayment64667` (`JobjobID` ASC),
360 CONSTRAINT `FKPayment64667`
361 FOREIGN KEY (`JobjobID`)
362 REFERENCES `new_schema`.`job` (`jobId`))
363ENGINE = InnoDB
364DEFAULT CHARACTER SET = utf8;
365
366
367-- -----------------------------------------------------
368-- Table `new_schema`.`card_information`
369-- -----------------------------------------------------
370CREATE TABLE IF NOT EXISTS `new_schema`.`card_information` (
371 `PaymentpaymentId` INT(10) NOT NULL,
372 `cardNumber` INT(16) NOT NULL,
373 `cardType` VARCHAR(255) NOT NULL,
374 `expiryDate` DATE NOT NULL,
375 INDEX `FKCard_Infor244416` (`PaymentpaymentId` ASC),
376 CONSTRAINT `FKCard_Infor244416`
377 FOREIGN KEY (`PaymentpaymentId`)
378 REFERENCES `new_schema`.`payment` (`paymentId`))
379ENGINE = InnoDB
380DEFAULT CHARACTER SET = utf8;
381
382
383-- -----------------------------------------------------
384-- Table `new_schema`.`staff`
385-- -----------------------------------------------------
386CREATE TABLE IF NOT EXISTS `new_schema`.`staff` (
387 `staffId` INT(10) NOT NULL AUTO_INCREMENT,
388 `username` VARCHAR(255) NOT NULL,
389 `password` VARCHAR(255) NOT NULL,
390 `userType` VARCHAR(255) NOT NULL,
391 `shift` VARCHAR(255) NOT NULL,
392 PRIMARY KEY (`staffId`))
393ENGINE = InnoDB
394DEFAULT CHARACTER SET = utf8;
395
396
397-- -----------------------------------------------------
398-- Table `new_schema`.`cash_information`
399-- -----------------------------------------------------
400CREATE TABLE IF NOT EXISTS `new_schema`.`cash_information` (
401 `PaymentpaymentId` INT(10) NOT NULL,
402 `cashAmount` DECIMAL(2,0) NOT NULL,
403 `StaffstaffId` INT(10) NOT NULL,
404 INDEX `FKCash_Infor927915` (`PaymentpaymentId` ASC),
405 INDEX `FKCash_Infor558034` (`StaffstaffId` ASC),
406 CONSTRAINT `FKCash_Infor558034`
407 FOREIGN KEY (`StaffstaffId`)
408 REFERENCES `new_schema`.`staff` (`staffId`),
409 CONSTRAINT `FKCash_Infor927915`
410 FOREIGN KEY (`PaymentpaymentId`)
411 REFERENCES `new_schema`.`payment` (`paymentId`))
412ENGINE = InnoDB
413DEFAULT CHARACTER SET = utf8;
414
415
416-- -----------------------------------------------------
417-- Table `new_schema`.`discount`
418-- -----------------------------------------------------
419CREATE TABLE IF NOT EXISTS `new_schema`.`discount` (
420 `discountPlanId` INT(10) NOT NULL AUTO_INCREMENT,
421 `description` VARCHAR(255) NULL DEFAULT NULL,
422 `discountPlan` VARCHAR(255) NULL DEFAULT NULL,
423 PRIMARY KEY (`discountPlanId`))
424ENGINE = InnoDB
425DEFAULT CHARACTER SET = utf8;
426
427
428-- -----------------------------------------------------
429-- Table `new_schema`.`initial_task`
430-- -----------------------------------------------------
431CREATE TABLE IF NOT EXISTS `new_schema`.`initial_task` (
432 `taskDetailsId` INT(11) NOT NULL AUTO_INCREMENT,
433 `name` INT(11) NOT NULL,
434 `price` DECIMAL(2,0) NOT NULL,
435 `description` VARCHAR(255) NOT NULL,
436 PRIMARY KEY (`taskDetailsId`))
437ENGINE = InnoDB
438DEFAULT CHARACTER SET = utf8;
439
440
441-- -----------------------------------------------------
442-- Table `new_schema`.`task`
443-- -----------------------------------------------------
444CREATE TABLE IF NOT EXISTS `new_schema`.`task` (
445 `taskId` INT(10) NOT NULL AUTO_INCREMENT,
446 `status` VARCHAR(255) NOT NULL,
447 `dateStarted` DATE NOT NULL,
448 `dateCompleted` DATE NULL DEFAULT NULL,
449 `department` VARCHAR(255) NULL DEFAULT NULL,
450 `shelfOnCompletion` VARCHAR(10) NULL DEFAULT NULL,
451 `JobjobID` INT(10) NOT NULL,
452 `StaffstaffId` INT(10) NOT NULL,
453 `Initial_TasktaskDetailsId` INT(11) NOT NULL,
454 PRIMARY KEY (`taskId`),
455 INDEX `FKTask170241` (`Initial_TasktaskDetailsId` ASC),
456 INDEX `FKTask954095` (`StaffstaffId` ASC),
457 INDEX `FKTask438151` (`JobjobID` ASC),
458 CONSTRAINT `FKTask170241`
459 FOREIGN KEY (`Initial_TasktaskDetailsId`)
460 REFERENCES `new_schema`.`initial_task` (`taskDetailsId`),
461 CONSTRAINT `FKTask438151`
462 FOREIGN KEY (`JobjobID`)
463 REFERENCES `new_schema`.`job` (`jobId`),
464 CONSTRAINT `FKTask954095`
465 FOREIGN KEY (`StaffstaffId`)
466 REFERENCES `new_schema`.`staff` (`staffId`))
467ENGINE = InnoDB
468DEFAULT CHARACTER SET = utf8;
469
470
471-- -----------------------------------------------------
472-- Table `new_schema`.`enquiry`
473-- -----------------------------------------------------
474CREATE TABLE IF NOT EXISTS `new_schema`.`enquiry` (
475 `enquiryId` INT(10) NOT NULL AUTO_INCREMENT,
476 `subject` VARCHAR(255) NOT NULL,
477 `enquiryText` VARCHAR(255) NOT NULL,
478 `replies` VARCHAR(255) NOT NULL,
479 `TasktaskID` INT(10) NOT NULL,
480 PRIMARY KEY (`enquiryId`),
481 INDEX `FKEnquiry98991` (`TasktaskID` ASC),
482 CONSTRAINT `FKEnquiry98991`
483 FOREIGN KEY (`TasktaskID`)
484 REFERENCES `new_schema`.`task` (`taskId`))
485ENGINE = InnoDB
486DEFAULT CHARACTER SET = utf8;
487
488
489-- -----------------------------------------------------
490-- Table `new_schema`.`fixed_discount`
491-- -----------------------------------------------------
492CREATE TABLE IF NOT EXISTS `new_schema`.`fixed_discount` (
493 `DiscountPlandiscountPlanId` INT(10) NOT NULL,
494 `percentage` FLOAT NOT NULL,
495 INDEX `FKFixed_disc377114` (`DiscountPlandiscountPlanId` ASC),
496 CONSTRAINT `FKFixed_disc377114`
497 FOREIGN KEY (`DiscountPlandiscountPlanId`)
498 REFERENCES `new_schema`.`discount` (`discountPlanId`))
499ENGINE = InnoDB
500DEFAULT CHARACTER SET = utf8;
501
502
503-- -----------------------------------------------------
504-- Table `new_schema`.`flexible_discount`
505-- -----------------------------------------------------
506CREATE TABLE IF NOT EXISTS `new_schema`.`flexible_discount` (
507 `DiscountPlandiscountPlanId` INT(10) NOT NULL,
508 `noOfJobs` INT(10) NOT NULL,
509 `discountBand` VARCHAR(255) NOT NULL,
510 INDEX `FKFlexible_D8707` (`DiscountPlandiscountPlanId` ASC),
511 CONSTRAINT `FKFlexible_D8707`
512 FOREIGN KEY (`DiscountPlandiscountPlanId`)
513 REFERENCES `new_schema`.`discount` (`discountPlanId`))
514ENGINE = InnoDB
515DEFAULT CHARACTER SET = utf8;
516
517
518-- -----------------------------------------------------
519-- Table `new_schema`.`valued_customer`
520-- -----------------------------------------------------
521CREATE TABLE IF NOT EXISTS `new_schema`.`valued_customer` (
522 `DiscountPlandiscountPlanId` INT(10) NOT NULL,
523 `CustomercustomerId` INT(10) NOT NULL,
524 INDEX `FKValued_Cus900329` (`CustomercustomerId` ASC),
525 INDEX `FKValued_Cus810069` (`DiscountPlandiscountPlanId` ASC),
526 CONSTRAINT `FKValued_Cus810069`
527 FOREIGN KEY (`DiscountPlandiscountPlanId`)
528 REFERENCES `new_schema`.`discount` (`discountPlanId`),
529 CONSTRAINT `FKValued_Cus900329`
530 FOREIGN KEY (`CustomercustomerId`)
531 REFERENCES `new_schema`.`customer` (`customerId`))
532ENGINE = InnoDB
533DEFAULT CHARACTER SET = utf8;
534
535
536-- -----------------------------------------------------
537-- Table `new_schema`.`variable_discount`
538-- -----------------------------------------------------
539CREATE TABLE IF NOT EXISTS `new_schema`.`variable_discount` (
540 `DiscountPlandiscountPlanId` INT(10) NOT NULL,
541 `calculatedDiscount` FLOAT NOT NULL,
542 `TasktaskID` INT(10) NOT NULL,
543 INDEX `FKVariable_D6523` (`TasktaskID` ASC),
544 INDEX `FKVariable_D974544` (`DiscountPlandiscountPlanId` ASC),
545 CONSTRAINT `FKVariable_D6523`
546 FOREIGN KEY (`TasktaskID`)
547 REFERENCES `new_schema`.`task` (`taskId`),
548 CONSTRAINT `FKVariable_D974544`
549 FOREIGN KEY (`DiscountPlandiscountPlanId`)
550 REFERENCES `new_schema`.`discount` (`discountPlanId`))
551ENGINE = InnoDB
552DEFAULT CHARACTER SET = utf8;
553
554
555SET SQL_MODE=@OLD_SQL_MODE;
556SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
557SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;