· 8 years ago · Jun 19, 2018, 04:02 PM
1-- -----------------------------------------------------
2-- Table `ascmanagtool`.`Club`
3-- -----------------------------------------------------
4DROP TABLE IF EXISTS `ascmanagtool`.`Club` ;
5
6CREATE TABLE IF NOT EXISTS `ascmanagtool`.`Club` (
7 `entity_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
8 `name` VARCHAR(500) NOT NULL,
9 `location` VARCHAR(500) NOT NULL,
10 `created_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
11 `updated_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
12 PRIMARY KEY (`entity_id`),
13 UNIQUE INDEX `entity_id_UNIQUE` (`entity_id` ASC))
14ENGINE = InnoDB;
15
16
17-- -----------------------------------------------------
18-- Table `ascmanagtool`.`Group`
19-- -----------------------------------------------------
20DROP TABLE IF EXISTS `ascmanagtool`.`Group` ;
21
22CREATE TABLE IF NOT EXISTS `ascmanagtool`.`Group` (
23 `entity_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
24 `club_id` INT UNSIGNED NOT NULL,
25 `name` VARCHAR(500) NOT NULL,
26 `created_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
27 `updated_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
28 PRIMARY KEY (`entity_id`),
29 UNIQUE INDEX `entity_id_UNIQUE` (`entity_id` ASC),
30 INDEX `fk_club_id_idx` (`club_id` ASC),
31 CONSTRAINT `fk_club_id`
32 FOREIGN KEY (`club_id`)
33 REFERENCES `ascmanagtool`.`Club` (`entity_id`)
34 ON DELETE NO ACTION
35 ON UPDATE NO ACTION)
36ENGINE = InnoDB;
37
38
39-- -----------------------------------------------------
40-- Table `ascmanagtool`.`Licenced`
41-- -----------------------------------------------------
42DROP TABLE IF EXISTS `ascmanagtool`.`Licenced` ;
43
44CREATE TABLE IF NOT EXISTS `ascmanagtool`.`Licenced` (
45 `entity_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
46 `licence_number` INT UNSIGNED NOT NULL,
47 `gender` TINYINT(1) UNSIGNED NOT NULL,
48 `birth_date` DATETIME NOT NULL,
49 `birth_city` VARCHAR(500) NOT NULL,
50 `nationality` VARCHAR(500) NOT NULL,
51 `firstname` VARCHAR(500) NOT NULL,
52 `lastname` VARCHAR(500) NOT NULL,
53 `road_num` INT(3) UNSIGNED NOT NULL,
54 `street_name` VARCHAR(500) NOT NULL,
55 `zip` INT(5) UNSIGNED NOT NULL,
56 `city` VARCHAR(500) NOT NULL,
57 `email` VARCHAR(500) NULL,
58 `cellphone` VARCHAR(12) NULL,
59 `password` VARCHAR(500) NOT NULL,
60 `created_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
61 `updated_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
62 `is_active` TINYINT(1) NOT NULL DEFAULT 1,
63 PRIMARY KEY (`entity_id`),
64 UNIQUE INDEX `entity_id_UNIQUE` (`entity_id` ASC))
65ENGINE = InnoDB;
66
67
68-- -----------------------------------------------------
69-- Table `ascmanagtool`.`Stats`
70-- -----------------------------------------------------
71DROP TABLE IF EXISTS `ascmanagtool`.`Stats` ;
72
73CREATE TABLE IF NOT EXISTS `ascmanagtool`.`Stats` (
74 `entity_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
75 `licence_id` INT UNSIGNED NOT NULL,
76 `goals` INT(4) UNSIGNED NOT NULL,
77 `stops` INT(4) UNSIGNED NOT NULL,
78 `decisive_assists` INT(4) UNSIGNED NOT NULL,
79 `created_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
80 `updated_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
81 PRIMARY KEY (`entity_id`),
82 UNIQUE INDEX `entity_id_UNIQUE` (`entity_id` ASC),
83 INDEX `fk_licenced_id_idx` (`licence_id` ASC),
84 CONSTRAINT `fk_licenced_id`
85 FOREIGN KEY (`licence_id`)
86 REFERENCES `ascmanagtool`.`Licenced` (`entity_id`)
87 ON DELETE NO ACTION
88 ON UPDATE NO ACTION)
89ENGINE = InnoDB;
90
91
92-- -----------------------------------------------------
93-- Table `ascmanagtool`.`Matchs`
94-- -----------------------------------------------------
95DROP TABLE IF EXISTS `ascmanagtool`.`Matchs` ;
96
97CREATE TABLE IF NOT EXISTS `ascmanagtool`.`Matchs` (
98 `entity_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
99 `locals` INT UNSIGNED NOT NULL,
100 `visitors` INT UNSIGNED NOT NULL,
101 `score` VARCHAR(5) NULL,
102 `date` DATETIME NOT NULL,
103 `created_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
104 `updated_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
105 PRIMARY KEY (`entity_id`),
106 UNIQUE INDEX `entity_id_UNIQUE` (`entity_id` ASC),
107 INDEX `fk_locals_id_idx` (`locals` ASC),
108 INDEX `fl_visitors_id_idx` (`visitors` ASC),
109 CONSTRAINT `fk_locals_id`
110 FOREIGN KEY (`locals`)
111 REFERENCES `ascmanagtool`.`Club` (`entity_id`)
112 ON DELETE NO ACTION
113 ON UPDATE NO ACTION,
114 CONSTRAINT `fl_visitors_id`
115 FOREIGN KEY (`visitors`)
116 REFERENCES `ascmanagtool`.`Club` (`entity_id`)
117 ON DELETE NO ACTION
118 ON UPDATE NO ACTION)
119ENGINE = InnoDB;
120
121
122-- -----------------------------------------------------
123-- Table `ascmanagtool`.`Players`
124-- -----------------------------------------------------
125DROP TABLE IF EXISTS `ascmanagtool`.`Players` ;
126
127CREATE TABLE IF NOT EXISTS `ascmanagtool`.`Players` (
128 `entity_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
129 `match_id` INT UNSIGNED NOT NULL,
130 `player_id` INT UNSIGNED NOT NULL,
131 `position` VARCHAR(45) NOT NULL,
132 `created_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
133 `updated_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
134 PRIMARY KEY (`entity_id`),
135 UNIQUE INDEX `entity_id_UNIQUE` (`entity_id` ASC),
136 INDEX `fk_match_id_idx` (`match_id` ASC),
137 INDEX `fk_player_id_idx` (`player_id` ASC),
138 CONSTRAINT `fk_match_id`
139 FOREIGN KEY (`match_id`)
140 REFERENCES `ascmanagtool`.`Matchs` (`entity_id`)
141 ON DELETE NO ACTION
142 ON UPDATE NO ACTION,
143 CONSTRAINT `fk_player_id`
144 FOREIGN KEY (`player_id`)
145 REFERENCES `ascmanagtool`.`Licenced` (`entity_id`)
146 ON DELETE NO ACTION
147 ON UPDATE NO ACTION)
148ENGINE = InnoDB;
149
150
151-- -----------------------------------------------------
152-- Table `ascmanagtool`.`Lasts`
153-- -----------------------------------------------------
154DROP TABLE IF EXISTS `ascmanagtool`.`Lasts` ;
155
156CREATE TABLE IF NOT EXISTS `ascmanagtool`.`Lasts` (
157 `entity_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
158 `licenced_id` INT UNSIGNED NOT NULL,
159 `pword` VARCHAR(500) NULL,
160 `created_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
161 `updated_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
162 PRIMARY KEY (`entity_id`),
163 UNIQUE INDEX `entity_id_UNIQUE` (`entity_id` ASC),
164 INDEX `fk_l_id_idx` (`licenced_id` ASC),
165 CONSTRAINT `fk_l_id`
166 FOREIGN KEY (`licenced_id`)
167 REFERENCES `ascmanagtool`.`Licenced` (`entity_id`)
168 ON DELETE NO ACTION
169 ON UPDATE NO ACTION)
170ENGINE = InnoDB;
171
172
173-- -----------------------------------------------------
174-- Table `ascmanagtool`.`Favs`
175-- -----------------------------------------------------
176DROP TABLE IF EXISTS `ascmanagtool`.`Favs` ;
177
178CREATE TABLE IF NOT EXISTS `ascmanagtool`.`Favs` (
179 `entity_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
180 `licence_id` INT UNSIGNED NOT NULL,
181 `pages` VARCHAR(500) NULL,
182 PRIMARY KEY (`entity_id`),
183 UNIQUE INDEX `entity_id_UNIQUE` (`entity_id` ASC),
184 INDEX `fkey_licenced_id_idx` (`licence_id` ASC),
185 CONSTRAINT `fkey_licenced_id`
186 FOREIGN KEY (`licence_id`)
187 REFERENCES `ascmanagtool`.`Licenced` (`entity_id`)
188 ON DELETE NO ACTION
189 ON UPDATE NO ACTION)
190ENGINE = InnoDB;
191
192
193-- -----------------------------------------------------
194-- Table `ascmanagtool`.`Trainings`
195-- -----------------------------------------------------
196DROP TABLE IF EXISTS `ascmanagtool`.`Trainings` ;
197
198CREATE TABLE IF NOT EXISTS `ascmanagtool`.`Trainings` (
199 `entity_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
200 `group_id` INT UNSIGNED NOT NULL,
201 `date` DATETIME NOT NULL,
202 `created_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
203 `updated_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
204 PRIMARY KEY (`entity_id`),
205 UNIQUE INDEX `entity_id_UNIQUE` (`entity_id` ASC),
206 INDEX `fkey_group_id_idx` (`group_id` ASC),
207 CONSTRAINT `fkey_group_id`
208 FOREIGN KEY (`group_id`)
209 REFERENCES `ascmanagtool`.`Group` (`entity_id`)
210 ON DELETE NO ACTION
211 ON UPDATE NO ACTION)
212ENGINE = InnoDB;
213
214
215-- -----------------------------------------------------
216-- Table `ascmanagtool`.`Categories`
217-- -----------------------------------------------------
218DROP TABLE IF EXISTS `ascmanagtool`.`Categories` ;
219
220CREATE TABLE IF NOT EXISTS `ascmanagtool`.`Categories` (
221 `entity_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
222 `label` VARCHAR(70) NOT NULL,
223 `created_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
224 `updated_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
225 PRIMARY KEY (`entity_id`),
226 UNIQUE INDEX `entity_id_UNIQUE` (`entity_id` ASC))
227ENGINE = InnoDB;
228
229-- -----------------------------------------------------
230-- Table `mydb`.`Trainings_has_Licenced`
231-- -----------------------------------------------------
232CREATE TABLE IF NOT EXISTS `ascmanagtool`.`Trainings_has_Licenced` (
233 `Trainings_entity_id` INT UNSIGNED NOT NULL,
234 `Licenced_entity_id` INT UNSIGNED NOT NULL,
235 PRIMARY KEY (`Trainings_entity_id`, `Licenced_entity_id`),
236 INDEX `fk_Trainings_has_Licenced_Licenced1_idx` (`Licenced_entity_id` ASC),
237 INDEX `fk_Trainings_has_Licenced_Trainings1_idx` (`Trainings_entity_id` ASC),
238 CONSTRAINT `fk_Trainings_has_Licenced_Trainings1`
239 FOREIGN KEY (`Trainings_entity_id`)
240 REFERENCES `ascmanagtool`.`Trainings` (`entity_id`)
241 ON DELETE NO ACTION
242 ON UPDATE NO ACTION,
243 CONSTRAINT `fk_Trainings_has_Licenced_Licenced1`
244 FOREIGN KEY (`Licenced_entity_id`)
245 REFERENCES `ascmanagtool`.`Licenced` (`entity_id`)
246 ON DELETE NO ACTION
247 ON UPDATE NO ACTION)
248ENGINE = InnoDB;
249
250
251-- -----------------------------------------------------
252-- Table `mydb`.`Group_has_Licenced`
253-- -----------------------------------------------------
254CREATE TABLE IF NOT EXISTS `ascmanagtool`.`Group_has_Licenced` (
255 `Group_entity_id` INT UNSIGNED NOT NULL,
256 `Licenced_entity_id` INT UNSIGNED NOT NULL,
257 PRIMARY KEY (`Group_entity_id`, `Licenced_entity_id`),
258 INDEX `fk_Group_has_Licenced_Licenced1_idx` (`Licenced_entity_id` ASC),
259 INDEX `fk_Group_has_Licenced_Group1_idx` (`Group_entity_id` ASC),
260 CONSTRAINT `fk_Group_has_Licenced_Group1`
261 FOREIGN KEY (`Group_entity_id`)
262 REFERENCES `ascmanagtool`.`Group` (`entity_id`)
263 ON DELETE NO ACTION
264 ON UPDATE NO ACTION,
265 CONSTRAINT `fk_Group_has_Licenced_Licenced1`
266 FOREIGN KEY (`Licenced_entity_id`)
267 REFERENCES `ascmanagtool`.`Licenced` (`entity_id`)
268 ON DELETE NO ACTION
269 ON UPDATE NO ACTION)
270ENGINE = InnoDB;
271
272
273-- -----------------------------------------------------
274-- Table `mydb`.`Categories_has_Licenced`
275-- -----------------------------------------------------
276CREATE TABLE IF NOT EXISTS `ascmanagtool`.`Categories_has_Licenced` (
277 `Categories_entity_id` INT UNSIGNED NOT NULL,
278 `Licenced_entity_id` INT UNSIGNED NOT NULL,
279 PRIMARY KEY (`Categories_entity_id`, `Licenced_entity_id`),
280 INDEX `fk_Categories_has_Licenced_Licenced1_idx` (`Licenced_entity_id` ASC),
281 INDEX `fk_Categories_has_Licenced_Categories1_idx` (`Categories_entity_id` ASC),
282 CONSTRAINT `fk_Categories_has_Licenced_Categories1`
283 FOREIGN KEY (`Categories_entity_id`)
284 REFERENCES `ascmanagtool`.`Categories` (`entity_id`)
285 ON DELETE NO ACTION
286 ON UPDATE NO ACTION,
287 CONSTRAINT `fk_Categories_has_Licenced_Licenced1`
288 FOREIGN KEY (`Licenced_entity_id`)
289 REFERENCES `ascmanagtool`.`Licenced` (`entity_id`)
290 ON DELETE NO ACTION
291 ON UPDATE NO ACTION)
292ENGINE = InnoDB;
293
294
295SET SQL_MODE=@OLD_SQL_MODE;
296SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
297SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;