database_schema.txt

CREATE SCHEMA skin;

CREATE TABLE Skin.User (
	ID serial PRIMARY KEY,
	username VARCHAR(12) UNIQUE NOT NULL,
	password VARCHAR(12) NOT NULL,
	email text NOT NULL
);


*** NEW Table ***
CREATE TABLE Skin.Product (
	ID serial PRIMARY KEY,
	name text NOT NULL,
	brand text,
	userID integer REFERENCES Skin.User(ID) NOT NULL,
	startDate timestamp DEFAULT now(),
	endDate timestamp,
	expiryDate timestamp
);


CREATE TABLE Skin.ProductReview (
	ID serial PRIMARY KEY,
	productID integer REFERENCES Skin.Product(ID) NOT NULL,
	userID integer REFERENCES Skin.User(ID) NOT NULL,
	rating integer CHECK (rating >= 1 AND rating <= 5) NOT NULL,
	review text
);


CREATE TABLE Skin.MyProduct (
	ID serial PRIMARY KEY,
	userID integer REFERENCES Skin.User(ID) NOT NULL,
	productID integer REFERENCES Skin.Product(ID) NOT NULL,
	startDate timestamp DEFAULT now(),
	endDate timestamp,
	expiryDate timestamp
	);


	*** NEW Table ****
	CREATE TABLE Skin.ProductUsed (
		entryID integer REFERENCES Skin.Entry(ID),
		productID integer REFERENCES Skin.Product(ID),
		rating integer CHECK (rating >= 0 AND rating <= 5),
		PRIMARY KEY (entryID, productID)
		);


CREATE TABLE Skin.Entry (
	ID serial PRIMARY KEY,
	userID integer REFERENCES Skin.User(ID) NOT NULL,
	date timestamp DEFAULT now(),
	photoLocation text,
	description text,
	rating integer CHECK (rating >= 0 AND rating <= 5)
	);


CREATE TABLE Skin.MyIssue (
	ID serial PRIMARY KEY,
	name text NOT NULL UNIQUE
	);


CREATE TABLE Skin.IssueTagged (
	entryID integer REFERENCES Skin.Entry(ID),
	myIssueID integer REFERENCES Skin.MyIssue(ID),
	PRIMARY KEY (entryID, myIssueID)
	);