SArjgM0E

1-- MySQL Script generated by MySQL Workbench
2-- Mon May 28 16:50:31 2018
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='TRADITIONAL,ALLOW_INVALID_DATES';
9
10-- -----------------------------------------------------
11-- Schema ascmanagtool
12-- -----------------------------------------------------
13
14
15-- -----------------------------------------------------
16-- Schema ascmanagtool
17-- -----------------------------------------------------
18CREATE SCHEMA IF NOT EXISTS `ascmanagtool` DEFAULT CHARACTER SET utf8 ;
19USE `ascmanagtool` ;
20
21-- -----------------------------------------------------
22-- Table `ascmanagtool`.`Club`
23-- -----------------------------------------------------
24
25
26CREATE TABLE IF NOT EXISTS `ascmanagtool`.`Club` (
27  `entity_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
28  `name` VARCHAR(500) NOT NULL,
29  `location` VARCHAR(500) NOT NULL,
30  `created_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
31  `updated_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
32  PRIMARY KEY (`entity_id`),
33  UNIQUE INDEX `entity_id_UNIQUE` (`entity_id` ASC))
34ENGINE = InnoDB;
35
36
37-- -----------------------------------------------------
38-- Table `ascmanagtool`.`Group`
39-- -----------------------------------------------------
40
41
42CREATE TABLE IF NOT EXISTS `ascmanagtool`.`Group` (
43  `entity_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
44  `club_id` INT UNSIGNED NOT NULL,
45  `name` VARCHAR(500) NOT NULL,
46  `created_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
47  `updated_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
48  PRIMARY KEY (`entity_id`),
49  UNIQUE INDEX `entity_id_UNIQUE` (`entity_id` ASC),
50  INDEX `fk_club_id_idx` (`club_id` ASC),
51  CONSTRAINT `fk_club_id`
52    FOREIGN KEY (`club_id`)
53    REFERENCES `ascmanagtool`.`Club` (`entity_id`)
54    ON DELETE NO ACTION
55    ON UPDATE NO ACTION)
56ENGINE = InnoDB;
57
58
59-- -----------------------------------------------------
60-- Table `ascmanagtool`.`Licenced`
61-- -----------------------------------------------------
62
63
64CREATE TABLE IF NOT EXISTS `ascmanagtool`.`Licenced` (
65  `entity_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
66  `licence_number` INT UNSIGNED NOT NULL,
67  `gender` TINYINT(1) UNSIGNED NOT NULL,
68  `birth_date` DATETIME NOT NULL,
69  `birth_city` VARCHAR(500) NOT NULL,
70  `nationality` VARCHAR(500) NOT NULL,
71  `firstname` VARCHAR(500) NOT NULL,
72  `lastname` VARCHAR(500) NOT NULL,
73  `road_num` INT(3) UNSIGNED NOT NULL,
74  `street_name` VARCHAR(500) NOT NULL,
75  `zip` INT(5) UNSIGNED NOT NULL,
76  `city` VARCHAR(500) NOT NULL,
77  `email` VARCHAR(500) NULL,
78  `cellphone` VARCHAR(12) NULL,
79  `password` VARCHAR(500) NOT NULL,
80  `created_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
81  `updated_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
82  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
83  PRIMARY KEY (`entity_id`),
84  UNIQUE INDEX `entity_id_UNIQUE` (`entity_id` ASC))
85ENGINE = InnoDB;
86
87
88-- -----------------------------------------------------
89-- Table `ascmanagtool`.`Stats`
90-- -----------------------------------------------------
91
92
93CREATE TABLE IF NOT EXISTS `ascmanagtool`.`Stats` (
94  `entity_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
95  `licence_id` INT UNSIGNED NOT NULL,
96  `goals` INT(4) UNSIGNED NOT NULL,
97  `stops` INT(4) UNSIGNED NOT NULL,
98  `decisive_assists` INT(4) UNSIGNED NOT NULL,
99  `created_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
100  `updated_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
101  PRIMARY KEY (`entity_id`),
102  UNIQUE INDEX `entity_id_UNIQUE` (`entity_id` ASC),
103  INDEX `fk_licenced_id_idx` (`licence_id` ASC),
104  CONSTRAINT `fk_licenced_id`
105    FOREIGN KEY (`licence_id`)
106    REFERENCES `ascmanagtool`.`Licenced` (`entity_id`)
107    ON DELETE NO ACTION
108    ON UPDATE NO ACTION)
109ENGINE = InnoDB;
110
111
112-- -----------------------------------------------------
113-- Table `ascmanagtool`.`Matchs`
114-- -----------------------------------------------------
115
116
117CREATE TABLE IF NOT EXISTS `ascmanagtool`.`Matchs` (
118  `entity_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
119  `locals` INT UNSIGNED NOT NULL,
120  `visitors` INT UNSIGNED NOT NULL,
121  `score` VARCHAR(5) NULL,
122  `date` DATETIME NOT NULL,
123  `created_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
124  `updated_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
125  PRIMARY KEY (`entity_id`),
126  UNIQUE INDEX `entity_id_UNIQUE` (`entity_id` ASC),
127  INDEX `fk_locals_id_idx` (`locals` ASC),
128  INDEX `fl_visitors_id_idx` (`visitors` ASC),
129  CONSTRAINT `fk_locals_id`
130    FOREIGN KEY (`locals`)
131    REFERENCES `ascmanagtool`.`Club` (`entity_id`)
132    ON DELETE NO ACTION
133    ON UPDATE NO ACTION,
134  CONSTRAINT `fl_visitors_id`
135    FOREIGN KEY (`visitors`)
136    REFERENCES `ascmanagtool`.`Club` (`entity_id`)
137    ON DELETE NO ACTION
138    ON UPDATE NO ACTION)
139ENGINE = InnoDB;
140
141
142-- -----------------------------------------------------
143-- Table `ascmanagtool`.`Players`
144-- -----------------------------------------------------
145
146
147CREATE TABLE IF NOT EXISTS `ascmanagtool`.`Players` (
148  `entity_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
149  `match_id` INT UNSIGNED NOT NULL,
150  `player_id` INT UNSIGNED NOT NULL,
151  `position` VARCHAR(45) NOT NULL,
152  `created_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
153  `updated_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
154  PRIMARY KEY (`entity_id`),
155  UNIQUE INDEX `entity_id_UNIQUE` (`entity_id` ASC),
156  INDEX `fk_match_id_idx` (`match_id` ASC),
157  INDEX `fk_player_id_idx` (`player_id` ASC),
158  CONSTRAINT `fk_match_id`
159    FOREIGN KEY (`match_id`)
160    REFERENCES `ascmanagtool`.`Matchs` (`entity_id`)
161    ON DELETE NO ACTION
162    ON UPDATE NO ACTION,
163  CONSTRAINT `fk_player_id`
164    FOREIGN KEY (`player_id`)
165    REFERENCES `ascmanagtool`.`Licenced` (`entity_id`)
166    ON DELETE NO ACTION
167    ON UPDATE NO ACTION)
168ENGINE = InnoDB;
169
170
171-- -----------------------------------------------------
172-- Table `ascmanagtool`.`Lasts`
173-- -----------------------------------------------------
174
175
176CREATE TABLE IF NOT EXISTS `ascmanagtool`.`Lasts` (
177  `entity_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
178  `licenced_id` INT UNSIGNED NOT NULL,
179  `pword` VARCHAR(500) NULL,
180  `created_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
181  `updated_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
182  PRIMARY KEY (`entity_id`),
183  UNIQUE INDEX `entity_id_UNIQUE` (`entity_id` ASC),
184  INDEX `fk_l_id_idx` (`licenced_id` ASC),
185  CONSTRAINT `fk_l_id`
186    FOREIGN KEY (`licenced_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`.`Favs`
195-- -----------------------------------------------------
196
197
198CREATE TABLE IF NOT EXISTS `ascmanagtool`.`Favs` (
199  `entity_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
200  `licence_id` INT UNSIGNED NOT NULL,
201  `pages` VARCHAR(500) NULL,
202  PRIMARY KEY (`entity_id`),
203  UNIQUE INDEX `entity_id_UNIQUE` (`entity_id` ASC),
204  INDEX `fkey_licenced_id_idx` (`licence_id` ASC),
205  CONSTRAINT `fkey_licenced_id`
206    FOREIGN KEY (`licence_id`)
207    REFERENCES `ascmanagtool`.`Licenced` (`entity_id`)
208    ON DELETE NO ACTION
209    ON UPDATE NO ACTION)
210ENGINE = InnoDB;
211
212
213-- -----------------------------------------------------
214-- Table `ascmanagtool`.`Trainings`
215-- -----------------------------------------------------
216
217
218CREATE TABLE IF NOT EXISTS `ascmanagtool`.`Trainings` (
219  `entity_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
220  `group_id` INT UNSIGNED NOT NULL,
221  `date` DATETIME NOT NULL,
222  `created_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
223  `updated_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
224  PRIMARY KEY (`entity_id`),
225  UNIQUE INDEX `entity_id_UNIQUE` (`entity_id` ASC),
226  INDEX `fkey_group_id_idx` (`group_id` ASC),
227  CONSTRAINT `fkey_group_id`
228    FOREIGN KEY (`group_id`)
229    REFERENCES `ascmanagtool`.`Group` (`entity_id`)
230    ON DELETE NO ACTION
231    ON UPDATE NO ACTION)
232ENGINE = InnoDB;
233
234
235-- -----------------------------------------------------
236-- Table `ascmanagtool`.`Categories`
237-- -----------------------------------------------------
238
239
240CREATE TABLE IF NOT EXISTS `ascmanagtool`.`Categories` (
241  `entity_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
242  `licenced_id` INT UNSIGNED NOT NULL,
243  `label` VARCHAR(70) NOT NULL,
244  `created_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
245  `updated_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
246  PRIMARY KEY (`entity_id`),
247  UNIQUE INDEX `entity_id_UNIQUE` (`entity_id` ASC),
248  INDEX `fk_cat_licenced_id_idx` (`licenced_id` ASC),
249  CONSTRAINT `fk_cat_licenced_id`
250    FOREIGN KEY (`licenced_id`)
251    REFERENCES `ascmanagtool`.`Licenced` (`entity_id`)
252    ON DELETE NO ACTION
253    ON UPDATE NO ACTION)
254ENGINE = InnoDB;
255
256
257SET SQL_MODE=@OLD_SQL_MODE;
258SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
259SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;