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