66 lines
2 KiB
SQL
66 lines
2 KiB
SQL
-- Таблица Categories
|
|
CREATE TABLE Categories (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name VARCHAR(100) NOT NULL,
|
|
description TEXT
|
|
);
|
|
|
|
-- Таблица Inventory
|
|
CREATE TABLE Inventory (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
category_id INTEGER NOT NULL,
|
|
name VARCHAR(200) NOT NULL,
|
|
base_price DECIMAL(10,2) NOT NULL,
|
|
season_type VARCHAR(10) NOT NULL,
|
|
FOREIGN KEY (category_id) REFERENCES Categories(id) ON DELETE RESTRICT
|
|
);
|
|
|
|
-- Таблица Partners
|
|
CREATE TABLE Partners (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name VARCHAR(200) NOT NULL,
|
|
phone VARCHAR(20),
|
|
email VARCHAR(100),
|
|
address TEXT
|
|
);
|
|
|
|
-- Таблица IssueOrders
|
|
CREATE TABLE IssueOrders (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
partner_id INTEGER NOT NULL,
|
|
issue_date DATETIME NOT NULL,
|
|
notes TEXT,
|
|
FOREIGN KEY (partner_id) REFERENCES Partners(id) ON DELETE RESTRICT
|
|
);
|
|
|
|
-- Таблица IssueOrderItems
|
|
CREATE TABLE IssueOrderItems (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
issue_order_id INTEGER NOT NULL,
|
|
inventory_id INTEGER NOT NULL,
|
|
quantity INTEGER NOT NULL CHECK (quantity > 0),
|
|
FOREIGN KEY (issue_order_id) REFERENCES IssueOrders(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (inventory_id) REFERENCES Inventory(id) ON DELETE RESTRICT
|
|
);
|
|
|
|
-- Таблица ReturnOrders
|
|
CREATE TABLE ReturnOrders (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
issue_order_id INTEGER NOT NULL UNIQUE,
|
|
return_date DATETIME NOT NULL,
|
|
total_amount DECIMAL(10,2),
|
|
notes TEXT,
|
|
FOREIGN KEY (issue_order_id) REFERENCES IssueOrders(id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- Таблица ReturnOrderItems
|
|
CREATE TABLE ReturnOrderItems (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
return_order_id INTEGER NOT NULL,
|
|
inventory_id INTEGER NOT NULL,
|
|
quantity INTEGER NOT NULL CHECK (quantity > 0),
|
|
wear_level VARCHAR(10) NOT NULL,
|
|
calculated_price DECIMAL(10,2),
|
|
FOREIGN KEY (return_order_id) REFERENCES ReturnOrders(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (inventory_id) REFERENCES Inventory(id) ON DELETE RESTRICT
|
|
); |