VbzCart/tables/stk history

About

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

1. REDIRECT Template:l/vc/table, cat_items

• History:
  • 2008-12-26 (saving current version to separate page)
• 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_Stock` INT NOT NULL COMMENT "stk_items.ID of item being moved",
 `ID_Item` INT /* NOT NULL */ COMMENT "cat_items.ID of item being moved",
 `QtyFound` INT /* NOT NULL */ COMMENT "number of items found in this record before the move",
 `QtyDone` INT NOT NULL COMMENT "number of items moved (Qty fields at targ/dest may change value later)",
 `QtyLeft` INT NOT NULL COMMENT "number of items remaining after the move",
 `When` DATETIME NOT NULL COMMENT "when the move happened",
 `IDS_ContSrce` varchar(31) /* NOT NULL */ COMMENT "_stk_containers.IDS of where the item was moved from",
 `IDS_ContDest` varchar(31) /* NOT NULL */ COMMENT "_stk_containers.IDS of where the item was moved to",
 `IDS_LineSrce` varchar(31) /* NOT NULL */ COMMENT "hypothetical _stk_lines.IDS of where item came from",
 `IDS_LineDest` varchar(31) /* NOT NULL */ COMMENT "hypothetical _stk_lines.IDS of where item was moved to",
 `Notes` varchar(255) DEFAULT NULL COMMENT "optional explanatory notes",
 PRIMARY KEY(`ID`)

) ENGINE = MYISAM;</mysql>