Intermediate Schema for MySQL and Amazon RDS

-- Salescast Push DB schema v1 for mySQL
-- Create database and execute queries in it.
-- Make sure to read documentation of how to customize meta data field list
-- Important: names of the tables and required fields are CASE-Sensitive 
-- (on most Linux installations of mySQL, which also includes Amazon)!

delimiter GO

START TRANSACTION

GO

CREATE TABLE `Lokad_Items` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  
  -- Meta data field samples
  -- `LabelProduct` varchar(256) DEFAULT NULL, 
  -- `LabelSupplier` varchar(256) DEFAULT NULL, 
  -- `TagColor` varchar(50) DEFAULT NULL, 
  -- end of metadata field samples
  
  -- Inventory fields
  -- `StockOnHand` float DEFAULT NULL, 
  -- `StockOnOrder` float DEFAULT NULL, 
  -- `LeadTime` float DEFAULT NULL, 
  -- `ServiceLevel` float DEFAULT NULL,
  -- `LotMultiplier` float DEFAULT NULL, 
  -- end of inventory fields

   PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

GO

CREATE TABLE `Lokad_Orders` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `Item_Id` bigint(20) NOT NULL,
  `OrderDate` datetime NOT NULL,
  `Quantity` float NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

GO

ALTER TABLE `Lokad_Orders` 
  ADD CONSTRAINT `FK_Lokad_Orders_Items`
  FOREIGN KEY (`Item_Id` )
  REFERENCES `Lokad_Items` (`Id` )
, ADD INDEX `FK_Lokad_Orders_Items` (`Item_Id` ASC) ;

GO

ALTER TABLE `Lokad_Orders` 
ADD INDEX `IX_Lokad_Orders_Query` (`Item_Id` ASC, `OrderDate` ASC) ;
GO


CREATE TABLE `Lokad_Events` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `Item_Id` bigint(20) NOT NULL,
  `StartDate` datetime NOT NULL,  
  
  -- Custom tags to describe the event
  -- `TagPromotionMechanism` varchar(50) DEFAULT NULL, 
  -- `TagPromotionMarketing` varchar(50) DEFAULT NULL, 
  -- `TagPromotionPackaging` varchar(50) DEFAULT NULL, 
  -- `TagPromotionFacing` varchar(50) DEFAULT NULL, 
  -- end of custom tags
  
   PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

GO

-- This table is typically omitted in the early setup
-- (event info is usually harder to gather)
ALTER TABLE `Lokad_Events` 
  ADD CONSTRAINT `FK_Lokad_Events_Items`
  FOREIGN KEY (`Item_Id` )
  REFERENCES `Lokad_Items` (`Id` )
, ADD INDEX `FK_Lokad_Events_Items` (`Item_Id` ASC) ;

GO

-- Include those tables only if you want forecasts to be pushed
-- into the intermediate table.

CREATE  TABLE `Lokad_ForecastSync` (
  `Id` BIGINT NOT NULL AUTO_INCREMENT,
  `SyncDate` DATETIME NOT NULL ,
  `SyncReference` VARCHAR(36) NOT NULL ,
  `CompletedDate` DATETIME DEFAULT NULL ,
  PRIMARY KEY (`Id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

GO

CREATE  TABLE `Lokad_Forecasts` (
  `Id` BIGINT NOT NULL AUTO_INCREMENT,
  `Sync_Id` BIGINT NOT NULL ,
  `Item_Id` BIGINT NOT NULL ,
  `ForecastDate` DATETIME NOT NULL ,
  `Quantity` FLOAT NOT NULL,
  `Accuracy` FLOAT NOT NULL,
  PRIMARY KEY (`Id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

GO

ALTER TABLE `Lokad_Forecasts` 
  ADD CONSTRAINT `FK_Lokad_Forecasts_ForecastSync`
  FOREIGN KEY (`Sync_Id` )
  REFERENCES `Lokad_ForecastSync` (`Id` )
, ADD INDEX `FK_Lokad_Forecasts_ForecastsSync` (`Sync_Id` ASC) ;

GO

ALTER TABLE `Lokad_ForecastSync` 
ADD INDEX `IX_Lokad_ForecastSync_SyncReference` (`SyncReference` ASC) ;

GO

-- Configure this table with the desired output format
-- for the optimization information
CREATE TABLE `Lokad_OptimizedItems` (
  -- Required columns that SHOULD be included exactly as specified
  `Id` BIGINT NOT NULL AUTO_INCREMENT,
  `Sync_Id` BIGINT NOT NULL ,
  `Item_Id` BIGINT NOT NULL ,
  
  -- optional Calculated fields
  `ReorderPoint` float DEFAULT NULL,
  `OrderQuantity` float DEFAULT NULL,
  `StockCover` float DEFAULT NULL,
  `LeadDemand` float DEFAULT NULL,
  -- end of optional calculated fields
  
  -- Optional data field samples.
  -- if provided, they should be NULLable and match original fields from Lokad_Items
  -- `LabelSKU` varchar(256) DEFAULT NULL,   
  -- `TagColor` varchar(50) DEFAULT NULL,   
  
  -- `StockOnHand` float DEFAULT NULL, 
  -- `StockOnOrder` float DEFAULT NULL, 
  -- `LeadTime` float DEFAULT NULL, 
  -- `ServiceLevel` float DEFAULT NULL , 
  -- end of optional data field samples

  PRIMARY KEY (`Id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

GO

ALTER TABLE `Lokad_OptimizedItems` 
  ADD CONSTRAINT `FK_Lokad_OptimizedItems_ForecastSync`
  FOREIGN KEY (`Sync_Id` )
  REFERENCES `Lokad_ForecastSync` (`Id` )
, ADD INDEX `FK_Lokad_OptimizedItems_ForecastSync` (`Sync_Id` ASC) ;

GO

COMMIT