Database creation script

createdb.sql

USE TRADER;

DROP TABLE IF EXISTS SALES;
DROP TABLE IF EXISTS PRODUCTS;

CREATE TABLE PRODUCTS (
	ID BIGINT NOT NULL AUTO_INCREMENT,
	NAME VARCHAR(255) NOT NULL,
	PRIMARY KEY (ID)
) ENGINE = INNODB;



CREATE TABLE SALES (
	ID BIGINT NOT NULL AUTO_INCREMENT,
	BUYER_NAME VARCHAR(255) NOT NULL,
	SELLER_NAME VARCHAR(255) NOT NULL,
	PRODUCT_ID BIGINT NOT NULL,
	PRICE DECIMAL(7,2) NOT NULL,
	QUANTITY INT NOT NULL,
	SALE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (ID),
	FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCTS(ID)
) ENGINE = INNODB;

CREATE INDEX IDX_SALES_PRODUCT_ID ON SALES(PRODUCT_ID);

ALTER TABLE SALES ADD COLUMN SO_ID BIGINT;
ALTER TABLE SALES ADD COLUMN PO_ID BIGINT;

CREATE INDEX IDX_SALES_PO_ID ON SALES(PO_ID);
CREATE INDEX IDX_SALES_SO_ID ON SALES(SO_ID);