· 7 years ago · Feb 22, 2019, 01:52 AM
1
2-- -----------------------------------------------------
3-- Table `work_rate`
4-- -----------------------------------------------------
5CREATE TABLE IF NOT EXISTS `work_rate` (
6 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
7 `workRate` VARCHAR(45) NOT NULL,
8 PRIMARY KEY (`id`),
9 UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
10 UNIQUE INDEX `workRate_UNIQUE` (`workRate` ASC) )
11ENGINE = InnoDB;
12
13
14-- -----------------------------------------------------
15-- Table `position`
16-- -----------------------------------------------------
17CREATE TABLE IF NOT EXISTS `position` (
18 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
19 `position` VARCHAR(45) NULL,
20 PRIMARY KEY (`id`))
21ENGINE = InnoDB;
22
23
24-- -----------------------------------------------------
25-- Table `body_type`
26-- -----------------------------------------------------
27CREATE TABLE IF NOT EXISTS `body_type` (
28 `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
29 `name` VARCHAR(20) NOT NULL,
30 PRIMARY KEY (`id`),
31 UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
32 UNIQUE INDEX `name_UNIQUE` (`name` ASC) )
33ENGINE = InnoDB
34DEFAULT CHARACTER SET = utf8;
35
36
37-- -----------------------------------------------------
38-- Table `club`
39-- -----------------------------------------------------
40CREATE TABLE IF NOT EXISTS `club` (
41 `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
42 `name` VARCHAR(139) NULL DEFAULT NULL,
43 `logo` VARCHAR(50) NULL DEFAULT NULL,
44 PRIMARY KEY (`id`),
45 UNIQUE INDEX `id_UNIQUE` (`id` ASC)
46ENGINE = InnoDB
47DEFAULT CHARACTER SET = utf8;
48
49
50-- -----------------------------------------------------
51-- Table `country`
52-- -----------------------------------------------------
53CREATE TABLE IF NOT EXISTS `country` (
54 `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
55 `name` VARCHAR(25) NOT NULL,
56 `flag_url` VARCHAR(40) NOT NULL,
57 PRIMARY KEY (`id`),
58 UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
59 UNIQUE INDEX `name_UNIQUE` (`name` ASC) ,
60 UNIQUE INDEX `flag_url_UNIQUE` (`flag_url` ASC) )
61ENGINE = InnoDB
62DEFAULT CHARACTER SET = utf8;
63
64
65-- -----------------------------------------------------
66-- Table `prefered_foot`
67-- -----------------------------------------------------
68CREATE TABLE IF NOT EXISTS `prefered_foot` (
69 `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
70 `name` VARCHAR(10) NOT NULL,
71 PRIMARY KEY (`id`),
72 UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
73 UNIQUE INDEX `name_UNIQUE` (`name` ASC) )
74ENGINE = InnoDB
75DEFAULT CHARACTER SET = utf8;
76
77
78-- -----------------------------------------------------
79-- Table `player`
80-- -----------------------------------------------------
81CREATE TABLE IF NOT EXISTS `player` (
82 `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
83 `name` VARCHAR(45) NOT NULL,
84 `age` INT(10) UNSIGNED NOT NULL,
85 `realFace` VARCHAR(3) NULL,
86 `photo_url` VARCHAR(50) NOT NULL,
87 `club_id` INT(10) UNSIGNED NULL DEFAULT NULL,
88 `position_id` INT UNSIGNED NULL,
89 `jerseyNumber` INT UNSIGNED NULL,
90 `country_id` INT(10) UNSIGNED NOT NULL,
91 `prefered_foot_id` INT(10) UNSIGNED NULL DEFAULT NULL,
92 `body_type_id` INT(10) UNSIGNED NULL DEFAULT NULL,
93 `work_rate_id` INT UNSIGNED NULL,
94 `special` INT NULL,
95 `internationalReputation` INT NULL,
96 `overall` INT NULL,
97 `potential` INT NULL,
98 PRIMARY KEY (`id`),
99 UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
100 INDEX `fk_player_club_idx` (`club_id` ASC) ,
101 INDEX `fk_player_country1_idx` (`country_id` ASC) ,
102 INDEX `fk_player_body_type1_idx` (`body_type_id` ASC) ,
103 INDEX `fk_player_prefered_foot1_idx` (`prefered_foot_id` ASC) ,
104 INDEX `fk_player_position1_idx` (`position_id` ASC) ,
105 INDEX `fk_player_work_rate1_idx` (`work_rate_id` ASC) ,
106 CONSTRAINT `fk_player_body_type1`
107 FOREIGN KEY (`body_type_id`)
108 REFERENCES `body_type` (`id`)
109 ON DELETE CASCADE
110 ON UPDATE CASCADE,
111 CONSTRAINT `fk_player_club`
112 FOREIGN KEY (`club_id`)
113 REFERENCES `club` (`id`)
114 ON DELETE CASCADE
115 ON UPDATE CASCADE,
116 CONSTRAINT `fk_player_country1`
117 FOREIGN KEY (`country_id`)
118 REFERENCES `country` (`id`)
119 ON DELETE CASCADE
120 ON UPDATE CASCADE,
121 CONSTRAINT `fk_player_prefered_foot1`
122 FOREIGN KEY (`prefered_foot_id`)
123 REFERENCES `prefered_foot` (`id`)
124 ON DELETE CASCADE
125 ON UPDATE CASCADE,
126 CONSTRAINT `fk_player_position1`
127 FOREIGN KEY (`position_id`)
128 REFERENCES `position` (`id`)
129 ON DELETE CASCADE
130 ON UPDATE CASCADE,
131 CONSTRAINT `fk_player_work_rate1`
132 FOREIGN KEY (`work_rate_id`)
133 REFERENCES `work_rate` (`id`)
134 ON DELETE CASCADE
135 ON UPDATE CASCADE)
136ENGINE = InnoDB
137DEFAULT CHARACTER SET = utf8;
138
139
140-- -----------------------------------------------------
141-- Table `player_contract`
142-- -----------------------------------------------------
143CREATE TABLE IF NOT EXISTS `player_contract` (
144 `player_id` INT(10) UNSIGNED NOT NULL,
145 `joined_date` INT(11) NULL DEFAULT NULL,
146 `value` INT(10) NULL,
147 `wage` INT(10) NULL DEFAULT NULL,
148 `contract_valid_untill` VARCHAR(10) NULL DEFAULT NULL,
149 `release_clause` INT(10) NULL DEFAULT NULL,
150 `loaned_from` INT(10) UNSIGNED NULL,
151 PRIMARY KEY (`player_id`),
152 INDEX `fk_player_contract_club1_idx` (`loaned_from` ASC) ,
153 CONSTRAINT `fk_player_contract_player1`
154 FOREIGN KEY (`player_id`)
155 REFERENCES `player` (`id`)
156 ON DELETE CASCADE
157 ON UPDATE CASCADE,
158 CONSTRAINT `fk_player_contract_club1`
159 FOREIGN KEY (`loaned_from`)
160 REFERENCES `club` (`id`)
161 ON DELETE CASCADE
162 ON UPDATE CASCADE)
163ENGINE = InnoDB
164DEFAULT CHARACTER SET = utf8;
165
166
167-- -----------------------------------------------------
168-- Table `player_stats`
169-- -----------------------------------------------------
170CREATE TABLE IF NOT EXISTS `player_stats` (
171 `player_id` INT(10) UNSIGNED NOT NULL,
172 `height` VARCHAR(4) NULL,
173 `weight` INT(3) NULL,
174 `weakFoot` INT NULL,
175 `skillMoves` INT NULL,
176 `ls` VARCHAR(5) NULL,
177 `st` VARCHAR(5) NULL,
178 `rs` VARCHAR(5) NULL,
179 `lw` VARCHAR(5) NULL,
180 `lf` VARCHAR(5) NULL,
181 `cf` VARCHAR(5) NULL,
182 `rf` VARCHAR(5) NULL,
183 `rw` VARCHAR(5) NULL,
184 `lam` VARCHAR(5) NULL,
185 `cam` VARCHAR(5) NULL,
186 `ram` VARCHAR(5) NULL,
187 `lm` VARCHAR(5) NULL,
188 `lcm` VARCHAR(5) NULL,
189 `cm` VARCHAR(5) NULL,
190 `rcm` VARCHAR(5) NULL,
191 `rm` VARCHAR(5) NULL,
192 `lwb` VARCHAR(5) NULL,
193 `ldm` VARCHAR(5) NULL,
194 `cdm` VARCHAR(5) NULL,
195 `rdm` VARCHAR(5) NULL,
196 `rwb` VARCHAR(5) NULL,
197 `lb` VARCHAR(5) NULL,
198 `lcb` VARCHAR(5) NULL,
199 `cb` VARCHAR(5) NULL,
200 `rcb` VARCHAR(5) NULL,
201 `rb` VARCHAR(5) NULL,
202 `crossing` INT(3) NULL,
203 `finishing` INT(3) NULL,
204 `headingAccuracy` INT(3) NULL,
205 `shortPassing` INT(3) NULL,
206 `volleys` INT(3) NULL,
207 `dribbling` INT(3) NULL,
208 `curve` INT(3) NULL,
209 `fKAccuracy` INT(3) NULL,
210 `longPassing` INT(3) NULL,
211 `ballControl` INT(3) NULL,
212 `acceleration` INT(3) NULL,
213 `sprintSpeed` INT(3) NULL,
214 `agility` INT(3) NULL,
215 `reactions` INT(3) NULL,
216 `balance` INT(3) NULL,
217 `shotPower` INT(3) NULL,
218 `jumping` INT(3) NULL,
219 `stamina` INT(3) NULL,
220 `strenght` INT(3) NULL,
221 `longShots` INT(3) NULL,
222 `aggression` INT(3) NULL,
223 `interception` INT(3) NULL,
224 `positioning` INT(3) NULL,
225 `vision` INT(3) NULL,
226 `penalties` INT(3) NULL,
227 `composure` INT(3) NULL,
228 `marking` INT(3) NULL,
229 `standingTackle` INT(3) NULL,
230 `slidingTackle` INT(3) NULL,
231 `GKDiving` INT(3) NULL,
232 `GKHandling` INT(3) NULL,
233 `GKKicking` INT(3) NULL,
234 `GKPositioning` INT(3) NULL,
235 `GKReflexes` INT(3) NULL,
236 PRIMARY KEY (`player_id`),
237 CONSTRAINT `fk_player_stats_player1`
238 FOREIGN KEY (`player_id`)
239 REFERENCES `player` (`id`)
240 ON DELETE CASCADE
241 ON UPDATE CASCADE)
242ENGINE = InnoDB
243DEFAULT CHARACTER SET = utf8;