Main menu

Inventory Allocation Sub-System

Submitted by mikehostetler on Thu, 04/26/2007 - 09:04

The Inventory Allocation functionality in my Inventory Management system is designed to distribute units from inventory to a number of partners. There's way to many business rules associated with this piece to describe here, but I wanted to at least give a high-level picture.

<?php
--
-- Table structure for table <code>ia</code>
--

CREATE TABLE <code>ia</code> (
  <code>ia_id</code> int(10) unsigned NOT NULL auto_increment,
  <code>date</code> date NOT NULL default '0000-00-00',
  <code>closed</code> tinyint(1) NOT NULL default '0',
  <code>locked</code> tinyint(1) NOT NULL default '0',
  <code>total_weight</code> int(10) default '0',
  <code>total_served</code> int(10) default '0',
  PRIMARY KEY  (<code>ia_id</code>),
  KEY <code>IDX_ia_date</code> (<code>date</code>)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
--

Table structure for table <code>ia_unit</code>
--

CREATE TABLE <code>ia_unit</code> (
  <code>ia_unit_id</code> int(10) unsigned NOT NULL auto_increment,
  <code>ia_id</code> int(10) unsigned NOT NULL default '0',
  <code>category_id</code> int(10) unsigned NOT NULL default '0',
  <code>unit_number</code> int(10) NOT NULL default '0',
  <code>cases</code> int(10) NOT NULL default '0',
  <code>case_weight</code> decimal(10,6) NOT NULL default '0.000000',
  <code>weight</code> int(10) NOT NULL default '0',
  <code>weight_percentage</code> decimal(10,6) default '0.000000',
  PRIMARY KEY  (<code>ia_unit_id</code>),
  KEY <code>IDX_ia_unit_ia_id</code> (<code>ia_id</code>),
  KEY <code>IDX_ia_unit_category_id</code> (<code>category_id</code>),
  KEY <code>IDX_ia_unit_unit_number</code> (<code>unit_number</code>),
  KEY <code>IDX_ia_unit_ia_id_category_id</code> (<code>ia_id</code>,<code>category_id</code>)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
--

Table structure for table <code>ia_partner</code>
--

CREATE TABLE <code>ia_partner</code> (
  <code>ia_partner_id</code> int(10) unsigned NOT NULL auto_increment,
  <code>ia_id</code> int(10) unsigned NOT NULL default '0',
  <code>partner_id</code> int(10) unsigned NOT NULL default '0',
  <code>net_served</code> int(10) NOT NULL default '0',
  <code>weight_needed</code> int(10) default '0',
  <code>served_percentage</code> decimal(10,6) default '0.000000',
  <code>dist_transaction_id</code> int(10) unsigned default NULL,
  PRIMARY KEY  (<code>ia_partner_id</code>),
  KEY <code>IDX_ia_partner_ia_id</code> (<code>ia_id</code>),
  KEY <code>IDX_ia_partner_partner_id</code> (<code>partner_id</code>),
  KEY <code>IDX_ia_partner_dist_transaction_id</code> (<code>dist_transaction_id</code>)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
--

Table structure for table <code>ia_allocation</code>
--

CREATE TABLE <code>ia_allocation</code> (
  <code>ia_allocation_id</code> int(10) unsigned NOT NULL auto_increment,
  <code>ia_id</code> int(10) unsigned NOT NULL default '0',
  <code>ia_partner_id</code> int(10) unsigned NOT NULL default '0',
  <code>ia_unit_id</code> int(10) unsigned NOT NULL default '0',
  <code>weight</code> int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (<code>ia_allocation_id</code>),
  UNIQUE KEY <code>IDX_ia_allocation_ia_id_ia_partner_id_ia_unit_id</code> (<code>ia_id</code>,<code>ia_partner_id</code>,<code>ia_unit_id</code>),
  KEY <code>IDX_ia_allocation_ia_id</code> (<code>ia_id</code>),
  KEY <code>IDX_ia_allocation_ia_partner_id</code> (<code>ia_partner_id</code>),
  KEY <code>IDX_ia_allocation_ia_unit_id</code> (<code>ia_unit_id</code>)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
?>
Taxonomy upgrade extras: 
mysql
cics
mysqli
stored procedure