Version 4 (modified by 7 months ago) ( diff ) | ,
---|
Креирање на табели
create table users
(
id bigserial primary key,
first_name varchar(255),
last_name varchar(255),
phone_number varchar(255),
email varchar(255) unique,
password varchar(255),
role varchar(255)
);
create table address
(
id bigserial primary key,
address varchar(255),
user_id bigint,
foreign key (user_id) references users (id)
on delete cascade
on update cascade
);
create table category (
id bigserial primary key, name varchar(255), category_id bigint references category (id)
on delete cascade on update cascade
);
create table supplier (
id bigserial primary key, name varchar(255), location varchar(255), phone_number varchar(255)
);
create table product (
id bigserial primary key, name varchar(255), description varchar(255), image bytea, quantity integer, category_id bigint references category (id)
on delete set null on update cascade,
supplier_id bigint references supplier (id)
on delete set null on update cascade
);
create table product_price (
product_id bigint, price integer, start_date date, end_date date, foreign key (product_id) references product (id)
on delete cascade on update cascade,
primary key (product_id, price, start_date), check ( price > 0 ), check ( end_date >= start_date )
);
create table property (
id bigserial primary key, name varchar(255)
);
create table product_property (
product_id bigint, property_id bigint, value varchar(255), foreign key (product_id) references product (id)
on delete cascade on update cascade,
foreign key (property_id) references property (id)
on delete cascade on update cascade,
primary key (product_id, property_id)
);
create table orders (
id bigserial primary key, status varchar(255), date date, price integer, user_id bigint, foreign key (user_id) references users (id)
on delete set null on update cascade
);
create table order_details (
order_id bigint, product_id bigint, quantity integer, foreign key (order_id) references orders (id)
on delete cascade on update cascade,
foreign key (product_id) references product (id)
on delete cascade on update cascade,
primary key (order_id, product_id)
);