2S5u9xnG

1DROP DATABASE IF EXISTS football;
2
3CREATE DATABASE fooball;
4
5USE football;
6
7# TODO do we want created_on and updated_on fields for every table?  or just games?
8# ActiveRecord will auto-populate.
9
10CREATE TABLE seasons (
11    id INT NOT NULL UNIQUE PRIMARY KEY,
12    name NVARCHAR(255) NOT NULL, # TODO this is derivable from start and end date
13    start_date DATETIME NOT NULL,
14    end_date DATETIME NOT NULL
15) Engine = InnoDB DEFAULT CHARSET = UTF8;
16
17CREATE TABLE teams (
18    id INT NOT NULL UNIQUE PRIMARY KEY,
19    short_name NVARCHAR(255),
20    name NVARCHAR(255) NOT NULL,
21    nicknames NVARCHAR(255),
22    year_formed INT
23    /* deputychariman, secretary, directors, 
24       commericalmanagement, address, postcode, telephone */
25) Engine = InnoDB DEFAULT CHARSET = UTF8;
26
27CREATE TABLE competitions (
28    id INT NOT NULL UNIQUE PRIMARY KEY,
29    name NVARCHAR(255)
30) Engine = InnoDB DEFAULT CHARSET = UTF8;
31
32CREATE TABLE grounds (
33    id INT NOT NULL UNIQUE PRIMARY KEY,
34    name NVARCHAR(255) NOT NULL,
35    address NVARCHAR(1023),
36    capacity INT,
37    length INT,
38    width INT
39) Engine = InnoDB DEFAULT CHARSET = UTF8;
40
41CREATE TABLE refs (
42    id INT NOT NULL UNIQUE PRIMARY KEY,
43    name NVARCHAR(255) NOT NULL,
44    home NVARCHAR(255)
45) Engine = InnoDB DEFAULT CHARSET = UTF8;
46
47# TODO perhaps put season_id in here or perhaps create a rounds_seasons table?
48CREATE TABLE rounds (
49    id INT NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
50    name NVARCHAR(255) NOT NULL,
51    competition_id INT NOT NULL,
52    FOREIGN KEY (competition_id) REFERENCES competitions (id)
53) Engine = InnoDB DEFAULT CHARSET = UTF8;
54
55CREATE TABLE games (
56    id INT NOT NULL UNIQUE PRIMARY KEY,
57    home_id INT NOT NULL,
58    away_id INT NOT NULL,
59    competition_id INT,
60    ground_id INT,
61    ref_id INT,
62    round_id INT,
63    season_id INT NOT NULL, 
64    attendance INT,
65    kick_off DATETIME,
66    extra_time BIT,
67    home_pens INT,
68    away_pens INT,
69    FOREIGN KEY (home_id) REFERENCES teams (id),
70    FOREIGN KEY (away_id) REFERENCES teams (id),
71    FOREIGN KEY (competition_id) REFERENCES competitions (id),
72    FOREIGN KEY (ground_id) REFERENCES grounds (id),
73    FOREIGN KEY (ref_id) REFERENCES refs (id),
74    FOREIGN KEY (round_id) REFERENCES rounds (id)
75) Engine = InnoDB DEFAULT CHARSET = UTF8;
76
77CREATE TABLE players (
78    id INT NOT NULL UNIQUE PRIMARY KEY,
79    name NVARCHAR(255) NOT NULL,
80    realname NVARCHAR(255),
81    date_of_birth DATETIME,
82    place_of_birth NVARCHAR(255),
83    /* ??? */
84    height INT,
85    weight INT,
86    nationality NVARCHAR(255),
87    position ENUM('G', 'D', 'M', 'F')
88) Engine = InnoDB DEFAULT CHARSET = UTF8;
89
90/*
91The inclusion of team_id in apps, goals and bookings is dubiously
92normal form.  but because a player can play for his country and his
93club I think it is justified.  
94*/
95
96CREATE TABLE appearances (
97    id INT NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
98    game_id INT NOT NULL,
99    player_id INT NOT NULL,
100    team_id INT NOT NULL,
101    # TODO these need better names but everything obvious is a SQL keyword!
102    came_on INT,
103    came_off INT, 
104    FOREIGN KEY (game_id) REFERENCES games (id),
105    FOREIGN KEY (player_id) REFERENCES players (id),
106    FOREIGN KEY (team_id) REFERENCES teams (id)
107) Engine = InnoDB DEFAULT CHARSET = UTF8;
108
109CREATE TABLE goals (
110    id INT NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
111    game_id INT NOT NULL,
112    player_id INT,
113    team_id INT NOT NULL, 
114    minute INT,
115    penalty BIT,
116    own_goal BIT,
117    first_half BIT,
118    FOREIGN KEY (game_id) REFERENCES games (id),
119    FOREIGN KEY (player_id) REFERENCES players (id),
120    FOREIGN KEY (team_id) REFERENCES teams (id)
121) Engine = InnoDB DEFAULT CHARSET = UTF8;
122
123CREATE TABLE bookings (
124    id INT NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
125    game_id INT NOT NULL,
126    player_id INT NOT NULL,
127    team_id INT NOT NULL,
128    minute INT,
129    color ENUM('yellow', 'red') NOT NULL,
130    FOREIGN KEY (game_id) REFERENCES games (id),
131    FOREIGN KEY (player_id) REFERENCES players (id),
132    FOREIGN KEY (team_id) REFERENCES teams (id)
133) Engine = InnoDB DEFAULT CHARSET = UTF8;
134
135CREATE TABLE managers (
136    id INT NOT NULL UNIQUE PRIMARY KEY,
137    name NVARCHAR(255) NOT NULL
138) Engine = InnoDB DEFAULT CHARSET = UTF8;
139
140CREATE TABLE tenures (
141    id INT NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
142    manager_id INT NOT NULL,
143    team_id INT NOT NULL,
144    start_date DATETIME,
145    end_date DATETIME
146    # XXX FOREIGN KEY (manager_id) REFERENCES managers (id),
147    # FOREIGN KEY (team_id) REFERENCES teams (id)
148) Engine = InnoDB DEFAULT CHARSET = UTF8;
149
150CREATE TABLE tenancies (
151    id INT NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
152    ground_id INT NOT NULL,
153    team_id INT NOT NULL,
154    start_date DATETIME NOT NULL,
155    end_date DATETIME,
156    FOREIGN KEY (ground_id) REFERENCES grounds (id),
157    FOREIGN KEY (team_id) REFERENCES teams (id)
158) Engine = InnoDB DEFAULT CHARSET = UTF8;
159
160CREATE TABLE transfers (
161    id INT NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
162    player_id INT NOT NULL,
163    team_id INT NOT NULL,
164    from_date DATETIME NOT NULL,
165    to_date DATETIME,
166    fee INT,
167    agreement NVARCHAR(255),
168    FOREIGN KEY (player_id) REFERENCES players (id),
169    FOREIGN KEY (team_id) REFERENCES teams (id)
170) Engine = InnoDB DEFAULT CHARSET = UTF8;
171
172-- TODO will be faster to change nvarchars to ints here
173CREATE TABLE notices (
174    id INT NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
175    game_id INT NOT NULL,
176    notice NVARCHAR(255) NOT NULL,
177    FOREIGN KEY (game_id) REFERENCES games (id)
178) Engine = InnoDB DEFAULT CHARSET = UTF8;