Untitled

--one to one => either one of the existing tables
--many to one => many side takes one's primary key
-- many to many => new relationship table




CREATE TABLE Customer(
	user_ID int PRIMARY KEY,
	phone_num int,
	e_mail varchar(55),
	name_surn varchar(55),
	reg_date date,
	birthday date,
	user_pass varchar(12),
	address varchar(255)
);

CREATE TABLE Shipper(
	ship_ID int PRIMARY KEY,
	compName varchar(255)
);

CREATE TABLE Orderings(
	order_ID int PRIMARY KEY,
	orderDate date,
	shippingDate date,
	ship_ID int NOT NULL,
	FOREIGN KEY(ship_ID) REFERENCES Shipper(ship_ID),
	user_ID int NOT NULL,
	FOREIGN KEY(user_ID) REFERENCES Customer(user_ID)
);

CREATE TABLE Cart(
	cart_ID int not null PRIMARY KEY,
	total_price int,
	num_of_items int,
	exp_Date date,
	order_ID int NOT NULL,
	FOREIGN KEY(order_ID) REFERENCES Orderings(order_ID),
	user_ID int NOT NULL,
	FOREIGN KEY(user_ID) REFERENCES Customer(user_ID)
);

CREATE TABLE Supplier(
	supp_ID int NOT NULL PRIMARY KEY,
	comp_name varchar(255),
	supp_name varchar(55),
	product_ID varchar(15) NOT NULL,
	FOREIGN KEY(product_ID) REFERENCES Product(product_ID)
);

CREATE TABLE Product(
	product_ID varchar(15) not null primary key,
	rate int,
	price int,
	star int,
	stock int,
	product_name varchar(255),
	cart_ID int NOT NULL,
	FOREIGN KEY(cart_ID) REFERENCES Cart(cart_ID)
);

CREATE TABLE Category(
	category_ID int not null,
	category_name varchar(50) not null,
	product_ID varchar(15) not null,
	primary key(product_ID, category_ID),
	foreign key(product_ID) references
	Product(product_ID)
);

CREATE TABLE Payment_Method(
	transaction_ID int not null,
	payment_type varchar(255),
	user_ID int NOT null,
	primary key (user_ID, transaction_ID),
	foreign key (user_ID) references
	Customer(user_ID)	
);

CREATE TABLE Ships(
	ship_ID int,
	order_ID int,
	PRIMARY KEY (ship_ID,order_ID),
	FOREIGN KEY (ship_ID) REFERENCES Shipper(ship_ID),
	FOREIGN KEY (order_ID) REFERENCES Orderings(order_ID)
);