Thursday 13 February 2014

OpenXava RETAILDB DataModel MySQL DB Script

-- -----------------------------------------------------
-- Table RETAILDB.CUSTOMER
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS RETAILDB.CUSTOMER (
  customerId INTEGER NOT NULL ,
  customerName VARCHAR(50) NOT NULL ,
  PRIMARY KEY (customerId) ,
  UNIQUE INDEX customerName_UNIQUE (customerName ASC) );

-- -----------------------------------------------------
-- Table RETAILDB.CATEGORY
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS RETAILDB.CATEGORY (
  categoryCode VARCHAR(5) NOT NULL ,
  categoryName VARCHAR(50) NOT NULL ,
  PRIMARY KEY (categoryCode) ,
  UNIQUE INDEX categoryName_UNIQUE (categoryName ASC) );

-- -----------------------------------------------------
-- Table RETAILDB.PRODUCT
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS RETAILDB.PRODUCT (
  productCode VARCHAR(10) NOT NULL ,
  productName VARCHAR(50) NOT NULL,
  description VARCHAR(100) ,  
  category_categoryCode VARCHAR(5) NOT NULL ,
  PRIMARY KEY (productCode) ,
  UNIQUE INDEX productName_UNIQUE (productName ASC),
 INDEX category_categoryCode_idx (category_categoryCode ASC) ,
CONSTRAINT category_categoryCode0
    FOREIGN KEY (category_categoryCode)
    REFERENCES RETAILDB.CATEGORY (categoryCode));
 

-- -----------------------------------------------------
-- Table RETAILDB.ITEM
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS RETAILDB.ITEM (
  itemCode VARCHAR(10) NOT NULL ,
  itemName VARCHAR(50) NOT NULL,  
  unitPrice NUMERIC NOT NULL,
  product_productCode VARCHAR(10) NOT NULL ,
  PRIMARY KEY (itemCode) ,
  UNIQUE INDEX itemName_UNIQUE (itemName ASC),
 INDEX product_productCode_idx (product_productCode ASC) ,
CONSTRAINT product_productCode0
    FOREIGN KEY (product_productCode)
    REFERENCES RETAILDB.PRODUCT (productCode));

 
-- -----------------------------------------------------
-- Table RETAILDB.IMAGES
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS RETAILDB.IMAGES (
  ID VARCHAR(255) NOT NULL ,
  GALLERY VARCHAR(255) ,
  IMAGE VARBINARY(255) ,
  PRIMARY KEY (ID));

-- -----------------------------------------------------
-- Table RETAILDB.INVOICE
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS RETAILDB.INVOICE (
  id VARCHAR(32) NOT NULL ,
  customer_customerId INTEGER NOT NULL ,
  invoiceNo INTEGER NOT NULL,
  year INTEGER,
  date TIMESTAMP,
  vatPercentage INTEGER,  
  remarks VARCHAR(255),
PRIMARY KEY (id) ,
UNIQUE INDEX invoiceNo_UNIQUE (invoiceNo ASC),
INDEX customer_customerId_idx (customer_customerId ASC) ,
CONSTRAINT customer_customerId0
    FOREIGN KEY (customer_customerId)
    REFERENCES RETAILDB.CUSTOMER (customerId ));

-- -----------------------------------------------------
-- Table RETAILDB.INVOICEDETAIL
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS RETAILDB.INVOICEDETAIL (
  id VARCHAR(32) NOT NULL ,
  invoice_id VARCHAR(32) NOT NULL,
  item_itemCode VARCHAR(10) NOT NULL ,
  quantity INTEGER NOT NULL,
PRIMARY KEY (id) ,
INDEX invoice_id_idx (invoice_id ASC) ,
CONSTRAINT invoice_id0
    FOREIGN KEY (invoice_id)
    REFERENCES RETAILDB.INVOICE (id ),
INDEX item_itemCode_idx (item_itemCode ASC) ,
CONSTRAINT item_itemCode0
    FOREIGN KEY (item_itemCode)
    REFERENCES RETAILDB.ITEM (itemCode));

-- -----------------------------------------------------
-- Table RETAILDB.XYZ
-- -----------------------------------------------------

No comments:

Post a Comment