VbzCart/docs/tables/stk history: Difference between revisions
Jump to navigation
Jump to search
imported>Woozle (12/29 final (?) revisions for working version) |
imported>Woozle (turned on all NOT NULLs because this will now only be for new data) |
||
Line 17: | Line 17: | ||
*** +CH_OthType | *** +CH_OthType | ||
*** +ID_OthCont | *** +ID_OthCont | ||
*** Created {{vbzcart/table|stk_history_legacy}} to handle legacy data properly; turned on all NOT NULLs | |||
* '''Rules''': | * '''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.) | ** 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.) | ||
Line 27: | Line 28: | ||
CREATE TABLE `stk_history` ( | CREATE TABLE `stk_history` ( | ||
`ID` INT | `ID` INT NOT NULL AUTO_INCREMENT, | ||
`ID_StkBin` INT | `ID_StkBin` INT NOT NULL COMMENT "stock item is being moved to or from this bin", | ||
`ID_StkLine` INT | `ID_StkLine` INT NOT NULL COMMENT "stk_items.ID of item in this bin being moved", | ||
`CH_OthType` | `CH_OthType` CHAR NOT NULL COMMENT "type for 'other' container & line", | ||
`ID_OthCont` INT | `ID_OthCont` INT NOT NULL COMMENT "[container type's table].ID of the Other place", | ||
`ID_OthLine` INT | `ID_OthLine` INT NOT NULL COMMENT "[container type's line table].ID of of the Other place", | ||
`IDS_OthCont` VARCHAR(31) | `IDS_OthCont` VARCHAR(31) NOT NULL COMMENT "container.IDS of the Other place", | ||
`ID_Item` INT | `ID_Item` INT NOT NULL COMMENT "cat_items.ID of item being moved", | ||
`QtyBefore` INT | `QtyBefore` INT NOT NULL COMMENT "quantity found in this stock line before the move", | ||
`QtyAdded` INT | `QtyAdded` INT NOT NULL COMMENT "quantity moved (Qty fields at targ/dest may change value later)", | ||
`QtyAfter` INT | `QtyAfter` INT NOT NULL COMMENT "quantity remaining in this stock line after the move", | ||
`QtyBinBefore` INT | `QtyBinBefore` INT NOT NULL COMMENT "quantity of this ID_Item in the current bin before the move", | ||
`QtyBinAfter` INT | `QtyBinAfter` INT NOT NULL COMMENT "quantity of this ID_Item in the current bin after the move", | ||
`When` DATETIME | `When` DATETIME NOT NULL COMMENT "when the move happened", | ||
`What` VARCHAR(63) | `What` VARCHAR(63) DEFAULT NULL COMMENT "brief automatic description of operation", | ||
`Notes` | `Notes` TINYTEXT COMMENT "optional human-added explanatory notes", | ||
PRIMARY KEY(`ID`) | PRIMARY KEY(`ID`) | ||
) ENGINE = MYISAM;</mysql> | ) ENGINE = MYISAM;</mysql> | ||
<section end=sql /> | <section end=sql /> |
Revision as of 18:50, 29 December 2008
About
- Purpose: log of all stock movement
- Refers to: Template:Vbzcart/query, Template:Vbzcart/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
- Created Template:Vbzcart/table to handle legacy data properly; turned on all NOT NULLs
- 2008-12-26 Changed field names, added new fields:
- 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
<section begin=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` TINYTEXT COMMENT "optional human-added explanatory notes", PRIMARY KEY(`ID`)
) ENGINE = MYISAM;</mysql> <section end=sql />