5A6a5pMJ

1-- MySQL Script generated by MySQL Workbench
2-- Fri Jan  4 03:01:27 2019
3-- Model: New Model    Version: 1.0
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`.`Client`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `mydb`.`Client` (
24  `phone` BIGINT UNSIGNED NULL,
25  `full_name` VARCHAR(128) NOT NULL,
26  PRIMARY KEY (`phone`))
27ENGINE = InnoDB;
28
29
30-- -----------------------------------------------------
31-- Table `mydb`.`Worker`
32-- -----------------------------------------------------
33CREATE TABLE IF NOT EXISTS `mydb`.`Worker` (
34  `tax_id` BIGINT NULL,
35  `schedule` VARCHAR(256) NOT NULL,
36  `full_name` VARCHAR(128) NOT NULL,
37  `phone` BIGINT UNSIGNED NOT NULL,
38  `occupation` SET('acceptor', 'operator') NOT NULL,
39  `password_hash` VARCHAR(256) NOT NULL,
40  PRIMARY KEY (`tax_id`))
41ENGINE = InnoDB;
42
43
44-- -----------------------------------------------------
45-- Table `mydb`.`Order`
46-- -----------------------------------------------------
47CREATE TABLE IF NOT EXISTS `mydb`.`Order` (
48  `id` INT NULL AUTO_INCREMENT,
49  `client_phone` BIGINT NOT NULL,
50  `acceptor_tax_id` BIGINT NOT NULL,
51  `deliverer_tax_id1` BIGINT NOT NULL,
52  `accepted` DATE NOT NULL,
53  `notified` DATE NOT NULL,
54  `received` DATE NOT NULL,
55  `description` VARCHAR(256) NOT NULL,
56  `urgent` TINYINT NOT NULL,
57  PRIMARY KEY (`id`),
58  INDEX `fk_Order_Client_idx` (`client_phone` ASC) VISIBLE,
59  INDEX `fk_Order_Worker1_idx` (`acceptor_tax_id` ASC) VISIBLE,
60  INDEX `fk_Order_Worker2_idx` (`deliverer_tax_id1` ASC) VISIBLE,
61  CONSTRAINT `fk_Order_Client`
62    FOREIGN KEY (`client_phone`)
63    REFERENCES `mydb`.`Client` (`phone`)
64    ON DELETE NO ACTION
65    ON UPDATE NO ACTION,
66  CONSTRAINT `fk_Order_Worker_acceptor`
67    FOREIGN KEY (`acceptor_tax_id`)
68    REFERENCES `mydb`.`Worker` (`tax_id`)
69    ON DELETE NO ACTION
70    ON UPDATE NO ACTION,
71  CONSTRAINT `fk_Order_Worker_deliverer`
72    FOREIGN KEY (`deliverer_tax_id1`)
73    REFERENCES `mydb`.`Worker` (`tax_id`)
74    ON DELETE NO ACTION
75    ON UPDATE NO ACTION)
76ENGINE = InnoDB;
77
78
79-- -----------------------------------------------------
80-- Table `mydb`.`Order_item`
81-- -----------------------------------------------------
82CREATE TABLE IF NOT EXISTS `mydb`.`Order_item` (
83  `id` INT NULL AUTO_INCREMENT,
84  `order_id` INT NOT NULL,
85  `operator_tax_id` BIGINT NOT NULL,
86  `ready` TINYINT NOT NULL,
87  `label` SMALLINT NOT NULL,
88  `description` VARCHAR(256) NOT NULL,
89  `price` INT UNSIGNED NOT NULL,
90  INDEX `fk_Order_item_Order1_idx` (`order_id` ASC) VISIBLE,
91  PRIMARY KEY (`id`),
92  INDEX `fk_Order_item_Worker1_idx` (`operator_tax_id` ASC) VISIBLE,
93  CONSTRAINT `fk_Order_item_Order1`
94    FOREIGN KEY (`order_id`)
95    REFERENCES `mydb`.`Order` (`id`)
96    ON DELETE NO ACTION
97    ON UPDATE NO ACTION,
98  CONSTRAINT `fk_Order_item_Worker1`
99    FOREIGN KEY (`operator_tax_id`)
100    REFERENCES `mydb`.`Worker` (`tax_id`)
101    ON DELETE NO ACTION
102    ON UPDATE NO ACTION)
103ENGINE = InnoDB;
104
105
106-- -----------------------------------------------------
107-- Table `mydb`.`Complaint`
108-- -----------------------------------------------------
109CREATE TABLE IF NOT EXISTS `mydb`.`Complaint` (
110  `order_item_id` INT NOT NULL,
111  `date` DATE NOT NULL,
112  `description` VARCHAR(512) NOT NULL,
113  `resolved` TINYINT NOT NULL,
114  PRIMARY KEY (`order_item_id`),
115  CONSTRAINT `Order_item_id`
116    FOREIGN KEY (`order_item_id`)
117    REFERENCES `mydb`.`Order_item` (`id`)
118    ON DELETE NO ACTION
119    ON UPDATE NO ACTION)
120ENGINE = InnoDB;
121
122
123-- -----------------------------------------------------
124-- Table `mydb`.`Complaint_update`
125-- -----------------------------------------------------
126CREATE TABLE IF NOT EXISTS `mydb`.`Complaint_update` (
127  `id` INT NULL AUTO_INCREMENT,
128  `complaint_Order_item_id` INT NOT NULL,
129  `date` DATE NOT NULL,
130  `description` VARCHAR(512) NOT NULL,
131  PRIMARY KEY (`id`),
132  INDEX `fk_Complaint_update_Complaint1_idx` (`complaint_Order_item_id` ASC) VISIBLE,
133  CONSTRAINT `fk_Complaint_update_Complaint1`
134    FOREIGN KEY (`complaint_Order_item_id`)
135    REFERENCES `mydb`.`Complaint` (`order_item_id`)
136    ON DELETE NO ACTION
137    ON UPDATE NO ACTION)
138ENGINE = InnoDB;
139
140
141SET SQL_MODE=@OLD_SQL_MODE;
142SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
143SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;