-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathworldcup-create-table.sql
More file actions
129 lines (113 loc) · 2.97 KB
/
worldcup-create-table.sql
File metadata and controls
129 lines (113 loc) · 2.97 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
CREATE TABLE countries (
id INT NOT NULL IDENTITY,
[name] varchar(50) NOT NULL,
slug varchar(50) NOT NULL,
[key] varchar(10) NOT NULL,
code varchar(10) NOT NULL,
created_at datetime2 NOT NULL,
updated_at datetime2 NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE events (
id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
[key] varchar(50) NOT NULL,
season_id integer NOT NULL,
[start_date] date NOT NULL,
created_at datetime2 NOT NULL,
updated_at datetime2 NOT NULL
);
CREATE TABLE events_teams (
id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
event_id integer NOT NULL,
team_id integer NOT NULL,
created_at datetime2 NOT NULL,
updated_at datetime2 NOT NULL
);
CREATE TABLE goals (
id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
person_id integer NOT NULL,
match_id integer NOT NULL,
team_id integer NOT NULL,
[minute] integer,
offset integer DEFAULT 0 NOT NULL,
score1 integer,
score2 integer,
penalty bit DEFAULT 0 NOT NULL,
owngoal bit DEFAULT 0 NOT NULL,
created_at datetime2 NOT NULL,
updated_at datetime2 NOT NULL
);
CREATE TABLE groups (
id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
event_id integer NOT NULL,
[name] varchar(20) NOT NULL,
pos integer NOT NULL,
[key] varchar(10),
created_at datetime2 NOT NULL,
updated_at datetime2 NOT NULL
);
CREATE TABLE groups_teams (
id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
group_id integer NOT NULL,
team_id integer NOT NULL,
created_at datetime2 NOT NULL,
updated_at datetime2 NOT NULL
);
CREATE TABLE matches (
id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
[key] varchar(10),
event_id integer NOT NULL,
pos integer NOT NULL,
num integer,
team1_id integer NOT NULL,
team2_id integer NOT NULL,
round_id integer,
group_id integer,
[date] date,
[time] varchar(10),
score1 integer,
score2 integer,
score1et integer,
score2et integer,
score1p integer,
score2p integer,
score1i integer,
score2i integer,
score1ii integer,
score2ii integer,
next_match_id integer,
prev_match_id integer,
winner integer,
winner90 integer,
comments varchar(100),
created_at datetime2 NOT NULL,
updated_at datetime2 NOT NULL
);
CREATE TABLE persons (
id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
[key] varchar(50) NOT NULL,
[name] varchar(100) NOT NULL,
created_at datetime2 NOT NULL,
updated_at datetime2 NOT NULL
);
CREATE TABLE rounds (
id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
event_id integer NOT NULL,
[name] varchar(50) NOT NULL,
pos integer NOT NULL,
created_at datetime2 NOT NULL,
updated_at datetime2 NOT NULL);
CREATE TABLE seasons (
id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
[key] varchar(10) NOT NULL,
[name] varchar(10) NOT NULL,
created_at datetime2 NOT NULL,
updated_at datetime2 NOT NULL);
CREATE TABLE teams (
id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
[key] varchar(10) NOT NULL,
[name] varchar(50) NOT NULL,
code varchar(10),
country_id integer NOT NULL,
created_at datetime2 NOT NULL,
updated_at datetime2 NOT NULL);