db.sql

CREATE SCHEMA blektu;

CREATE TABLE blektu.products (
  productID SERIAL,
  productSKU VARCHAR(50) NOT NULL,
  productName VARCHAR(100) NOT NULL,
  productPrice DECIMAL NOT NULL,
  productWeight DECIMAL,
  productCartDescription VARCHAR(250) NOT NULL, -- ?
  productShortDescription VARCHAR(1000) NOT NULL,
  productLongDescription TEXT,
  productThumbnail VARCHAR(100) NOT NULL,
  productImage VARCHAR(100) NOT NULL,
  productCategoryID INT NOT NULL REFERENCES blektu.product_categories(categoryID),
  productStock DECIMAL, -- ?
  productLive BOOLEAN NOT NULL, -- ?
  productUnlimited BOOLEAN NOT NULL, -- ?
  productLocation VARCHAR(250) NOT NULL,

  PRIMARY KEY(ProductID)
);

CREATE TABLE blektu.order_details (
  detailID SERIAL,
  detailOrderID INT NOT NULL REFERENCES blektu.orders(orderID),
  detailProductID INT NOT NULL REFERENCES blektu.products(productID),
  detailName VARCHAR(250) NOT NULL,
  detailSKU VARCHAR(50) NOT NULL, -- ?
  quantity INT NOT NULL,

  PRIMARY KEY (detailID)
);

CREATE TABLE blektu.product_options (
  productOptionID	SERIAL,
  optionID INT NOT NULL REFERENCES blektu.options(optionID),
  productID INT NOT NULL REFERENCES blektu.products(productID),
  optionGroupID INT NOT NULL,
  optionPriceIncrement REAL, -- ?

  PRIMARY KEY (productOptionID)
);

CREATE TABLE blektu.product_categories (
  categoryID SERIAL,
  categoryName VARCHAR(50) NOT NULL,

  PRIMARY KEY(CategoryID)
);

CREATE TABLE blektu.orders (
  orderID SERIAL,
  orderUserID INT NOT NULL REFERENCES blektu.users(userID),
  orderAMount DECIMAL,
  orderShipName VARCHAR(100) NOT NULL,
  orderShipAddress1 VARCHAR(100) NOT NULL,
  orderShipAddress2 VARCHAR(100),
  orderCity VARCHAR(50) NOT NULL,
  orderState VARCHAR(50) NOT NULL,
  orderZip VARCHAR(20) NOT NULL,
  orderCountry VARCHAR(50) NOT NULL,
  orderPhone VARCHAR(20) NOT NULL,
  orderFax VARCHAR(20) NOT NULL,
  orderShipping DECIMAL,
  orderTax DECIMAL,
  orderEmail VARCHAR(100) NOT NULL, -- ?
  orderShipped BOOLEAN,
  orderTrackingNumber VARCHAR(80),

  PRIMARY KEY (orderID)
);

-- Per cfare sherben?
CREATE TABLE blektu.options (
  optionID SERIAL,
  optionName VARCHAR(50) NOT NULL,

  PRIMARY KEY (optionID)
);


-- Per cfare sherben?
CREATE TABLE blektu.option_groups (
  optionGroupID SERIAL,
  optionGroupName VARCHAR(50) NOT NULL,

  PRIMARY KEY (optionGroupID)
);


CREATE TABLE blektu.users (
  userID SERIAL,
  userEmail VARCHAR(50) NOT NULL,
  userPassword VARCHAR(500) NOT NULL,
  userFirstName VARCHAR(50) NOT NULL,
  userLastNAme VARCHAR(50) NOT NULL,
  userCity VARCHAR(90) NOT NULL,
  userState VARCHAR(20) NOT NULL,
  userZip VARCHAR(12) NOT NULL,
  userEmailVerified BOOLEAN NOT NULL,
  userVerificationCode VARCHAR(20) NOT NULL,
  userPhone VARCHAR(20),
  userFax VARCHAR(20),
  userCountry VARCHAR(20) NOT NULL,
  userAddress1 VARCHAR(20) NOT NULL,
  userAddress2 VARCHAR(20),

  PRIMARY KEY (userID)
);

OK
Ke zevendesuar Serial me INT(12) qe autoinkrementohet etj pra ke emertuar ndryshe tipet e te dhenave.
Bera commit ketij gisti si dhe shtova komente dhe korrigjime te vogla.