VbzCart/tables/stk lines

About

• Purpose: listing of what is now and what used to be in stock
• Refers to:

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

• Notes: In early versions of VbzCart, this table was partly used as a sort of klugey stock-log. If a row containing qty>1 had less than qty removed from it, a new row was "split off" from the existing row and then the WhenRemoved timestamp was set, rather than setting qty to 0.
• Fields:
  • WhenRemoved: DEPRECATED. Keep setting it properly until it is removed completely, but don't use it.
    • There are currently still a lot of records where WhenRemoved is set but Qty is > 0, so until that remediation has been done, we have to keep referring to WhenRemoved. For now, make sure that Qty is set to 0 whenever all pieces have been removed from a record.
• Future:
  • Change name to stk_lines. The affix "items" is already overloaded, and "lines" correctly implies a dependent relationship.
  • For all existing data where WhenRemoved is set and Qty>0, retroactively create stock log entries showing the removal and set Qty to 0. Finally, remove the WhenRemoved field altogether. Code which uses WhenRemoved as a flag should check the Qty field instead ("WhenRemoved IS NULL" -> "Qty > 0", "WhenRemoved IS NOT NULL" -> "Qty == 0").
  • The Cost and CatNum fields seem useful at first glance, but it looks like they mainly complicate things. When pulling items from stock, do we prefer cheaper or more expensive Costs? Why do we actually need to keep track of what the catalog number was, independently of the master Items table? It's possible that this might be useful when looking for an item whose tag has an obsolete catalog number, but we can't rely on the database knowing that (especially for older items likely to have obsolete numbers). Maybe we should start putting item IDs on the tags instead of, or in addition to, the human-friendly-ish catalog number.
    • In any case, they should probably be removed, unless we want to seriously rethink how the stock lines work. (There are currently only 14 records where Cost is filled in, out of 4505 stock line records.) I probably had some idea, in the past, that it would serve as a sort of stock log -- but now we actually have a proper stock log. (...although that does not currently track cost of items.)

SQL

<mysql>DROP TABLE IF EXISTS `stk_items`; CREATE TABLE `stk_items` (

 `ID` INT  NOT NULL AUTO_INCREMENT,
 `ID_Bin` INT DEFAULT NULL COMMENT 'stk_bins.ID: which bin these items are in',
 `ID_Item` INT DEFAULT NULL COMMENT 'cat_items.ID: which exact type of item',
 `Qty` INT DEFAULT NULL,
 `WhenAdded` DATETIME DEFAULT NULL COMMENT 'when stock record was created for this item',
 `WhenChanged` DATETIME DEFAULT NULL COMMENT "when the quantity for this record was last altered (e.g. stock rmvd, or a split)",
 `WhenCounted` DATETIME DEFAULT NULL COMMENT "when this stock record was last verified by hand-count",
 `WhenRemoved` DATETIME DEFAULT NULL COMMENT "(DEPRECATED) if not NULL, item is no longer in stock",
 `Cost` INT DEFAULT NULL COMMENT "(DEPRECATED) what we paid (each) for these particular items in stock",
 `CatNum` varchar(63) DEFAULT NULL COMMENT "(DEPRECATED) catalog number on tag (official cat# can change over time)",
  `Notes` TEXT,
  PRIMARY KEY(`ID`)
) ENGINE = MYISAM;</mysql>