VbzCart/tables/stk history

About

 * Purpose: log of all stock movement
 * Refers to: ,
 * Rules:
 * Stock items must always be moved to or from a stock line. (The source/destination can be either another stock line or a line from a package or received restock.)
 * The sign of QtyAdded indicates whether the movement was to or from.
 * Fields:
 * CH_OthType: appropriate type code -- see for codes
 * IDS_OthCont: type code plus separator (period) plus ID unique to that type
 * ID_Event: where known, this is the ID of the corresponding event in
 * This is a sort of first step towards making this log part of the unified event log.
 * History:
 * 2008-12-26 Changed field names, added new fields:
 * QtyFound &rarr; QtyBefore – quantity in this stock line before moving
 * QtyDone &rarr; QtyAdded – quantity added to this stock line
 * QtyLeft &rarr; QtyAfter – quantity in this stock line after moving
 * + QtyTotBefore – total of item in this bin before move)
 * + QtyTotAfter – total of item in this bin after move)
 * 2008-12-27 More field name changes, to show grouping of local ("stock") and external ("other") locations:
 * ID_Stock &rarr; ID_StkLine
 * ID_Bin &rarr; ID_StkBin
 * ID_Cont &rarr; ID_OthCont
 * ID_Line &rarr; ID_OthLine
 * 2008-12-29 Final revisions to match what I ended up with in Access for the migration:
 * +CH_OthType
 * +ID_OthCont
 * Created to handle legacy data properly; turned on all NOT NULLs
 * 2009-12-20 Added fields WhoAdmin, WhoSystem, WhoNetwork
 * 2009-12-22 Renamed When -> WhenStarted; added WhenFinished; QtyAfter and QtyBinAfter can now be NULL so we can record them when the event is complete.
 * 2010-12-01 Added ID_Event field -- working towards including this in unified event logging
 * Notes:
 * The IDS_Line* fields are somewhat redundant, but I decided that redundancy was a goal: one of the purposes of history data (including stk_history) is to help reconstruct what happened when something goes wrong. Maintenance of the _stk_containers table is also somewhat redundant, but should make displaying meaningful history reports quicker (i.e. it's basically a sort of cache, as are all the _* tables).

SQL
DROP TABLE IF EXISTS `stk_history`;

CREATE TABLE `stk_history` ( `ID`           INT         NOT NULL AUTO_INCREMENT,  `ID_Event`     INT     DEFAULT NULL COMMENT "ID of corresponding event in unified log",  `ID_StkBin`    INT         NOT NULL COMMENT "stock item is being moved to or from this bin",  `ID_StkLine`   INT         NOT NULL COMMENT "stk_items.ID of item in this bin being moved",  `CH_OthType`   CHAR        NOT NULL COMMENT "type for 'other' container & line",  `ID_OthCont`   INT         NOT NULL COMMENT "[container type's table].ID of the Other place",  `ID_OthLine`   INT         NOT NULL COMMENT "[container type's line table].ID of of the Other place",  `IDS_OthCont`  VARCHAR(31) NOT NULL COMMENT "container.IDS of the Other place",  `ID_Item`      INT         NOT NULL COMMENT "cat_items.ID of item being moved",  `QtyBefore`    INT         NOT NULL COMMENT "quantity found in this stock line before the move",  `QtyAdded`     INT         NOT NULL COMMENT "quantity moved (Qty fields at targ/dest may change value later)", `QtyAfter`    INT          DEFAULT COMMENT "quantity remaining in this stock line after the move", `QtyBinBefore` INT        NOT NULL COMMENT "quantity of this ID_Item in the current bin before the move", `QtyBinAfter` INT          DEFAULT COMMENT "quantity of this ID_Item in the current bin after the move", `WhenStarted` DATETIME         NOT NULL COMMENT "when the move was started", `WhenFinished` DATETIME    DEFAULT NULL COMMENT "when the move was completed", `What`        VARCHAR(63)  DEFAULT NULL COMMENT "brief automatic description of operation", `WhoAdmin`    VARCHAR(127)     NOT NULL COMMENT "VbzCart admin username", `WhoSystem`   VARCHAR(127) DEFAULT NULL COMMENT "who logged into the operating system, if using non-web client app", `WhoNetwork`  VARCHAR(64)          COMMENT "network name or IP address of client machine", `Notes`       TINYTEXT             COMMENT "optional human-added explanatory notes", PRIMARY KEY(`ID`) ) ENGINE = MYISAM;