VbzCart/tables/stk lines - Revision history

Woozle at 13:37, 5 September 2017

2017-09-05T13:37:33Z

← Older revision		Revision as of 13:37, 5 September 2017
Line 1:		Line 1:
	==About==		==About==
	* '''Purpose''': listing of what is now and what used to be in stock		* '''Purpose''': listing of what is now and what used to be in stock
	* '''Refers to''': {{~~vbzcart~~/table\|stk_bins}}, {{vbzcart/table\|cat_items}}		* '''Refers to''': {{l/vc/table\|stk_bins}}, {{vbzcart/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.		* '''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''':		* '''Fields''':
	** '''WhenCleared''': set whenever removing stock from a line results in zero quantity; not cleared if the line is ever refilled.		** '''WhenCleared''': set whenever removing stock from a line results in zero quantity; not cleared if the line is ever refilled.

Woozle: Woozle moved page VbzCart/tables/stk items to VbzCart/tables/stk lines: table has been renamed

2017-03-16T21:22:17Z

Woozle moved page VbzCart/tables/stk items to VbzCart/tables/stk lines: table has been renamed

← Older revision	Revision as of 21:22, 16 March 2017
(No difference)

Woozle: some long-planned remediation

2017-03-16T21:21:50Z

some long-planned remediation

← Older revision		Revision as of 21:21, 16 March 2017
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>

Woozle: deprecating 3 fields; many notes on this

2014-06-14T22:04:54Z

deprecating 3 fields; many notes on this

← Older revision		Revision as of 22:04, 14 June 2014
Line 8:		Line 8:
	* '''Future''':		* '''Future''':
	** Change name to '''stk_lines'''. The affix "items" is already overloaded, and "lines" correctly implies a dependent relationship.		** 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, 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").		** 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==		==SQL==
	<mysql>DROP TABLE IF EXISTS `stk_items`;		<mysql>DROP TABLE IF EXISTS `stk_items`;
Line 19:		Line 21:
	`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 "if not NULL, item is no longer in stock",		`WhenRemoved` DATETIME DEFAULT NULL COMMENT "(DEPRECATED) if not NULL, item is no longer in stock",
	`Cost` INT DEFAULT NULL COMMENT "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 "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 = MYISAM;</mysql>

Woozle: this table should be renamed

2014-06-02T20:51:39Z

this table should be renamed

← Older revision		Revision as of 20:51, 2 June 2014
Line 6:		Line 6:
	** '''WhenRemoved''': DEPRECATED. Keep setting it properly until it is removed completely, but don't use it.		** '''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.		*** 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''': For all existing data where WhenRemoved is set and Qty>0, 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").		* '''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, 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").
	==SQL==		==SQL==
	<mysql>DROP TABLE IF EXISTS `stk_items`;		<mysql>DROP TABLE IF EXISTS `stk_items`;

Woozle: tidying

2014-05-11T20:29:53Z

tidying

← Older revision		Revision as of 20:29, 11 May 2014
Line 8:		Line 8:
	* '''Future''': For all existing data where WhenRemoved is set and Qty>0, 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").		* '''Future''': For all existing data where WhenRemoved is set and Qty>0, 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").
	==SQL==		==SQL==
	~~<section begin=sql />~~<mysql>DROP TABLE IF EXISTS `stk_items`;		<mysql>DROP TABLE IF EXISTS `stk_items`;
	CREATE TABLE `stk_items` (		CREATE TABLE `stk_items` (
	`ID` INT NOT NULL AUTO_INCREMENT,		`ID` INT NOT NULL AUTO_INCREMENT,
Line 23:		Line 23:
	PRIMARY KEY(`ID`)		PRIMARY KEY(`ID`)
	) ENGINE = MYISAM;</mysql>		) ENGINE = MYISAM;</mysql>
	~~<section end=sql />~~

Woozle: note about WhenRemoved as currently used in data

2010-11-05T18:24:36Z

note about WhenRemoved as currently used in data

← Older revision		Revision as of 18:24, 5 November 2010
Line 5:		Line 5:
	* '''Fields''':		* '''Fields''':
	** '''WhenRemoved''': DEPRECATED. Keep setting it properly until it is removed completely, but don't use it.		** '''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''': For all existing data where WhenRemoved is set and Qty>0, 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").		* '''Future''': For all existing data where WhenRemoved is set and Qty>0, 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").
	==SQL==		==SQL==

Woozle: note about usage and future of WhenRemoved field, which is now deprecated

2010-06-17T15:36:29Z

note about usage and future of WhenRemoved field, which is now deprecated

← Older revision		Revision as of 15:36, 17 June 2010
Line 2:		Line 2:
	* '''Purpose''': listing of what is now and what used to be in stock		* '''Purpose''': listing of what is now and what used to be in stock
	* '''Refers to''': {{vbzcart/table\|stk_bins}}, {{vbzcart/table\|cat_items}}		* '''Refers to''': {{vbzcart/table\|stk_bins}}, {{vbzcart/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.
			* '''Future''': For all existing data where WhenRemoved is set and Qty>0, 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").
	==SQL==		==SQL==
	<section begin=sql /><mysql>DROP TABLE IF EXISTS `stk_items`;		<section begin=sql /><mysql>DROP TABLE IF EXISTS `stk_items`;

Woozle: extracted from VbzCart/tables

2008-12-29T17:35:38Z

extracted from VbzCart/tables

New page

==About==
* '''Purpose''': listing of what is now and what used to be in stock
* '''Refers to''': {{vbzcart/table|stk_bins}}, {{vbzcart/table|cat_items}}
==SQL==
<section begin=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 "if not NULL, item is no longer in stock",
`Cost` INT DEFAULT NULL COMMENT "what we paid (each) for these particular items in stock",
`CatNum` varchar(63) DEFAULT NULL COMMENT "catalog number on tag (official cat# can change over time)",
`Notes` TEXT,
PRIMARY KEY(`ID`)
) ENGINE = MYISAM;</mysql>
<section end=sql />