1 | create table product_category (
|
---|
2 | id serial,
|
---|
3 | category_name varchar(50) not null,
|
---|
4 | constraint pk_pc primary key(id)
|
---|
5 | );
|
---|
6 |
|
---|
7 | create table product (
|
---|
8 | id serial,
|
---|
9 | sku varchar(255) default null,
|
---|
10 | description varchar(1000) not null,
|
---|
11 | units_in_stock integer not null,
|
---|
12 | unit_price decimal(13,2) not null,
|
---|
13 | image_url varchar(255) not null,
|
---|
14 | name varchar(100) not null,
|
---|
15 | active boolean default true,
|
---|
16 | date_created timestamp default null,
|
---|
17 | last_updated timestamp default null,
|
---|
18 | category_id integer not null,
|
---|
19 | constraint product_id primary key(id),
|
---|
20 | constraint fk_category foreign key(category_id) references product_category(id)
|
---|
21 | );
|
---|
22 |
|
---|
23 |
|
---|
24 | create table address(
|
---|
25 | id serial,
|
---|
26 | street varchar(255) not null,
|
---|
27 | city varchar(100) not null,
|
---|
28 | country varchar(100) not null,
|
---|
29 | zip_code varchar(50) default null,
|
---|
30 | constraint pk_address primary key(id)
|
---|
31 | );
|
---|
32 |
|
---|
33 | create table customer(
|
---|
34 | id serial,
|
---|
35 | first_name varchar(100) not null,
|
---|
36 | last_name varchar(100) not null,
|
---|
37 | email varchar(255) unique not null,
|
---|
38 | constraint pk_customer primary key(id)
|
---|
39 | );
|
---|
40 |
|
---|
41 | -- áèäå¼è order å ðåçåðâèðàí çáîð âî postgresql òàáåëàòà ¼à èìàíóâàâ êàêî orders
|
---|
42 | create table orders(
|
---|
43 | id bigserial,
|
---|
44 | order_tracking_number varchar(255) default null,
|
---|
45 | total_price decimal(19,2) not null,
|
---|
46 | total_quantity int not null,
|
---|
47 | date_created timestamp default null,
|
---|
48 | customer_id int not null,
|
---|
49 | billing_address_id int not null,
|
---|
50 | shipping_address_id int not null,
|
---|
51 | constraint pk_order primary key(id),
|
---|
52 | constraint fk_customer_id foreign key(customer_id) references customer(id),
|
---|
53 | constraint fk_billing_address_id foreign key(billing_address_id) references address(id),
|
---|
54 | constraint fk_shipping_address_id foreign key(shipping_address_id) references address(id)
|
---|
55 | );
|
---|
56 |
|
---|
57 | create table order_item (
|
---|
58 | o_id serial,
|
---|
59 | order_id bigserial,
|
---|
60 | image_url varchar(255) default null,
|
---|
61 | quantity int not null,
|
---|
62 | unit_price decimal(19,2) not null,
|
---|
63 | product_id int not null,
|
---|
64 | constraint pk_order_item primary key(o_id, order_id),
|
---|
65 | constraint fk_order_id foreign key(order_id) references orders(id),
|
---|
66 | constraint fk_product_id foreign key(product_id) references product(id)
|
---|
67 | );
|
---|
68 |
|
---|
69 | create table order_status(
|
---|
70 | os_id serial,
|
---|
71 | order_id bigserial,
|
---|
72 | status varchar(128) default 'active',
|
---|
73 | date_updated timestamp default null,
|
---|
74 | constraint pk_order_status primary key(os_id, order_id),
|
---|
75 | constraint fk_order foreign key(order_id) references orders(id)
|
---|
76 | );
|
---|
77 |
|
---|
78 |
|
---|
79 |
|
---|
80 |
|
---|
81 |
|
---|