db_schema.sql - task12 - MarkZhukov/web

db_schema.sql
103 строки4 KB
markkentos
add pdf view
04 июн 2026, 01:25
04 июн 2026, 01:25bb001aa
-- Категории товаров
CREATE TABLE IF NOT EXISTS shop_category (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL
);

-- Поставщики
CREATE TABLE IF NOT EXISTS shop_supplier (
    id SERIAL PRIMARY KEY,
    company_name VARCHAR(255) NOT NULL,
    contact_person VARCHAR(255) DEFAULT '',
    phone VARCHAR(30) DEFAULT '',
    email VARCHAR(254) DEFAULT '',
    website VARCHAR(500) DEFAULT ''
);

-- Товары
CREATE TABLE IF NOT EXISTS shop_product (
    id SERIAL PRIMARY KEY,
    category_id INT NOT NULL REFERENCES shop_category(id) ON DELETE RESTRICT,
    supplier_id INT NOT NULL REFERENCES shop_supplier(id) ON DELETE RESTRICT,
    name VARCHAR(255) NOT NULL,
    description TEXT DEFAULT '',
    sku VARCHAR(50) UNIQUE NOT NULL,
    price NUMERIC(10,2) NOT NULL,
    cost_price NUMERIC(10,2) NOT NULL,
    quantity_received INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    image VARCHAR(100) DEFAULT NULL,
    specification VARCHAR(100) DEFAULT NULL
);

-- Остатки на складе
CREATE TABLE IF NOT EXISTS shop_stock (
    id SERIAL PRIMARY KEY,
    product_id INT UNIQUE NOT NULL REFERENCES shop_product(id) ON DELETE CASCADE,
    quantity_in_stock INT DEFAULT 0,
    reserved_quantity INT DEFAULT 0,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    received_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Клиенты
CREATE TABLE IF NOT EXISTS shop_customer (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(254) UNIQUE NOT NULL,
    phone VARCHAR(30) DEFAULT '',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Заказы
CREATE TABLE IF NOT EXISTS shop_order (
    id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES shop_customer(id) ON DELETE RESTRICT,
    order_date TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(50) NOT NULL,
    total_amount NUMERIC(12,2) NOT NULL,
    discount_amount NUMERIC(12,2) NOT NULL,
    final_amount NUMERIC(12,2) NOT NULL
);

-- Позиции заказов
CREATE TABLE IF NOT EXISTS shop_orderitem (
    id SERIAL PRIMARY KEY,
    order_id INT NOT NULL REFERENCES shop_order(id) ON DELETE CASCADE,
    product_id INT NOT NULL REFERENCES shop_product(id) ON DELETE RESTRICT,
    quantity INT NOT NULL,
    price NUMERIC(10,2) NOT NULL,
    line_total NUMERIC(12,2) NOT NULL
);

-- Оплаты
CREATE TABLE IF NOT EXISTS shop_payment (
    id SERIAL PRIMARY KEY,
    order_id INT NOT NULL REFERENCES shop_order(id) ON DELETE CASCADE,
    payment_date TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    amount NUMERIC(12,2) NOT NULL,
    payment_method VARCHAR(50) NOT NULL,
    payment_status VARCHAR(50) NOT NULL
);

-- Акции
CREATE TABLE IF NOT EXISTS shop_promotion (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    discount_type VARCHAR(20) NOT NULL,
    discount_value NUMERIC(10,2) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Связи товаров и акций
CREATE TABLE IF NOT EXISTS shop_productpromotion (
    id SERIAL PRIMARY KEY,
    product_id INT NOT NULL REFERENCES shop_product(id) ON DELETE CASCADE,
    promotion_id INT NOT NULL REFERENCES shop_promotion(id) ON DELETE CASCADE,
    CONSTRAINT unique_product_promotion UNIQUE (product_id, promotion_id)
);