c2hKU8n0

1/* 1. 
2I. Виды спорта +
3II. Турниры +
4III. Клубы +
5IV. Матчи +
6V. Игроки / тренеры 
7
82. 
9VI. Пользователи +
10VII. Блоги +
11VIII. Посты +
12IX. Подкасты
13X. Видео
14XI. Лайки */
15
16DROP DATABASE IF EXISTS sports;
17CREATE DATABASE sports;
18USE sports;
19 
20DROP TABLE IF EXISTS users;
21CREATE TABLE users 
22(id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
23 firstname VARCHAR(30),
24 lastname VARCHAR(30),
25 phone BIGINT UNSIGNED UNIQUE,
26 email VARCHAR(100) UNIQUE,
27 INDEX users_firstname_lastname_idx(firstname, lastname) -- ????????????
28);
29 
30DROP TABLE IF EXISTS profiles;
31CREATE TABLE profiles (
32    user_id BIGINT UNSIGNED NOT NULL UNIQUE,
33    gender ENUM("Мужской", "Женский"),
34    birthday DATE,
35    profile_photo BIGINT UNSIGNED NULL,
36    created_at DATETIME DEFAULT NOW(),
37    country VARCHAR(100),
38    user_info VARCHAR(1000),
39    favorite_teams BIGINT UNSIGNED,
40    favorite_personals BIGINT UNSIGNED,
41    favorite_blogs BIGINT UNSIGNED,
42    favorite_tournaments BIGINT UNSIGNED
43 
44    -- FOREIGN KEY (favorite_teams) REFERENCES teams(id)
45    );
46 
47DROP TABLE IF EXISTS blogs;
48CREATE TABLE blogs(
49    id SERIAL,
50    name VARCHAR(100),
51    admin_user_id BIGINT UNSIGNED NOT NULL,
52 
53    INDEX blogs_name_idx(name), -- индексу можно давать свое имя (communities_name_idx)
54    FOREIGN KEY (admin_user_id) REFERENCES users(id)
55);
56 
57DROP TABLE IF EXISTS posts;
58CREATE TABLE posts (
59    id SERIAL,
60    author_user_id BIGINT UNSIGNED NOT NULL,
61    blog_id BIGINT UNSIGNED NOT NULL,
62    body TEXT,
63    created_at DATETIME DEFAULT NOW(), -- можно будет даже не упоминать это поле при вставке
64 
65    FOREIGN KEY (author_user_id) REFERENCES users(id),
66    FOREIGN KEY (blog_id) REFERENCES blogs(id)
67);
68 
69DROP TABLE IF EXISTS kindof_sports;
70CREATE TABLE kindof_sports (
71    id SERIAL,
72    name VARCHAR(50)
73);
74 
75DROP TABLE IF EXISTS tournaments;
76CREATE TABLE tournaments (
77    id SERIAL,
78    name VARCHAR(50),
79    kindof_sport BIGINT UNSIGNED NOT NULL,
80 
81    FOREIGN KEY (kindof_sport) REFERENCES kindof_sports(id)
82);
83-- ------------------------------------------------
84DROP TABLE IF EXISTS teams;
85CREATE TABLE teams (
86    id SERIAL,
87    name VARCHAR(50),
88    tournament BIGINT UNSIGNED NOT NULL, 
89 
90    FOREIGN KEY (tournament) REFERENCES tournaments(id)
91);
92 
93ALTER TABLE profiles ADD 
94FOREIGN KEY (favorite_teams) REFERENCES teams(id);
95
96DROP TABLE IF EXISTS mathes;
97CREATE TABLE matches (
98    id SERIAL,
99    team_1 BIGINT UNSIGNED NOT NULL,
100    team_2 BIGINT UNSIGNED NOT NULL,
101    tournament BIGINT UNSIGNED NOT NULL, 
102 
103    FOREIGN KEY (tournament) REFERENCES tournaments(id),
104    FOREIGN KEY (team_1) REFERENCES teams(id),
105    FOREIGN KEY (team_2) REFERENCES teams(id)
106);
107
108DROP TABLE IF EXISTS persons;
109CREATE TABLE persons (
110	id SERIAL,
111	firstname VARCHAR(30),
112	lastname VARCHAR(30),
113	gender ENUM("Мужской", "Женский"),
114    birthday DATE,
115    weight TINYINT UNSIGNED,
116    height TINYINT UNSIGNED,
117    profile_photo BIGINT UNSIGNED NOT NULL UNIQUE,
118    stats BIGINT UNSIGNED NOT NULL,
119    team BIGINT UNSIGNED NOT NULL,
120    -- career ????
121    
122    -- FOREIGN KEY (profile_photo) REFERENCES photos(id),
123    -- FOREIGN KEY (stat) REFERENCES stats(id),
124    FOREIGN KEY (team) REFERENCES teams(id)
125	);