gxFcg5Xi

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_persons BIGINT UNSIGNED,
26    favorite_blogs BIGINT UNSIGNED,
27    favorite_tournaments BIGINT UNSIGNED
28    );
29 
30DROP TABLE IF EXISTS blogs;
31CREATE TABLE blogs(
32    id SERIAL,
33    name VARCHAR(100),
34    admin_user_id BIGINT UNSIGNED NOT NULL,
35 
36    INDEX blogs_name_idx(name),
37    FOREIGN KEY (admin_user_id) REFERENCES users(id)
38);
39 
40DROP TABLE IF EXISTS posts;
41CREATE TABLE posts (
42    id SERIAL,
43    blog_id BIGINT UNSIGNED NOT NULL,
44    body TEXT,
45    -- media       ??????????????
46    created_at DATETIME DEFAULT NOW(),
47 
48    FOREIGN KEY (blog_id) REFERENCES blogs(id)
49);
50 
51DROP TABLE IF EXISTS kindof_sports;
52CREATE TABLE kindof_sports (
53    id SERIAL,
54    name VARCHAR(50)
55);
56 
57DROP TABLE IF EXISTS tournaments;
58CREATE TABLE tournaments (
59    id SERIAL,
60    name VARCHAR(50),
61    kindof_sport BIGINT UNSIGNED NOT NULL,
62 
63    FOREIGN KEY (kindof_sport) REFERENCES kindof_sports(id)
64);
65-- ------------------------------------------------
66DROP TABLE IF EXISTS teams;
67CREATE TABLE teams (
68    id SERIAL,
69    name VARCHAR(50)
70);
71 
72ALTER TABLE profiles ADD 
73FOREIGN KEY (favorite_teams) REFERENCES teams(id);
74 
75DROP TABLE IF EXISTS mathes;
76CREATE TABLE matches (
77    id SERIAL,
78    team_1 BIGINT UNSIGNED NOT NULL,
79    team_2 BIGINT UNSIGNED NOT NULL,
80    tournament BIGINT UNSIGNED NOT NULL,
81    -- result ????????????????????????????
82 
83    FOREIGN KEY (tournament) REFERENCES tournaments(id),
84    FOREIGN KEY (team_1) REFERENCES teams(id),
85    FOREIGN KEY (team_2) REFERENCES teams(id)
86);
87 
88DROP TABLE IF EXISTS persons;
89CREATE TABLE persons (
90    id SERIAL,
91    firstname VARCHAR(30),
92    lastname VARCHAR(30),
93    gender ENUM("Мужской", "Женский"),
94    birthday DATE,
95    weight TINYINT UNSIGNED,
96    height TINYINT UNSIGNED,
97    person_photo BIGINT UNSIGNED NOT NULL UNIQUE,
98    stats BIGINT UNSIGNED NOT NULL,
99    team BIGINT UNSIGNED NOT NULL,
100    -- career ????
101    
102    -- FOREIGN KEY (person_photo) REFERENCES persons_photos(id),
103    -- FOREIGN KEY (stat) REFERENCES stats(id),
104    FOREIGN KEY (team) REFERENCES teams(id)
105    );
106    
107    /* DROP TABLE IF EXISTS persons_photo;
108CREATE TABLE persons (
109    id SERIAL,
110    посмотреть таблицы из vk    */
111    
112    /* DROP TABLE IF EXISTS profile_photo;
113CREATE TABLE persons (
114    id SERIAL,
115    посмотреть таблицы из vk    */
116    
117DROP TABLE IF EXISTS likes;
118CREATE TABLE likes(
119    id SERIAL,
120    profil_id BIGINT UNSIGNED NOT NULL,
121    post_id BIGINT UNSIGNED NOT NULL,
122    created_at DATETIME DEFAULT NOW(),
123    
124    FOREIGN KEY (profil_id) REFERENCES profiles(user_id),
125    FOREIGN KEY (post_id) REFERENCES posts(id)
126 );   
127    
128DROP TABLE IF EXISTS dislikes;
129CREATE TABLE dislikes(
130    id SERIAL,
131    profil_id BIGINT UNSIGNED NOT NULL,
132    post_id BIGINT UNSIGNED NOT NULL,
133    created_at DATETIME DEFAULT NOW(),
134    
135    FOREIGN KEY (profil_id) REFERENCES profiles(user_id),
136    FOREIGN KEY (post_id) REFERENCES posts(id)
137 );
138    
139    ALTER TABLE profiles ADD 
140    FOREIGN KEY (favorite_teams) REFERENCES teams(id);
141    
142    ALTER TABLE profiles ADD
143    FOREIGN KEY (favorite_persons) REFERENCES persons(id);
144   
145    ALTER TABLE profiles ADD
146    FOREIGN KEY (favorite_blogs) REFERENCES blogs(id);
147   
148    ALTER TABLE profiles ADD
149    FOREIGN KEY (favorite_tournaments) REFERENCES tournaments(id);