VbzCart/tables/stk history

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
< VbzCart‎ | tables
Revision as of 21:20, 2 March 2016 by Woozle (talk | contribs) (→‎SQL: Qty*After fields are explicitly DEFAULT NULL)
Jump to navigation Jump to search

About

  • Purpose: log of all stock movement
  • Refers to:
  1. REDIRECT Template:l/vc/query,
  2. REDIRECT Template:l/vc/table
  • 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 qryStock_containers 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 event_log
      • This is a sort of first step towards making this log part of the unified event log.
  • Future:
    • Rename QtyBefore and QtyAfter to QtyLineBefore and QtyLineAfter.

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
    • Created stk_history_legacy 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
  • 2014-05-26 Made fields DEFAULT NULL instead of NOT NULL so we don't have to know all of them before the event is started: ID_StkLine, ID_OthCont, ID_OthLine, IDS_OthCont
    • Left these as NOT NULL: ID_StkBin, CH_OthType -- Bins must be explicitly created by an administrator, and we should always know what type of container the Other is.
  • 2014-06-26 Lengthened What from 63 chars to 255 because descriptions were being cut off.
  • 2016-02-24 Apparently I never changed the actual DB; this time, lengthened What from 63 chars to TEXT.

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

<mysql>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         DEFAULT 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         DEFAULT NULL COMMENT "[container type's table].ID of the Other place",
 `ID_OthLine`   INT         DEFAULT NULL COMMENT "[container type's line table].ID of of the Other place",
 `IDS_OthCont`  VARCHAR(31) DEFAULT 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",
 `QtyBinBefore` INT         NOT NULL COMMENT "quantity of this ID_Item in the current bin before the move",
 `QtyAdded`     INT         NOT NULL COMMENT "quantity moved (Qty fields at targ/dest may change value later)",
 `QtyAfter`     INT          DEFAULT NULL COMMENT "quantity remaining in this stock line after the move",
 `QtyBinAfter`  INT          DEFAULT NULL 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`         TEXT         DEFAULT NULL COMMENT "automatic, succinct 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;</mysql>

Retrieved from "https://htyp.org/mw/index.php?title=VbzCart/tables/stk_history&oldid=21728"