neJ3D9ar

1DROP DATABASE IF EXISTS sports;
2CREATE DATABASE sports;
3USE sports;
4 
5DROP TABLE IF EXISTS users;
6CREATE TABLE users 
7(id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
8 firstname VARCHAR(30),
9 lastname VARCHAR(30),
10 phone BIGINT UNSIGNED UNIQUE,
11 email VARCHAR(100) UNIQUE,
12 INDEX users_firstname_lastname_idx(firstname, lastname) -- ????????????
13)
14 
15DROP TABLE IF EXISTS profiles;
16CREATE TABLE profiles (
17	user_id BIGINT UNSIGNED NOT NULL UNIQUE,
18    gender ENUM("Мужской", "Женский"),
19    birthday DATE,
20	profile_photo BIGINT UNSIGNED NULL,
21    created_at DATETIME DEFAULT NOW(),
22    country VARCHAR(100),
23    user_info VARCHAR(1000),
24    favorite_teams BIGINT UNSIGNED,
25    favorite_personals BIGINT UNSIGNED,
26    favorite_blogs BIGINT UNSIGNED,
27    favorite_tournaments BIGINT UNSIGNED
28 
29    -- FOREIGN KEY (favorite_clubs) REFERENCES team(id),
30    );
31 
32DROP TABLE IF EXISTS blogs;
33CREATE TABLE blogs(
34	id SERIAL,
35	name VARCHAR(100),
36	admin_user_id BIGINT UNSIGNED NOT NULL,
37 
38	INDEX blogs_name_idx(name), -- индексу можно давать свое имя (communities_name_idx)
39	FOREIGN KEY (admin_user_id) REFERENCES users(id)
40);
41 
42DROP TABLE IF EXISTS posts;
43CREATE TABLE posts (
44	id SERIAL,
45	author_user_id BIGINT UNSIGNED NOT NULL,
46    blog_id BIGINT UNSIGNED NOT NULL,
47    body TEXT,
48    created_at DATETIME DEFAULT NOW(), -- можно будет даже не упоминать это поле при вставке
49 
50    FOREIGN KEY (author_user_id) REFERENCES users(id),
51    FOREIGN KEY (blog_id) REFERENCES blogs(id)
52);
53 
54DROP TABLE IF EXISTS kindof_sports;
55CREATE TABLE kindof_sports (
56	id SERIAL,
57	name VARCHAR(50)
58);
59 
60DROP TABLE IF EXISTS tournaments;
61CREATE TABLE tournaments (
62	id SERIAL,
63	name VARCHAR(50),
64	kindof_sport BIGINT UNSIGNED NOT NULL
65 
66	FOREIGN KEY (kindof_sport) REFERENCES kindof_sports(id)
67);
68--------------------------------------------------
69DROP TABLE IF EXISTS teams;
70CREATE TABLE teams (
71	id SERIAL,
72	name VARCHAR(50),
73	tournament BIGINT UNSIGNED NOT NULL 
74 
75	FOREIGN KEY (tournament) REFERENCES tournaments(id)
76);
77
78DROP TABLE IF EXISTS mathes;
79CREATE TABLE matches (
80	id SERIAL,
81	team_1 BIGINT UNSIGNED NOT NULL,
82	team_2 BIGINT UNSIGNED NOT NULL,
83	tournament BIGINT UNSIGNED NOT NULL 
84 
85	FOREIGN KEY (tournament) REFERENCES tournaments(id),
86	FOREIGN KEY (team_1) REFERENCES teams(id),
87	FOREIGN KEY (team_2) REFERENCES teams(id)
88);
89