# Создаём таблицу "магазин"
CREATE TABLE shop
(
id INTEGER(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(100),
inn INTEGER(11) UNSIGNED UNIQUE
);
# Создаём таблицу "клиент"
CREATE TABLE client
(
c_id INTEGER(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
sh_id INTEGER(11) UNSIGNED NOT NULL,
first_name VARCHAR(50),
last_name VARCHAR(70) NOT NULL,
middle_name VARCHAR(50),
login VARCHAR(50) NOT NULL,
password VARCHAR(150) NOT NULL,
status VARCHAR(10),
FOREIGN KEY (sh_id)
REFERENCES shop(id)
ON DELETE CASCADE
);
# Создаём таблицу "производитель"
CREATE TABLE manufacturer
(
id INTEGER(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(100) NOT NULL,
country VARCHAR(100)
);
# Создаём таблицу "заказ"
CREATE TABLE user_order
(
id INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
sh_id INTEGER(11) UNSIGNED NOT NULL,
c_id INTEGER(11) UNSIGNED,
items TEXT,
total_sum DECIMAL(12, 2) UNSIGNED NOT NULL,
total_quantity INTEGER(11) UNSIGNED NOT NULL,
DISCOUNT DECIMAL(10, 2) UNSIGNED,
FOREIGN KEY (sh_id)
REFERENCES shop(id)
ON DELETE CASCADE,
FOREIGN KEY (c_id)
REFERENCES client(c_id)
ON DELETE SET NULL
);
# Создаём таблицу "телефон"
CREATE TABLE phone
(
id INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
manuf_id INTEGER(11) UNSIGNED NOT NULL,
price DECIMAL(10,2) UNSIGNED,
model VARCHAR(70) NOT NULL UNIQUE,
features TEXT,
FOREIGN KEY (manuf_id)
REFERENCES manufacturer(id)
);
# Создаём таблицу "аксессуар"
CREATE TABLE accessory
(
id INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
p_id INTEGER(11) UNSIGNED NOT NULL,
title VARCHAR(50) NOT NULL,
price DECIMAL(10,2) UNSIGNED,
FOREIGN KEY (p_id)
REFERENCES phone(id)
);
# Создаём таблицу отношений "магазин-телефон"
CREATE TABLE phone_shop_relations
(
p_id INTEGER(11) UNSIGNED NOT NULL,
sh_id INTEGER(11) UNSIGNED NOT NULL,
FOREIGN KEY (p_id)
REFERENCES phone(id),
FOREIGN KEY (sh_id)
REFERENCES shop(id),
UNIQUE (p_id, sh_id)
);