VbzCart/tables/cat items

About

• Purpose: catalog items within a title, i.e. varieties in which the title is (or was ever) available
• Fields:
  • 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?
• 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"

SQL

<mysql> DROP TABLE IF EXISTS `cat_items`;

CREATE TABLE `cat_items` (
  `ID`                INT NOT NULL AUTO_INCREMENT,
  `CatNum`    VARCHAR(64) COMMENT "catalog number: must be unique or null; can be changed",
  `isForSale`        BOOL COMMENT "available to be sold/ordered (either in stock or in print)",
  `isMaster`         BOOL COMMENT "contains multiple items; split apart when moving into stock",
  `isInPrint`        BOOL COMMENT "item is in current supplier catalog, and should be orderable",
  `isCloseOut`       BOOL COMMENT "item is available as a close-out only; limited quantities, may be gone at any moment",
  `isCurrent`        BOOL COMMENT "item availability is current; FALSE = don't assume out of print",
  `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_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(7)  DEFAULT NULL COMMENT "sorting override (optional); sorting is by GrpSort+GrpCode",
  `CatSfx`      VARCAHR(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",
  `QtyMin_Stk`          INT COMMENT "minimum quantity to keep in stock",
  PRIMARY KEY(`ID`)
)
ENGINE = MYISAM;</mysql>