VbzCart/tables/stk history

About

• Purpose: log of all stock movement
• Refers to:

1. REDIRECT Template:l/vc/query,
2. REDIRECT Template:l/vc/table

• History:
  • 2008-12-26 Changed field names, added new fields:
    • QtyFound → QtyBefore – quantity in this stock line before moving
    • QtyDone → QtyAdded – quantity added to this stock line
    • QtyLeft → 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 → ID_StkLine
    • ID_Bin → ID_StkBin
    • ID_Cont → ID_OthCont
    • ID_Line → ID_OthLine
  • 2008-12-29 Final revisions to match what I ended up with in Access for the migration:
    • +CH_OthType
    • +ID_OthCont
• 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.
• 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).
  • Commented-out bits can be included if you have no incomplete legacy data to deal with.

SQL

<mysql>DROP TABLE IF EXISTS `stk_history`;

CREATE TABLE `stk_history` (

 `ID`           INT    NOT NULL   AUTO_INCREMENT,
 `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   NOT NULL   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 /*NOT NULL*/ COMMENT "quantity of this ID_Item in the current bin after the move",
 `When`         DATETIME      NOT NULL    COMMENT "when the move happened",
 `What`         VARCHAR(63)  DEFAULT NULL COMMENT "brief automatic description of operation",
 `Notes`        VARCHAR(255) DEFAULT NULL COMMENT "optional human-added explanatory notes",
 PRIMARY KEY(`ID`)

) ENGINE = MYISAM;</mysql>