VbzCart/docs/tables/stk lines: Difference between revisions

From Woozle Writes Code
< VbzCart‎ | docs‎ | tables
Jump to navigation Jump to search
imported>Woozle
(deprecating 3 fields; many notes on this)
imported>Woozle
(some long-planned remediation)
Line 4: Line 4:
* '''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.
* '''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''':
* '''Fields''':
** '''WhenRemoved''': DEPRECATED. Keep setting it properly until it is removed completely, but don't use it.
** '''WhenCleared''': set whenever removing stock from a line results in zero quantity; not cleared if the line is ever refilled.
*** 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''':
* '''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.
** 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.)
*** 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==
==SQL==
<mysql>DROP TABLE IF EXISTS `stk_items`;
<mysql>DROP TABLE IF EXISTS `stk_lines`;
CREATE TABLE `stk_items` (
CREATE TABLE `stk_items` (
   `ID` INT NOT NULL AUTO_INCREMENT,
   `ID`     INT NOT NULL AUTO_INCREMENT,
   `ID_Bin` INT DEFAULT NULL COMMENT 'stk_bins.ID: which bin these items are in',
   `ID_Bin` INT NOT 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',
   `ID_Item` INT NOT NULL COMMENT 'cat_items.ID: which exact type of item',
   `Qty` INT DEFAULT NULL,
   `Qty` INT DEFAULT NULL,
   `WhenAdded` DATETIME DEFAULT NULL COMMENT 'when stock record was created for this item',
   `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)",
   `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",
   `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",
   `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",
   `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)",
   `CatNum` varchar(63) DEFAULT NULL COMMENT "(DEPRECATED) catalog number on tag (official cat# can change over time)",
  `Notes` TEXT,
  `Notes` TEXT,
   PRIMARY KEY(`ID`)
   PRIMARY KEY(`ID`)
  ) ENGINE = MYISAM;</mysql>
  ) ENGINE = InnoDB;</mysql>

Revision as of 21:21, 16 March 2017

About

  • Purpose: listing of what is now and what used to be in stock
  • Refers to: Template:Vbzcart/table, Template:Vbzcart/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.
  • 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>
Retrieved from "https://wooz.dev/mw/index.php?title=VbzCart/docs/tables/stk_lines&oldid=6203"