VbzCart/tables/stk lines

About

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

1. REDIRECT Template:l/vc/table

• 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. We now have a proper stock event log.
• Fields:
  • WhenCleared: set whenever removing stock from a line results in zero quantity; not cleared if the line is ever refilled.
• Future:
  • 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, so maybe that is a thing which needs to happen.)

History

• 2017-03-16:
  • WhenRemoved/Qty remediation:
    • set Qty to zero where WhenRemoved is not NULL
    • renaming WhenRemoved to WhenCleared - this field no longer affects determination of a stock line's availability
      • I had originally been planning to remove this field altogether, but thought better of it.
  • ID_Bin and ID_Item are now both NOT NULL (fixed bogus record by setting both fields to zero)
  • renamed table from stk_items to stk_lines
    • This was a change I had been planning for a long time. The suffix "items" was already overloaded, and "x_lines" correctly implies a many-to-one relationship with "x".
  • engine was apparently changed from MyISAM to InnoDB already; updating SQL here to reflect that

SQL

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

 `ID`      INT NOT NULL AUTO_INCREMENT,
 `ID_Bin`  INT NOT NULL COMMENT 'stk_bins.ID: which bin these items are in',
 `ID_Item` INT NOT 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",
 `WhenCleared` 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 = InnoDB;</mysql>