zqvSKaPz

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