Креирање на табели

---

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)

);

---

Полнење на табели со информации (пример скрипти)

---

INSERT INTO users (first_name, last_name, phone_number, email, password, role)

SELECT 'First' '

' (i) + 1,

'Last'

(i) + 1,

'123-456-7890',

'user' 'password'

(i) + 1	'@…',
(i) + 1,

CASE

WHEN i <= 2 THEN 'ADMIN'
ELSE 'USER'
END

FROM generate_series(10003, 12500) AS i;