· 6 years ago · Jan 24, 2020, 08:38 AM
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='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
6
7-- -----------------------------------------------------
8-- Schema mydb
9-- -----------------------------------------------------
10-- -----------------------------------------------------
11-- Schema db_manolito
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema db_manolito
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `db_manolito` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ;
18USE `db_manolito` ;
19
20-- -----------------------------------------------------
21-- Table `db_manolito`.`userspec`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `db_manolito`.`userspec` (
24 `id` INT(6) NOT NULL,
25 `dni` VARCHAR(9) NULL DEFAULT NULL,
26 `iban_number` VARCHAR(34) NULL DEFAULT NULL,
27 `nie` VARCHAR(9) NULL DEFAULT NULL,
28 `passport` VARCHAR(10) NULL DEFAULT NULL,
29 `ssn` VARCHAR(16) NOT NULL,
30 PRIMARY KEY (`id`),
31 UNIQUE INDEX `UK_3rq3680wycpheo0o71mr3av9w` (`ssn` ASC),
32 UNIQUE INDEX `UK_bpgyb16004gsv8496utfypqgy` (`dni` ASC),
33 UNIQUE INDEX `UK_5ig62xay9rup8yf0rpstlw9jn` (`nie` ASC),
34 UNIQUE INDEX `UK_cgmnsrppq50r6ars8hp95y0kv` (`passport` ASC))
35ENGINE = InnoDB
36DEFAULT CHARACTER SET = utf8mb4
37COLLATE = utf8mb4_0900_ai_ci;
38
39
40-- -----------------------------------------------------
41-- Table `db_manolito`.`autonomy`
42-- -----------------------------------------------------
43CREATE TABLE IF NOT EXISTS `db_manolito`.`autonomy` (
44 `id` INT(6) NOT NULL AUTO_INCREMENT,
45 `name` VARCHAR(20) NULL DEFAULT NULL,
46 PRIMARY KEY (`id`))
47ENGINE = InnoDB
48AUTO_INCREMENT = 20
49DEFAULT CHARACTER SET = utf8mb4
50COLLATE = utf8mb4_0900_ai_ci;
51
52
53-- -----------------------------------------------------
54-- Table `db_manolito`.`province`
55-- -----------------------------------------------------
56CREATE TABLE IF NOT EXISTS `db_manolito`.`province` (
57 `id` INT(6) NOT NULL AUTO_INCREMENT,
58 `name` VARCHAR(30) NULL DEFAULT NULL,
59 `autonomy_id` INT(6) NULL DEFAULT NULL,
60 PRIMARY KEY (`id`),
61 INDEX `FKfoyf3fc1tssl59xwef7brf1tg` (`autonomy_id` ASC),
62 CONSTRAINT `FKfoyf3fc1tssl59xwef7brf1tg`
63 FOREIGN KEY (`autonomy_id`)
64 REFERENCES `db_manolito`.`autonomy` (`id`))
65ENGINE = InnoDB
66AUTO_INCREMENT = 53
67DEFAULT CHARACTER SET = utf8mb4
68COLLATE = utf8mb4_0900_ai_ci;
69
70
71-- -----------------------------------------------------
72-- Table `db_manolito`.`municipe`
73-- -----------------------------------------------------
74CREATE TABLE IF NOT EXISTS `db_manolito`.`municipe` (
75 `id` INT(6) NOT NULL AUTO_INCREMENT,
76 `name` VARCHAR(35) NULL DEFAULT NULL,
77 `province_id` INT(6) NULL DEFAULT NULL,
78 PRIMARY KEY (`id`),
79 INDEX `FKets6albrrudgue0wsnrsj7lg3` (`province_id` ASC),
80 CONSTRAINT `FKets6albrrudgue0wsnrsj7lg3`
81 FOREIGN KEY (`province_id`)
82 REFERENCES `db_manolito`.`province` (`id`))
83ENGINE = InnoDB
84AUTO_INCREMENT = 253
85DEFAULT CHARACTER SET = utf8mb4
86COLLATE = utf8mb4_0900_ai_ci;
87
88
89-- -----------------------------------------------------
90-- Table `db_manolito`.`postal_code`
91-- -----------------------------------------------------
92CREATE TABLE IF NOT EXISTS `db_manolito`.`postal_code` (
93 `id` INT(6) NOT NULL AUTO_INCREMENT,
94 `code` INT(5) NOT NULL,
95 `municipe_id` INT(6) NULL DEFAULT NULL,
96 PRIMARY KEY (`id`),
97 INDEX `FKkxvibmlj9h8exm8xypjemru47` (`municipe_id` ASC),
98 CONSTRAINT `FKkxvibmlj9h8exm8xypjemru47`
99 FOREIGN KEY (`municipe_id`)
100 REFERENCES `db_manolito`.`municipe` (`id`))
101ENGINE = InnoDB
102AUTO_INCREMENT = 306
103DEFAULT CHARACTER SET = utf8mb4
104COLLATE = utf8mb4_0900_ai_ci;
105
106
107-- -----------------------------------------------------
108-- Table `db_manolito`.`address`
109-- -----------------------------------------------------
110CREATE TABLE IF NOT EXISTS `db_manolito`.`address` (
111 `id` INT(6) NOT NULL AUTO_INCREMENT,
112 `door` VARCHAR(1) NULL DEFAULT NULL,
113 `floor` INT(2) NOT NULL,
114 `portal` INT(2) NOT NULL,
115 `street` VARCHAR(50) NULL DEFAULT NULL,
116 `postal_code_id` INT(6) NULL DEFAULT NULL,
117 `userspec_id` INT(6) NULL DEFAULT NULL,
118 PRIMARY KEY (`id`),
119 INDEX `FKlt6jp7yd0eheixxymk8p4gku9` (`postal_code_id` ASC),
120 INDEX `FKa7h66ygg0ot04m1k99yeqi9qj` (`userspec_id` ASC),
121 CONSTRAINT `FKa7h66ygg0ot04m1k99yeqi9qj`
122 FOREIGN KEY (`userspec_id`)
123 REFERENCES `db_manolito`.`userspec` (`id`),
124 CONSTRAINT `FKlt6jp7yd0eheixxymk8p4gku9`
125 FOREIGN KEY (`postal_code_id`)
126 REFERENCES `db_manolito`.`postal_code` (`id`))
127ENGINE = InnoDB
128AUTO_INCREMENT = 12
129DEFAULT CHARACTER SET = utf8mb4
130COLLATE = utf8mb4_0900_ai_ci;
131
132
133-- -----------------------------------------------------
134-- Table `db_manolito`.`permissions`
135-- -----------------------------------------------------
136CREATE TABLE IF NOT EXISTS `db_manolito`.`permissions` (
137 `id` INT(6) NOT NULL AUTO_INCREMENT,
138 `name` VARCHAR(20) NULL DEFAULT NULL,
139 PRIMARY KEY (`id`))
140ENGINE = InnoDB
141AUTO_INCREMENT = 14
142DEFAULT CHARACTER SET = utf8mb4
143COLLATE = utf8mb4_0900_ai_ci;
144
145
146-- -----------------------------------------------------
147-- Table `db_manolito`.`roles`
148-- -----------------------------------------------------
149CREATE TABLE IF NOT EXISTS `db_manolito`.`roles` (
150 `id` INT(6) NOT NULL AUTO_INCREMENT,
151 `name` VARCHAR(20) NULL DEFAULT NULL,
152 PRIMARY KEY (`id`))
153ENGINE = InnoDB
154AUTO_INCREMENT = 4
155DEFAULT CHARACTER SET = utf8mb4
156COLLATE = utf8mb4_0900_ai_ci;
157
158
159-- -----------------------------------------------------
160-- Table `db_manolito`.`roles_permissions`
161-- -----------------------------------------------------
162CREATE TABLE IF NOT EXISTS `db_manolito`.`roles_permissions` (
163 `role_id` INT(6) NOT NULL,
164 `permissions_id` INT(6) NOT NULL,
165 PRIMARY KEY (`role_id`, `permissions_id`),
166 INDEX `FK570wuy6sacdnrw8wdqjfh7j0q` (`permissions_id` ASC),
167 CONSTRAINT `FK570wuy6sacdnrw8wdqjfh7j0q`
168 FOREIGN KEY (`permissions_id`)
169 REFERENCES `db_manolito`.`permissions` (`id`),
170 CONSTRAINT `FKqi9odri6c1o81vjox54eedwyh`
171 FOREIGN KEY (`role_id`)
172 REFERENCES `db_manolito`.`roles` (`id`))
173ENGINE = InnoDB
174DEFAULT CHARACTER SET = utf8mb4
175COLLATE = utf8mb4_0900_ai_ci;
176
177
178-- -----------------------------------------------------
179-- Table `db_manolito`.`user_details`
180-- -----------------------------------------------------
181CREATE TABLE IF NOT EXISTS `db_manolito`.`user_details` (
182 `id` INT(6) NOT NULL AUTO_INCREMENT,
183 `dasid` VARCHAR(7) NOT NULL,
184 `email` VARCHAR(30) NOT NULL,
185 `name` VARCHAR(20) NULL DEFAULT NULL,
186 `status` VARCHAR(1) NOT NULL,
187 `surname1` VARCHAR(40) NULL DEFAULT NULL,
188 `surname2` VARCHAR(40) NULL DEFAULT NULL,
189 `location` VARCHAR(20) NULL DEFAULT NULL,
190 PRIMARY KEY (`id`),
191 UNIQUE INDEX `UK_wcvs0jlp2br5w65bglga5hy1` (`dasid` ASC))
192ENGINE = InnoDB
193AUTO_INCREMENT = 24
194DEFAULT CHARACTER SET = utf8mb4
195COLLATE = utf8mb4_0900_ai_ci;
196
197
198-- -----------------------------------------------------
199-- Table `db_manolito`.`user_details_roles`
200-- -----------------------------------------------------
201CREATE TABLE IF NOT EXISTS `db_manolito`.`user_details_roles` (
202 `user_id` INT(6) NOT NULL,
203 `role_id` INT(6) NOT NULL,
204 PRIMARY KEY (`user_id`, `role_id`),
205 INDEX `FKb48hks4x9aq8jp80knkm5uj6w` (`role_id` ASC),
206 CONSTRAINT `FKb48hks4x9aq8jp80knkm5uj6w`
207 FOREIGN KEY (`role_id`)
208 REFERENCES `db_manolito`.`roles` (`id`),
209 CONSTRAINT `FKpu8elkbgi46pmghqsodex3xdw`
210 FOREIGN KEY (`user_id`)
211 REFERENCES `db_manolito`.`user_details` (`id`))
212ENGINE = InnoDB
213DEFAULT CHARACTER SET = utf8mb4
214COLLATE = utf8mb4_0900_ai_ci;
215
216
217-- -----------------------------------------------------
218-- Table `db_manolito`.`users`
219-- -----------------------------------------------------
220CREATE TABLE IF NOT EXISTS `db_manolito`.`users` (
221 `id` INT(6) NOT NULL AUTO_INCREMENT,
222 `counter` INT(1) NOT NULL,
223 `password` VARCHAR(60) NULL DEFAULT NULL,
224 PRIMARY KEY (`id`))
225ENGINE = InnoDB
226AUTO_INCREMENT = 23
227DEFAULT CHARACTER SET = utf8mb4
228COLLATE = utf8mb4_0900_ai_ci;
229
230
231-- -----------------------------------------------------
232-- Table `db_manolito`.`userspayroll`
233-- -----------------------------------------------------
234CREATE TABLE IF NOT EXISTS `db_manolito`.`userspayroll` (
235 `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
236 `dni` VARCHAR(9) NULL DEFAULT NULL,
237 `name` VARCHAR(20) NOT NULL,
238 `net_salary` VARCHAR(255) NULL DEFAULT NULL,
239 `ssnn` VARCHAR(16) NULL DEFAULT NULL,
240 `surrname1` VARCHAR(20) NOT NULL,
241 `surrname2` VARCHAR(20) NULL DEFAULT NULL,
242 PRIMARY KEY (`id`),
243 UNIQUE INDEX `UK_79ow8oj4k44628a9w49q21o1q` (`dni` ASC),
244 UNIQUE INDEX `UK_surg95j5u7o5v1ga23w34ieul` (`ssnn` ASC))
245ENGINE = InnoDB
246AUTO_INCREMENT = 2
247DEFAULT CHARACTER SET = utf8mb4
248COLLATE = utf8mb4_0900_ai_ci;
249
250
251SET SQL_MODE=@OLD_SQL_MODE;
252SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
253SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;