schema.sql

CREATE DATABASE Yeti_Kamaev
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_0900_ai_ci;
USE Yeti_Kamaev;

CREATE TABLE users (
                     id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
                     registry_date DATETIME NOT NULL,
                     contact VARCHAR(255) UNIQUE,
                     nickname VARCHAR(255) NOT NULL,
                     e_mail VARCHAR(255) NOT NULL UNIQUE,
                     password VARCHAR(255) NOT NULL

);

CREATE TABLE categories (
                          id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
                          title VARCHAR(255) NOT NULL UNIQUE,
                          uniq_code VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE lots (
                    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
                    id_category BIGINT NOT NULL,
                    id_user_author BIGINT NOT NULL,
                    id_user_win BIGINT,
                    create_date DATETIME NOT NULL,
                    good_name VARCHAR(255) NOT NULL UNIQUE,
                    good_discription VARCHAR(4096) NOT NULL,
                    url_img VARCHAR(255) NOT NULL UNIQUE,
                    close_date DATETIME NOT NULL,
                    price DECIMAL NOT NULL,
                    step_price DECIMAL NOT NULL,
                    FOREIGN KEY(id_category) REFERENCES categories(id),
                    FOREIGN KEY(id_user_author) REFERENCES users(id),
                    FOREIGN KEY(id_user_win) REFERENCES users(id)
);

CREATE TABLE bets (
                    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
                    id_user_made BIGINT NOT NULL,
                    id_lot BIGINT NOT NULL,
                    date_time DATETIME NOT NULL,
                    cost DECIMAL NOT NULL,
                    FOREIGN KEY(id_lot) REFERENCES lots(id),
                    FOREIGN KEY(id_user_made) REFERENCES users(id)
);