VbzCart/tables/cat items

About

• Purpose: catalog items within a title, i.e. varieties in which the title is (or was ever) available
• Subpages: /integrity checks

Fields

• ID_Supp ties the item directly back to its supplier. This should be set when new items are created during catalog building, and also when new "scratch" items are created during invoice entry.
• CatSfx is basically whatever sets the item's catalog number apart from other items of the same title
• GrpCode and GrpDescr are for differentiating within an item type, e.g. a short-sleeved t-shirt which is available in both tie-dye and one or more solid colors. GrpCode becomes part of the catalog number for items in that group, and GrpDesc is the description for that group. When displaying items for a particular title, sorting should be GrpSort,GrpCode,ItOptSort. See VbzCart catalog building for more.
• isCurrent: If FALSE, display of item status should not make any assumptions about the correctness of the "isInPrint" flag. Instead of "out of print", display "availability unknown"; instead of "can probably get more", display "was available when last checked".
  • TO DO: How can we derive the date of the latest catalog showing this as available?
• isInPrint: FALSE = even if this item is available, it is no longer being printed
• isPulled: TRUE = the record is being retired, but isn't ready to be recycled yet. This gives us a chance to see if the pulling was done in error. The item will still show up in listings by title. Pulling typically happens when duplicate items are created by mistake. When trying to decide which of two identical records to pull, pull the higher-numbered one if there are no other considerations.
• isDumped: TRUE = the record is ready to be recycled.
• QtyIn_Stk is updated by the catalog building process; later on, we will want to have a finer-grained process for updating this so the entire catalog build doesn't have to be run every time an item moves in/out of stock
• cntCtgDup indicates which items were duplicates during the catalog building process. It may end up being unnecessary, but I put it in during debugging so I thought I should document it.
• Supp_CatNum: the supplier's catalog number for this item; used for entering packing lists
• SC_LastUpdate: when the supplier catalog record was last updated
• SC_DateActive: validity date of the current supplier catalog

Future

• isMaster, isAvail, isInPrint, isPulled, isDumped should be changed from BOOL to BIT.
• Notes should be provided by a more efficient sparse annotation system (to be written).

History

• 2009-04-10 Tentatively eliminating qtyInStock field (adding " OLD" to name) because (a) can't find any code that uses it, and (b) it really should be calculated dynamically, otherwise it's not reliable.
• 2009-11-19 Adding "isCurrent" field; removing "qtyInStock OLD"
• 2009-12-11 Added "cntCtgDup"; restoring "QtyIn_Stk" field (formerly "qtyInStock") because:
  • it is used by existing code for displaying catalog pages
  • it doesn't make sense to have isForSale (which is calculated from (qtyInStock > 0) || isInPrint) and not qtyInStock; either isForSale should be eliminated, or qtyInStock should be restored
  • when we start having frequent stock adjustments it may become inefficient to recalculate qtyInStock for every stock item every time there is a change, but by that time we can write code to optimize this procedure -- i.e. only adjust qtyInStock for items which have actually been moved in or out of stock.
• 2010-12-28 Added Descr field to allow adding item records directly from invoice data, without a title
• 2010-12-29 Added ID_Supp field for much the same reason (need to be able to tie new Items to a Supplier without knowing Title or Department)
• 2011-02-24 Extended Descr field from 63 chars to 127 chars because some descriptions ended up being longer (only by a few chars, though, which is good). Problem, though, in that the build process reported no errors; only MySQL Query Browser reported errors. WTF? Further testing needed, but no time now.
• 2016-01-17 Added Notes field because I needed to make notes on particular Items
• 2016-02-29
  • A week or two ago:
    • Deprecated isForSale (renamed it to isForSale_DONT_USE in the database).
    • Added isAvail to indicate item is available from Supplier. "FOR SALE" status is now isAvail OR is in stock. Stock presence is calculated dynamically.
  • Today:
    • Deprecated Descr (renamed it to Descr_DONT_USE in the database; removed from doc page).
    • Removed isForSale from doc page.
