Креирање на табели
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' || (i) + 1 || '@example.com',
'password' || (i) + 1,
CASE
WHEN i <= 2 THEN 'ADMIN'
ELSE 'USER'
END
FROM generate_series(1, 10000) AS i;
SELECT setseed(0.5);
DO
$$
DECLARE
i integer;
BEGIN
FOR i IN 1..(500 + floor(random() * 501))
LOOP
INSERT INTO category (name, category_id)
VALUES ('Category_' || i,
CASE
WHEN random() < 0.2 THEN NULL
ELSE (SELECT id FROM category WHERE random() < 0.4 LIMIT 1)
END);
END LOOP;
END
$$;
