83 lines
2.3 KiB
MySQL
83 lines
2.3 KiB
MySQL
|
SET lc_monetary TO 'ru_RU.UTF-8';
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS clients (
|
||
|
|
client_id SERIAL PRIMARY KEY,
|
||
|
|
surname VARCHAR(50) NOT NULL,
|
||
|
|
forename VARCHAR(50) NOT NULL,
|
||
|
|
patronymic VARCHAR(50),
|
||
|
|
age SMALLINT,
|
||
|
|
phone_number VARCHAR(15) NOT NULL,
|
||
|
|
email VARCHAR(50) NOT NULL
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS auth (
|
||
|
|
client_id INT PRIMARY KEY REFERENCES clients(client_id),
|
||
|
|
password VARCHAR(50) NOT NULL
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS board_games (
|
||
|
|
board_game_id SERIAL PRIMARY KEY,
|
||
|
|
name VARCHAR(100) NOT NULL,
|
||
|
|
genre VARCHAR(30),
|
||
|
|
publisher VARCHAR(50),
|
||
|
|
price MONEY NOT NULL
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS sections (
|
||
|
|
section_id SERIAL PRIMARY KEY,
|
||
|
|
name VARCHAR(20) NOT NULL
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS sections_board_games (
|
||
|
|
section_id INT REFERENCES sections(section_id),
|
||
|
|
board_game_id INT REFERENCES board_games(board_game_id)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS bags (
|
||
|
|
bag_id SERIAL PRIMARY KEY,
|
||
|
|
client_id INT REFERENCES clients(client_id)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS bags_board_games (
|
||
|
|
bag_id INT REFERENCES bags(bag_id),
|
||
|
|
board_game_id INT REFERENCES board_games(board_game_id),
|
||
|
|
count INT NOT NULL
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS discounts (
|
||
|
|
discount_id SERIAL PRIMARY KEY,
|
||
|
|
board_game_id INT REFERENCES board_games(board_game_id),
|
||
|
|
amount SMALLINT NOT NULL,
|
||
|
|
valid_until TIMESTAMP NOT NULL
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS clients_discounts (
|
||
|
|
discount INT REFERENCES discounts(discount_id),
|
||
|
|
client_id INT REFERENCES clients(client_id)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS wishlists (
|
||
|
|
wishlist_id SERIAL PRIMARY KEY,
|
||
|
|
client_id INT REFERENCES clients(client_id)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS wishlists_games (
|
||
|
|
wishlist_id INT REFERENCES wishlists(wishlist_id),
|
||
|
|
board_game_id INT REFERENCES board_games(board_game_id)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS orders (
|
||
|
|
order_id SERIAL PRIMARY KEY,
|
||
|
|
client_id INT REFERENCES clients(client_id),
|
||
|
|
pick_up VARCHAR(50) NOT NULL,
|
||
|
|
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
cost MONEY NOT NULL
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS orders_games (
|
||
|
|
order_id INT REFERENCES orders(order_id),
|
||
|
|
board_game_id INT REFERENCES board_games(board_game_id),
|
||
|
|
discount_id INT REFERENCES discounts(discount_id),
|
||
|
|
count INT NOT NULL
|
||
|
|
);
|