lmt/schema.sql at main · JWhist/lmt · GitHub

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
CREATE TABLE admins (
  id SERIAL PRIMARY KEY,
  username TEXT UNIQUE NOT NULL,
  password TEXT NOT NULL
);

CREATE TABLE sports (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  admin_id INTEGER REFERENCES admins (id)
  ON DELETE CASCADE
);

CREATE TABLE leagues (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  sport_id INTEGER REFERENCES sports (id)
  ON DELETE CASCADE,
  admin_id INTEGER REFERENCES admins (id)
  ON DELETE CASCADE
);

CREATE TABLE teams (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  league_id INTEGER REFERENCES leagues (id)
  ON DELETE CASCADE,
  admin_id INTEGER REFERENCES admins (id)
  ON DELETE CASCADE
);

CREATE TABLE players (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT,
  phone TEXT,
  admin_id INTEGER REFERENCES admins (id)
  ON DELETE CASCADE
);

CREATE TABLE coaches (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT,
  phone TEXT,
  admin_id INTEGER REFERENCES admins (id)
  ON DELETE CASCADE
);

CREATE TABLE games (
  id SERIAL PRIMARY KEY,
  gameday DATE,
  venue TEXT,
  homescore TEXT,
  awayscore TEXT,
  hometeam_id INTEGER REFERENCES teams (id)
  ON DELETE CASCADE,
  awayteam_id INTEGER REFERENCES teams (id)
  ON DELETE CASCADE,
  admin_id INTEGER REFERENCES admins (id)
  ON DELETE CASCADE
);

CREATE TABLE teams_players (
  team_id INTEGER REFERENCES teams (id)
  ON DELETE CASCADE,
  player_id INTEGER REFERENCES players (id)
  ON DELETE CASCADE,
  admin_id INTEGER REFERENCES admins (id)
);

CREATE TABLE teams_coaches (
  team_id INTEGER REFERENCES teams (id)
  ON DELETE CASCADE,
  coach_id INTEGER REFERENCES coaches (id)
  ON DELETE CASCADE,
  admin_id INTEGER REFERENCES admins (id)
);