VbzCart/docs/tables/stk history legacy: Difference between revisions

From Woozle Writes Code
< VbzCart‎ | docs‎ | tables
Jump to navigation Jump to search
imported>Woozle
m (a little tidying -- trying to figure out what is different from stk_history)
imported>Woozle
(changes, round 1: NOT NULLing)
Line 4: Line 4:
* '''History''':
* '''History''':
** '''2008-12-29''' Created so that old data could be accommodated without compromising integrity of new data added to {{vbzcart/table|stk_history}}.
** '''2008-12-29''' Created so that old data could be accommodated without compromising integrity of new data added to {{vbzcart/table|stk_history}}.
** '''2016-03-02'''
*** Found that there were no NULL values in my data for '''ID_StkBin''', '''ID_StkLine''', '''CH_OthType''', '''IDS_OthCont''', '''QtyAdded''' -- so made them NOT NULL
**** Actually, '''QtyAdded''' was already NOT NULL in my actual table... just making that official, in this table which should be going away shortly.
==SQL==
==SQL==
<mysql>CREATE TABLE `stk_history_legacy` (
<mysql>CREATE TABLE `stk_history_legacy` (
   `ID`          INT    NOT NULL  AUTO_INCREMENT,
   `ID`          INT    NOT NULL  AUTO_INCREMENT,
   `ID_StkBin`    INT /*NOT NULL*/ COMMENT "stock item is being moved to or from this bin",
   `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",
   `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",
   `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_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",
   `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",
   `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",
   `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",
   `QtyBefore`    INT  /*NOT NULL*/ COMMENT "quantity found in this stock line before the move",

Revision as of 23:21, 2 March 2016

About

  • Purpose: For accommodating old stock history data (possibly imported from another application) where some rows don't meet the stricter requirements of Template:Vbzcart/table. This table should have all the same fields as that table, but some fields may have looser requirements and additional fields may be added.
  • Usage: Feel free to NOT-NULLify any existing fields, or even add additional fields to preserve your old data. If you have no old data to deal with, this table is unnecessary; you can either leave it empty, or modify Template:Vbzcart/query not to require it.
  • History:
    • 2008-12-29 Created so that old data could be accommodated without compromising integrity of new data added to Template:Vbzcart/table.
    • 2016-03-02
      • Found that there were no NULL values in my data for ID_StkBin, ID_StkLine, CH_OthType, IDS_OthCont, QtyAdded -- so made them NOT NULL
        • Actually, QtyAdded was already NOT NULL in my actual table... just making that official, in this table which should be going away shortly.

SQL

<mysql>CREATE TABLE `stk_history_legacy` (

 `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`        TINYTEXT     DEFAULT NULL COMMENT "optional human-added explanatory notes",

/* fields specific to vbz.net data migration -- these may be omitted for normal usage */

 `oIDS_ContSrce` VARCHAR(31),
 `oIDS_LineSrce` VARCHAR(31),
 `oIDS_ContDest` VARCHAR(31),
 `oIDS_LineDest` VARCHAR(31),
 `oID_StkBin`    INT,
 `oID_StkLine`   INT,
 `oQtyFnd`       INT,
 `oQtyDone`      INT,
 `oQtyLeft`      INT,
 `wasSrce`       BIT COMMENT "how the data was handled at migration: TRUE = stock is source",
 PRIMARY KEY(`ID`)

) ENGINE = MYISAM;</mysql>