· 5 years ago · Feb 14, 2020, 07:52 PM
1-- MySQL Script generated by MySQL Workbench
2-- Fri Feb 14 20:48:30 2020
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 nsaservices_db
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema nsaservices_db
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `nsaservices_db` DEFAULT CHARACTER SET utf8 ;
18USE `nsaservices_db` ;
19
20-- -----------------------------------------------------
21-- Table `nsaservices_db`.`membership`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `nsaservices_db`.`membership` (
24 `id` INT NOT NULL AUTO_INCREMENT,
25 `name` VARCHAR(45) NOT NULL,
26 `price` VARCHAR(45) NOT NULL,
27 `timeQuota` INT NOT NULL,
28 `openDays` VARCHAR(45) NOT NULL,
29 `closeDays` VARCHAR(45) NOT NULL,
30 `openHours` VARCHAR(45) NOT NULL,
31 `closeHours` VARCHAR(45) NOT NULL,
32 `description` LONGTEXT NOT NULL,
33 `duration` VARCHAR(45) NOT NULL,
34 PRIMARY KEY (`id`))
35ENGINE = InnoDB;
36
37
38-- -----------------------------------------------------
39-- Table `nsaservices_db`.`user`
40-- -----------------------------------------------------
41CREATE TABLE IF NOT EXISTS `nsaservices_db`.`user` (
42 `id` INT NOT NULL AUTO_INCREMENT,
43 `firstname` VARCHAR(45) NOT NULL,
44 `lastname` VARCHAR(45) NOT NULL,
45 `email` VARCHAR(80) NOT NULL,
46 `password` VARCHAR(65) NOT NULL,
47 `phone_number` VARCHAR(20) NOT NULL,
48 `address` VARCHAR(45) NOT NULL,
49 `city` VARCHAR(45) NOT NULL,
50 `rank` INT NOT NULL DEFAULT 0,
51 `profile_picture` VARCHAR(255) NULL,
52 `signup_date` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
53 `signup_token` VARCHAR(80) NULL,
54 `confirm_date` DATETIME NULL,
55 `pass_reset_token` VARCHAR(80) NULL,
56 PRIMARY KEY (`id`))
57ENGINE = InnoDB;
58
59CREATE UNIQUE INDEX `email_UNIQUE` ON `nsaservices_db`.`user` (`email` ASC) VISIBLE;
60
61CREATE UNIQUE INDEX `phone_number_UNIQUE` ON `nsaservices_db`.`user` (`phone_number` ASC) VISIBLE;
62
63
64-- -----------------------------------------------------
65-- Table `nsaservices_db`.`role`
66-- -----------------------------------------------------
67CREATE TABLE IF NOT EXISTS `nsaservices_db`.`role` (
68 `id` INT NOT NULL AUTO_INCREMENT,
69 `name` VARCHAR(45) NOT NULL,
70 `nbForDiscount` INT NULL,
71 PRIMARY KEY (`id`))
72ENGINE = InnoDB;
73
74
75-- -----------------------------------------------------
76-- Table `nsaservices_db`.`memberships_history`
77-- -----------------------------------------------------
78CREATE TABLE IF NOT EXISTS `nsaservices_db`.`memberships_history` (
79 `customer_id` INT NOT NULL,
80 `membership_id` INT NOT NULL,
81 `beginning` DATETIME NOT NULL,
82 `end` DATETIME NOT NULL,
83 `status` VARCHAR(45) NULL,
84 PRIMARY KEY (`customer_id`, `membership_id`),
85 CONSTRAINT `fk_User_has_Membership_User1`
86 FOREIGN KEY (`customer_id`)
87 REFERENCES `nsaservices_db`.`user` (`id`)
88 ON DELETE CASCADE
89 ON UPDATE CASCADE,
90 CONSTRAINT `fk_User_has_Membership_Membership1`
91 FOREIGN KEY (`membership_id`)
92 REFERENCES `nsaservices_db`.`membership` (`id`)
93 ON DELETE CASCADE
94 ON UPDATE CASCADE)
95ENGINE = InnoDB;
96
97CREATE INDEX `fk_User_has_Membership_Membership1_idx` ON `nsaservices_db`.`memberships_history` (`membership_id` ASC) VISIBLE;
98
99
100-- -----------------------------------------------------
101-- Table `nsaservices_db`.`category`
102-- -----------------------------------------------------
103CREATE TABLE IF NOT EXISTS `nsaservices_db`.`category` (
104 `id` INT NOT NULL AUTO_INCREMENT,
105 `name` VARCHAR(120) NOT NULL,
106 PRIMARY KEY (`id`))
107ENGINE = InnoDB;
108
109
110-- -----------------------------------------------------
111-- Table `nsaservices_db`.`service`
112-- -----------------------------------------------------
113CREATE TABLE IF NOT EXISTS `nsaservices_db`.`service` (
114 `id` INT NOT NULL AUTO_INCREMENT,
115 `name` VARCHAR(120) NOT NULL,
116 `price` DOUBLE NOT NULL,
117 `discountPrice` DOUBLE NULL,
118 `description` LONGTEXT NOT NULL,
119 `category_id` INT NOT NULL,
120 PRIMARY KEY (`id`),
121 CONSTRAINT `fk_Service_category1`
122 FOREIGN KEY (`category_id`)
123 REFERENCES `nsaservices_db`.`category` (`id`)
124 ON DELETE CASCADE
125 ON UPDATE CASCADE)
126ENGINE = InnoDB;
127
128CREATE INDEX `fk_Service_Category1_idx` ON `nsaservices_db`.`service` (`category_id` ASC) VISIBLE;
129
130
131-- -----------------------------------------------------
132-- Table `nsaservices_db`.`partner`
133-- -----------------------------------------------------
134CREATE TABLE IF NOT EXISTS `nsaservices_db`.`partner` (
135 `partner_id` INT NOT NULL AUTO_INCREMENT,
136 `corporation_name` VARCHAR(80) NULL,
137 `corporation_id` VARCHAR(45) NULL,
138 `lastname` VARCHAR(45) NULL,
139 `firstname` VARCHAR(45) NULL,
140 `role_id` INT NOT NULL,
141 `address` TEXT NOT NULL,
142 `city` VARCHAR(45) NOT NULL,
143 `email` VARCHAR(80) NOT NULL,
144 `password` VARCHAR(255) NULL,
145 `phone` VARCHAR(20) NOT NULL,
146 `qrcode` VARCHAR(1024) NOT NULL,
147 `pricing` DOUBLE NULL,
148 `disponibility_begin` DATETIME NULL,
149 `disponibility_end` DATETIME NULL,
150 PRIMARY KEY (`partner_id`),
151 CONSTRAINT `fk_partner_role1`
152 FOREIGN KEY (`role_id`)
153 REFERENCES `nsaservices_db`.`role` (`id`)
154 ON DELETE CASCADE
155 ON UPDATE CASCADE)
156ENGINE = InnoDB;
157
158CREATE INDEX `fk_partner_role1_idx` ON `nsaservices_db`.`partner` (`role_id` ASC) VISIBLE;
159
160CREATE UNIQUE INDEX `email_UNIQUE` ON `nsaservices_db`.`partner` (`email` ASC) VISIBLE;
161
162CREATE UNIQUE INDEX `phone_UNIQUE` ON `nsaservices_db`.`partner` (`phone` ASC) VISIBLE;
163
164
165-- -----------------------------------------------------
166-- Table `nsaservices_db`.`contract`
167-- -----------------------------------------------------
168CREATE TABLE IF NOT EXISTS `nsaservices_db`.`contract` (
169 `id` INT NOT NULL AUTO_INCREMENT,
170 `beginning` DATETIME NOT NULL,
171 `end` DATETIME NOT NULL,
172 `clauses` LONGTEXT NULL,
173 `partner_id` INT NOT NULL,
174 PRIMARY KEY (`id`),
175 CONSTRAINT `fk_contract_partner1`
176 FOREIGN KEY (`partner_id`)
177 REFERENCES `nsaservices_db`.`partner` (`partner_id`)
178 ON DELETE CASCADE
179 ON UPDATE CASCADE)
180ENGINE = InnoDB;
181
182CREATE INDEX `fk_contract_partner1_idx` ON `nsaservices_db`.`contract` (`partner_id` ASC) VISIBLE;
183
184
185-- -----------------------------------------------------
186-- Table `nsaservices_db`.`order`
187-- -----------------------------------------------------
188CREATE TABLE IF NOT EXISTS `nsaservices_db`.`order` (
189 `order_id` INT NOT NULL AUTO_INCREMENT,
190 `customer_id` INT NOT NULL,
191 `date` DATETIME NOT NULL,
192 `nbHours` DOUBLE NULL,
193 `service_id` INT NOT NULL,
194 `payment_status` TINYINT(1) NULL,
195 `order_status` VARCHAR(45) NULL,
196 PRIMARY KEY (`order_id`),
197 CONSTRAINT `fk_order_user1`
198 FOREIGN KEY (`customer_id`)
199 REFERENCES `nsaservices_db`.`user` (`id`)
200 ON DELETE CASCADE
201 ON UPDATE CASCADE,
202 CONSTRAINT `fk_order_service1`
203 FOREIGN KEY (`service_id`)
204 REFERENCES `nsaservices_db`.`service` (`id`)
205 ON DELETE CASCADE
206 ON UPDATE CASCADE)
207ENGINE = InnoDB;
208
209CREATE INDEX `fk_order_user1_idx` ON `nsaservices_db`.`order` (`customer_id` ASC) VISIBLE;
210
211CREATE INDEX `fk_order_service1_idx` ON `nsaservices_db`.`order` (`service_id` ASC) VISIBLE;
212
213
214-- -----------------------------------------------------
215-- Table `nsaservices_db`.`intervention`
216-- -----------------------------------------------------
217CREATE TABLE IF NOT EXISTS `nsaservices_db`.`intervention` (
218 `id` INT NOT NULL AUTO_INCREMENT,
219 `partner_id` INT NOT NULL,
220 `order_id` INT NOT NULL,
221 PRIMARY KEY (`id`),
222 CONSTRAINT `fk_intervention_partner1`
223 FOREIGN KEY (`partner_id`)
224 REFERENCES `nsaservices_db`.`partner` (`partner_id`)
225 ON DELETE NO ACTION
226 ON UPDATE NO ACTION,
227 CONSTRAINT `fk_intervention_order1`
228 FOREIGN KEY (`order_id`)
229 REFERENCES `nsaservices_db`.`order` (`order_id`)
230 ON DELETE NO ACTION
231 ON UPDATE NO ACTION)
232ENGINE = InnoDB;
233
234CREATE INDEX `fk_intervention_partner1_idx` ON `nsaservices_db`.`intervention` (`partner_id` ASC) VISIBLE;
235
236CREATE INDEX `fk_intervention_order1_idx` ON `nsaservices_db`.`intervention` (`order_id` ASC) VISIBLE;
237
238
239SET SQL_MODE=@OLD_SQL_MODE;
240SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
241SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;