VbzCart/docs/tables/stk history: Difference between revisions
Jump to navigation
Jump to search
imported>Woozle (+ID_Event) |
imported>Woozle (making line fields DEFAULT NULL instead of NOT NULL) |
||
| Line 10: | Line 10: | ||
** '''ID_Event''': where known, this is the ID of the corresponding event in {{vbzcart|table|event_log}} | ** '''ID_Event''': where known, this is the ID of the corresponding event in {{vbzcart|table|event_log}} | ||
*** This is a sort of first step towards making this log part of the unified event log. | *** 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 → 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 {{vbzcart/table|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_OthLine''', '''IDS_OthCont''' | ||
** 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). | ** Left these as NOT NULL: '''ID_StkBin''', '''ID_OthCont''', '''CH_OthType''' -- we should never need to create a ''container'' or ''bin'' on either end, and we should always know what ''type'' of container the Other is. | ||
==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== | ==SQL== | ||
<mysql>CREATE TABLE `stk_history` ( | |||
`ID` INT NOT NULL AUTO_INCREMENT, | |||
CREATE TABLE `stk_history` ( | `ID_Event` INT DEFAULT NULL COMMENT "ID of corresponding event in unified log", | ||
`ID` INT | `ID_StkBin` INT NOT NULL COMMENT "stock item is being moved to or from this bin", | ||
`ID_Event` INT | `ID_StkLine` INT DEFAULT NULL COMMENT "stk_items.ID of item in this bin being moved", | ||
`ID_StkBin` INT | `CH_OthType` CHAR NOT NULL COMMENT "type for 'other' container & line", | ||
`ID_StkLine` INT | `ID_OthCont` INT NOT NULL COMMENT "[container type's table].ID of the Other place", | ||
`CH_OthType` CHAR | `ID_OthLine` INT DEFAULT NULL COMMENT "[container type's line table].ID of of the Other place", | ||
`ID_OthCont` INT | `IDS_OthCont` VARCHAR(31) DEFAULT NULL COMMENT "container.IDS of the Other place", | ||
`ID_OthLine` INT | |||
`IDS_OthCont` VARCHAR(31) | |||
`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", | ||
| Line 58: | Line 59: | ||
PRIMARY KEY(`ID`) | PRIMARY KEY(`ID`) | ||
) ENGINE = MYISAM;</mysql> | ) ENGINE = MYISAM;</mysql> | ||
Revision as of 20:20, 26 May 2014
About
- Purpose: log of all stock movement
- Refers to: Template:Vbzcart/query, Template:Vbzcart/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 Template:Vbzcart/query 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 Template:Vbzcart
- 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 → 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 Template:Vbzcart/table 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_OthLine, IDS_OthCont
- Left these as NOT NULL: ID_StkBin, ID_OthCont, CH_OthType -- we should never need to create a container or bin on either end, and we should always know what type of container the Other is.
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 NOT 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", `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;</mysql>