• 2016-03-01
  • Added WhenCreated, WhenUpdated. Despite how promising this sounds, there can be only one auto-stamped field in a table. I decided it was harder to be sure that updates were recorded than creations, so I made that one auto-stamped on update.
  • Changed engine from MYISAM to INNODB.
• 2017-06-18 Adding Supp_LastUpdate, Supp_CatDate
• 2017-06-20 More SC field changes:
  • Renaming Supp_LastUpdate -> SC_LastUpdate, Supp_CatDate -> SC_DateActive
  • Adding SC_DateExpires
  • Was tempted to rename Supp_CatNum to SC_CatNum, but decided against; it also appears on packing lists, not just catalogs
  • Removing cntCtgDup; not used in code, and 2017 building process does not set it.
  • Removing isCloseOut and isCurrent: both are made unnecessary by SC_* fields (and revisions to the building process)
  • Removing Descr and QtyIn_Stk: both are now dynamically calculated from other tables
• 2017-07-02 Changing length of GrpSort from 7 to 31 to match ctg_groups.Sort

SQL

CREATE TABLE `cat_items` (
  `ID`                INT NOT NULL AUTO_INCREMENT,
  `CatNum`    VARCHAR(64) COMMENT "catalog number: must be unique or null; can be changed",
  `isMaster`         BOOL COMMENT "contains multiple items; split apart when moving into stock",
  `isAvail`          BOOL COMMENT "available from the Supplier",
  `isInPrint`        BOOL COMMENT "item is in current supplier catalog, and should be orderable",
  `isPulled`         BOOL COMMENT "TRUE = record is garbage or a duplicate, and nothing should refer to it",
  `isDumped`         BOOL COMMENT "TRUE = pulled item which has been checked to make sure nobody is using it: ok to recycle",
  `ID_Supp`  INT NOT NULL COMMENT "cat_supp.ID",
  `ID_Title` INT NOT NULL COMMENT "cat_titles.ID",
  `ID_ItTyp`          INT COMMENT "cat_ittyps.ID: basic type of item",
  `ID_ItOpt`          INT COMMENT "cat_itopts.ID: item option (e.g. size)",
  `ItOpt_Descr` VARCHAR(63)  DEFAULT NULL COMMENT "overrides cat_itopts.Descr if present",
  `ItOpt_Sort`  VARCHAR(63)  DEFAULT NULL COMMENT "concatenation of IGroup, Option, and Item Type sorting indexes",
  `GrpCode`     VARCHAR(15)  DEFAULT NULL COMMENT "grouping code - new header when this changes",
  `GrpDescr`    VARCHAR(63)  DEFAULT NULL COMMENT "group description - show in header, add to item description as shown in cart",
  `GrpSort`     VARCHAR(31)  DEFAULT NULL COMMENT "sorting override (optional); sorting is by GrpSort+GrpCode",
  `CatSfx`      VARCHAR(31)  DEFAULT NULL COMMENT "whatever is added to the title's catnum to make the item's catnum",
  `ID_ShipCost`         INT  COMMENT "applicable shipping cost calculation data",
  `PriceBuy`   DECIMAL(9,2)  COMMENT "wholesale price from supplier",
  `PriceSell`  DECIMAL(9,2)  COMMENT "price to customer",
  `PriceList`  DECIMAL(9,2)  COMMENT "supplier's retail list price (if available)",
  `Supp_CatNum` VARCHAR(32)  COMMENT "supplier's catalog number, if available",
  `SC_DateActive`  DATE     DEFAULT NULL,
  `SC_LastUpdate`  DATETIME DEFAULT NULL,
  `SC_DateExpires` DATE     DEFAULT NULL,
  `QtyMin_Stk`          INT  COMMENT "minimum quantity to keep in stock",
  `WhenCreated`    DATETIME NOT NULL "set this to NOW() when creating new records",
  `WhenUpdated`   TIMESTAMP NULL ON UPDATE NOW() COMMENT "automatically updated when record changes",
  `Notes`              TEXT  COMMENT "human-entered notes for admin use only",
  PRIMARY KEY(`ID`)
)
ENGINE = InnoDB;