fgn75MJN

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    tournament BIGINT UNSIGNED NOT NULL, 
72 
73    FOREIGN KEY (tournament) REFERENCES tournaments(id)
74);
75 
76ALTER TABLE profiles ADD 
77FOREIGN KEY (favorite_teams) REFERENCES teams(id);
78 
79DROP TABLE IF EXISTS mathes;
80CREATE TABLE matches (
81    id SERIAL,
82    team_1 BIGINT UNSIGNED NOT NULL,
83    team_2 BIGINT UNSIGNED NOT NULL,
84    tournament BIGINT UNSIGNED NOT NULL,
85    -- result ???
86 
87    FOREIGN KEY (tournament) REFERENCES tournaments(id),
88    FOREIGN KEY (team_1) REFERENCES teams(id),
89    FOREIGN KEY (team_2) REFERENCES teams(id)
90);
91 
92DROP TABLE IF EXISTS persons;
93CREATE TABLE persons (
94    id SERIAL,
95    firstname VARCHAR(30),
96    lastname VARCHAR(30),
97    gender ENUM("Мужской", "Женский"),
98    birthday DATE,
99    weight TINYINT UNSIGNED,
100    height TINYINT UNSIGNED,
101    person_photo BIGINT UNSIGNED NOT NULL UNIQUE,
102    stats BIGINT UNSIGNED NOT NULL,
103    team BIGINT UNSIGNED NOT NULL,
104    -- career ????
105    
106    -- FOREIGN KEY (person_photo) REFERENCES persons_photos(id),
107    -- FOREIGN KEY (stat) REFERENCES stats(id),
108    FOREIGN KEY (team) REFERENCES teams(id)
109    );
110    
111    /* DROP TABLE IF EXISTS persons_photo;
112CREATE TABLE persons (
113    id SERIAL,
114    посмотреть таблицы из vk    */
115    
116    /* DROP TABLE IF EXISTS profile_photo;
117CREATE TABLE persons (
118    id SERIAL,
119    посмотреть таблицы из vk    */
120    
121    /* DROP TABLE IF EXISTS likes;
122CREATE TABLE persons (
123    id SERIAL,
124    посмотреть таблицы из vk    */
125    
126    /* DROP TABLE IF EXISTS dislikes;
127CREATE TABLE persons (
128    id SERIAL,
129    посмотреть таблицы из vk    */
130    
131    ALTER TABLE profiles ADD 
132    FOREIGN KEY (favorite_teams) REFERENCES teams(id),
133    FOREIGN KEY (favorite_persons) REFERENCES persons(id),
134    FOREIGN KEY (favorite_blogs) REFERENCES blogs(id),
135    FOREIGN KEY (favorite_tournaments) REFERENCES tournaments(id);
136    
137
138