database.txt

-- public.account definition

-- Drop table

-- DROP TABLE public.account;

CREATE TABLE public.account (
	account_id int4 NOT NULL,
	firstname varchar NULL,
	lastname varchar NULL,
	username varchar NOT NULL,
	"password" varchar NOT NULL,
	email varchar NULL,
	phone varchar NULL,
	created_in date NULL,
	updated_in date NULL,
	CONSTRAINT account_pk PRIMARY KEY (account_id)
);


-- public.brand definition

-- Drop table

-- DROP TABLE public.brand;

CREATE TABLE public.brand (
	brand_id int4 NOT NULL,
	bname varchar NOT NULL,
	created_in date NULL,
	updated_in date NULL,
	CONSTRAINT brand_pk PRIMARY KEY (brand_id)
);


-- public.category definition

-- Drop table

-- DROP TABLE public.category;

CREATE TABLE public.category (
	category_id int4 NOT NULL,
	cname varchar NOT NULL,
	description varchar NULL,
	created_in date NULL,
	updated_in date NULL,
	CONSTRAINT category_pk PRIMARY KEY (category_id)
);


-- public.erole definition

-- Drop table

-- DROP TABLE public.erole;

CREATE TABLE public.erole (

);


-- public.image definition

-- Drop table

-- DROP TABLE public.image;

CREATE TABLE public.image (
	image_id int4 NOT NULL,
	"data" bytea NOT NULL,
	content_type varchar NULL,
	"size" int4 NULL,
	CONSTRAINT image_pk PRIMARY KEY (image_id)
);


-- public.paying_type definition

-- Drop table

-- DROP TABLE public.paying_type;

CREATE TABLE public.paying_type (

);


-- public."role" definition

-- Drop table

-- DROP TABLE public."role";

CREATE TABLE public."role" (
	role_id int4 NOT NULL,
	rname varchar NOT NULL,
	CONSTRAINT role_pk PRIMARY KEY (role_id)
);


-- public.shipping_type definition

-- Drop table

-- DROP TABLE public.shipping_type;

CREATE TABLE public.shipping_type (

);


-- public.account_role definition

-- Drop table

-- DROP TABLE public.account_role;

CREATE TABLE public.account_role (
	account_id int4 NOT NULL,
	role_id int4 NOT NULL,
	CONSTRAINT account_role_pk PRIMARY KEY (role_id, account_id),
	CONSTRAINT account_role_fk FOREIGN KEY (account_id) REFERENCES public.account(account_id),
	CONSTRAINT account_role_fk_1 FOREIGN KEY (role_id) REFERENCES public."role"(role_id)
);


-- public.cart definition

-- Drop table

-- DROP TABLE public.cart;

CREATE TABLE public.cart (
	cart_id int4 NOT NULL,
	account_id int4 NOT NULL,
	"valid" bool NULL,
	CONSTRAINT carts_pk PRIMARY KEY (cart_id),
	CONSTRAINT cart_fk FOREIGN KEY (account_id) REFERENCES public.account(account_id)
);


-- public."order" definition

-- Drop table

-- DROP TABLE public."order";

CREATE TABLE public."order" (
	order_id int4 NOT NULL,
	cart_id int4 NOT NULL,
	name varchar(255) NOT NULL,
	phone varchar(11) NOT NULL,
	note varchar(500) NULL,
	shipping_type varchar(255) NOT NULL,
	paying_type varchar(255) NOT NULL,
	cancel_reason varchar(500) NULL,
	shipping_fee int8 NOT NULL,
	address_city varchar(255) NOT NULL,
	address_province varchar(255) NOT NULL,
	address_ward varchar(255) NOT NULL,
	address varchar(255) NOT NULL,
	total_price int8 NOT NULL,
	CONSTRAINT orders_pk PRIMARY KEY (order_id),
	CONSTRAINT orders_fk FOREIGN KEY (cart_id) REFERENCES public.cart(cart_id)
);


-- public.product definition

-- Drop table

-- DROP TABLE public.product;

CREATE TABLE public.product (
	product_id int4 NOT NULL,
	pname varchar(255) NOT NULL,
	category_id int4 NOT NULL,
	price int8 NULL,
	rating_star float4 NULL,
	image_id int4 NULL,
	created_in date NULL,
	updated_in date NULL,
	description varchar NULL,
	brand_id int4 NOT NULL,
	CONSTRAINT products_pk PRIMARY KEY (product_id),
	CONSTRAINT product_fk FOREIGN KEY (brand_id) REFERENCES public.brand(brand_id),
	CONSTRAINT product_fk_1 FOREIGN KEY (category_id) REFERENCES public.category(category_id),
	CONSTRAINT product_fk_2 FOREIGN KEY (image_id) REFERENCES public.image(image_id)
);


-- public.rating definition

-- Drop table

-- DROP TABLE public.rating;

CREATE TABLE public.rating (
	rating_id int4 NOT NULL,
	product_id int4 NOT NULL,
	account_id int4 NOT NULL,
	"date" date NULL,
	star int4 NOT NULL,
	"comment" varchar NULL,
	CONSTRAINT rating_pk PRIMARY KEY (rating_id),
	CONSTRAINT rating_fk FOREIGN KEY (account_id) REFERENCES public.account(account_id),
	CONSTRAINT rating_fk_1 FOREIGN KEY (product_id) REFERENCES public.product(product_id)
);


-- public.cart_item definition

-- Drop table

-- DROP TABLE public.cart_item;

CREATE TABLE public.cart_item (
	product_id int4 NOT NULL,
	cart_id int4 NOT NULL,
	quantity int4 NOT NULL,
	cart_item_id int4 NOT NULL,
	CONSTRAINT cart_items_pk PRIMARY KEY (cart_item_id),
	CONSTRAINT cart_item_fk FOREIGN KEY (cart_id) REFERENCES public.cart(cart_id),
	CONSTRAINT cart_item_fk_1 FOREIGN KEY (product_id) REFERENCES public.product(product_id)
);