Логички и физички дизајн - Креирање база податоци (со SQL DDL): DDL V2.sql

/*
drop table online_shop.products_orders;
drop table online_shop.stock;
drop table online_shop.categories;
drop table online_shop.products;
drop table online_shop.orders;
drop table online_shop.customers;
drop table online_shop.delivery;
*/

-- Табела КОРИСНИК
create table online_shop.customers
(
    id_customer serial primary key,
    email       varchar(100) not null unique,
    first_name  varchar(50) not null,
    last_name   varchar(50) not null,
    phone       varchar(15) not null,
    delivery_address varchar(200) not null,
    password    varchar(100) not null
);

-- Табела КАТЕГОРИИ
create table online_shop.categories
(
    id_category serial primary key,
    category_name varchar(50) not null
);

-- Табела ПРОИЗВОДИ
create table online_shop.products
(
    id_product  serial primary key,
    id_category integer not null,
    product_name varchar(100) not null,
    color       varchar(50) not null,
    price       numeric(10, 2) not null,
    description text,
    constraint fk_products_categories foreign key (id_category) references online_shop.categories (id_category)
        on delete cascade on update cascade
);

-- Табела ЗАЛИХА
create table online_shop.stock
(
    id_stock   serial primary key,
    id_product integer not null,
    size       varchar(5) not null check (size in ('XS', 'S', 'M', 'L', 'XL')),
    quantity   integer not null,
    constraint fk_stock_products foreign key (id_product) references online_shop.products (id_product)
        on delete cascade on update cascade
);

-- Табела НАРАЧКИ
create table online_shop.orders
(
    id_order       serial primary key,
    id_customer    integer not null,
    payment_method varchar(20) not null check (payment_method in ('Cash', 'Card')),
    status         varchar(20) not null check (status in ('Completed', 'Failed')),
    order_price    numeric(10, 2) not null,
    delivery_price numeric(10, 2) not null,
    total_price    numeric(10, 2) not null,
    invoice_code   varchar(50) not null unique,
    order_date     timestamp not null,
    constraint fk_orders_customers foreign key (id_customer) references online_shop.customers (id_customer)
        on delete cascade on update cascade
);

-- Табела НАРАЧКИ_Производи
create table online_shop.products_orders
(
    id_order   integer not null,
    id_product integer not null,
    quantity   integer not null,
    size       varchar(5) not null check (size in ('XS', 'S', 'M', 'L', 'XL')),
    id_stock   integer not null,
    primary key (id_order, id_product, id_stock),
    constraint fk_products_orders_orders foreign key (id_order) references online_shop.orders (id_order)
        on delete cascade on update cascade,
    constraint fk_products_orders_products foreign key (id_product) references online_shop.products (id_product)
        on delete cascade on update cascade,
    constraint fk_products_orders_stock foreign key (id_stock) references online_shop.stock (id_stock)
        on delete cascade on update cascade
);

-- Табела ИСПОРАКА
create table online_shop.delivery
(
    id_delivery serial primary key,
    id_order    integer not null,
    delivery_address varchar(200) not null,
    status         varchar(20) not null check (status in ('Pending', 'Shipped', 'Delivered')),
    constraint fk_delivery_orders foreign key (id_order) references online_shop.orders (id_order)
        on delete cascade on update cascade
);