Untitled

drop table if exists stores cascade;
 CREATE TABLE stores(
	"id" INT NOT NULL PRIMARY KEY,
	storename VARCHAR(50) NOT NULL
 )
CREATE TABLE product(
	"id" INT NOT NULL PRIMARY KEY,
	productname VARCHAR(50) NOT NULL,
	productdescription VARCHAR(200) NOT NULL,
	productprice DOUBLE PRECISION  NOT NULL,
	store_id INT NOT NULL, 
	
	CONSTRAINT fk_store 
	FOREIGN KEY (store_id)
	references stores("id")
);
CREATE TABLE users(
"id" INT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
username VARCHAR(30) NOT NULL,
"password" VARCHAR(30) NOT NULL,
order_id INT,
store_id INT,
	CONSTRAINT fk_store
	FOREIGN KEY(store_id)
	  REFERENCES stores("id"),
	CONSTRAINT fk_order
	foreign key(order_id) references orders("id")
);
create table orders(
	"id" INT NOT NULL PRIMARY KEY,
	product_id INT NOT NULL,
	totalprice INT,
	
	CONSTRAINT fk_product 
	FOREIGN KEY(product_id) 
		references product("id")
)
CREATE TABLE roles (
  role_id int NOT NULL,
  name varchar(45) NOT NULL,
  PRIMARY KEY (role_id)
);

 drop table if exists privilege CASCADE;

CREATE TABLE privilege (
  privilege_id int NOT NULL,
  name varchar(45) NOT NULL,
  PRIMARY KEY (privilege_id)
);
drop table if exists roles_privileges;

CREATE TABLE roles_privileges(
	privilege_id int NOT NULL,
	role_id int NOT NULL,
	CONSTRAINT fk_privilege
	FOREIGN KEY(privilege_id) 
	  REFERENCES privilege(privilege_id),
	CONSTRAINT fk_role
	FOREIGN KEY(role_id) 
	  REFERENCES roles(role_id)
);
drop table if exists users_roles;

CREATE TABLE users_roles(
	role_id int NOT NULL,
	user_id int NOT NULL,
	CONSTRAINT fk_role
	FOREIGN KEY(role_id) 
	  REFERENCES roles(role_id),
	constraint fk_user 
	FOREIGN KEY(user_id) 
		references users(id)
)