ER Model

ЕР Дијаграм

Податочни побарувања

Ентититети

1. PRODUCT_CATEGORY
   1. id serial,
   2. category_name varchar
   3. constraint pk_cid primary key(id)
2. PRODUCT
   1. id serial
   2. sku varchar
   3. name varchar
   4. description varchar
   5. unit_price decimal
   6. image_url varchar
   7. units_in_stock integer
   8. date_created timestamp
   9. last_updated timestamp
   10. category_id integer
   11. constraint pk_id primary key(id)
   12. constraint fk_cid foreign key(category_id) references product_category(id)
3. CUSTOMER
   1. id serial
   2. first_name varchar
   3. last_name varchar
   4. email varchar unique
   5. constraint pk_customer primary key(id)
4. ADDRESS
   1. id serial
   2. city varchar
   3. country varchar
   4. street varchar
   5. zip_code varchar
   6. constraint pk_address primary_key(id)
5. ORDERS
   1. id bigserial
   2. order_tracking_number varchar
   3. total_price decimal
   4. total_quantity int
   5. customer_id int
   6. last_updated timestamp
   7. billing_address_id int unique
   8. shipping_address unique
   9. constraint pk_orders primary key(id)
   10. constraint fk_billing_add foreign key(billing_address_id) references address(id)
   11. constraint fk_shipping_add foreign key(shipping_address_id) references address(id)
6. ORDERED_ITEM
   1. id serial
   2. quantity int
   3. order_id bigserial
   4. product_id int
   5. constraint pk_order_item primary key(id)
   6. constraint fk_order_id foreign key(order_id) references orders(id)
   7. constraint fk_product_id foreign key(product_id) references product(id)

Следните два ентитети се ентитети за Држава и Град кои служат за популирање на полиња во апликацијата на front-end.

7. COUNTRY
   1. id serial
   2. code varchar
   3. name varchar
   4. constraint pk_country primary_key(id)
8. CITY
   1. id serial
   2. name varchar
   3. country_id int
   4. constraint pk_city primary key(id),
   5. constraint fk_country foreign key(country_id) references country(id)