VbzCart/tables

From HTYP, the free directory anyone can edit

Jump to: navigation, search

Contents

[edit] Navigation

computing: software: web: shopping carts: VbzCart: tables

[edit] Subpages

[edit] Tables

  • "#" indicates Primary Key fields
  • "@" indicates autonumbered fields

[edit] Catalog

These tables describe and price the items displayed in the catalog pages and in shopping carts.

A "Title" is a group of items with a common description, e.g. different sizes or styles of a shirt, different media (CD, cassette) for an audio recording.

In the previous version of the cart software, we had to have items of somewhat different appearance (e.g. longsleeve and shortsleeve shirts) sharing a single title, so as to remove the necessity to always have pictures for each. In this version, a title can point to another title for its picture, thus keeping it clear whether the picture is truly representative or just an approximation.

  • /cat_supp: catalog suppliers (i.e. manufacturers, wholesalers)
  • /cat_depts: catalog departments within a supplier
  • /cat_titles: titles within a department - a particular "thing" which may be available in multiple varieties
  • /cat_items: items within a title - a particular version of a title
  • /cat_ittyps: item types - every item has one, but they are often all the same
  • /cat_ioptns: these typically distinguish items within a title
  • /cat_images: image data; so far, we only keep track of images by title
  • /cat_ship_costs: shipping costs for different items
  • /cat_pages: mapping URLs to various catalog entities (suppliers, depts, titles)
  • /cat_pages_old: catalog designations sometimes change; where possible, this lets us redirect old URLs

[edit] future

It may be that Departments and Suppliers should be handled by the Topics tree, but this involves creating some infrastructure which is going to take some doing. (Specifically, we need to be able to assign named values – like "catkey" – to any topic, so that each Department topic and Supplier topic can refer to the proper catalog number.)

[edit] leftover stuff

Are these still being used? To be checked. 2008-12-06

SQL for updating availability from current stock: 

  CREATE PROCEDURE Upd_CatItems_fr_StkItems_StkBins()
    UPDATE cat_items AS i LEFT JOIN (
      SELECT
        si.ID_Item,
        SUM(si.Qty) AS QtyInStock
       FROM stk_items AS si LEFT JOIN stk_bins AS sb ON si.ID_Bin=sb.ID
       WHERE (sb.isForSale) AND ISNULL(sb.WhenVoided) AND ISNULL(si.WhenRemoved)
       GROUP BY ID_Item
     ) AS sig ON i.ID=sig.ID_Item
       SET
         i.QtyInStock = sig.QtyInStock,
         i.isForSale = i.isInPrint OR (sig.QtyInStock > 0);


CREATE OR REPLACE VIEW _cat_pages AS
  SELECT
    CONCAT_WS('-','S',ID) AS AB,
    LOWER(CatKey) AS Path,
    ID,
    'S' AS Type
  FROM cat_supp
  UNION
  SELECT
    CONCAT_WS('-','D',ID) AS AB,
    LOWER(CatWeb_Dept) AS Path,
    ID,
    'D' AS Type
  FROM qryCat_Depts
  UNION
  SELECT
    CONCAT_WS('-','T',ID) AS AB,
    REPLACE(LOWER(CatWeb),'-','/') AS Path,
    ID,
    'T' AS Type
  FROM qryCat_Titles
  UNION
  /* image pages (every size except thumbnail and small) */
  SELECT
    CONCAT_WS('-','I',i.ID) AS AB,
    LOWER(CONCAT_WS('/',REPLACE(t.CatWeb,'-','/'),AttrFldr,i.Ab_Size)) AS Path,
    i.ID,
    'I' AS Type
  FROM cat_images AS i LEFT JOIN qryCat_Titles AS t ON i.ID_Title=t.ID WHERE (i.isActive) AND (i.Ab_Size NOT IN ('th','sm'));


CREATE PROCEDURE Upd_CatPages()
  REPLACE INTO cat_pages
  SELECT * FROM _cat_pages
  ORDER BY Path;


This --

  • LOWER(CONCAT_WS('/',REPLACE(t.CatWeb,'-','/'),AttrFldr,i.Ab_Size)) AS Path,

...is intended to be a simplified version of this:

  • LOWER(CONCAT(REPLACE(t.CatWeb,'-','/'),'/',CONCAT_WS('/',AttrFldr,i.Ab_Size))) AS Path,

(saved here in case the simplification doesn't work)

[edit] Catalog Entry

These are tables used to make it easier to update the catalog. The key concept here is "catalog title groups" (CTGs). A CTG is a set of titles which are all available with the same set of options (e.g. sizes) at the same prices (e.g. $10 S, $11 M-XL, $12 2XL). The final vbz catalog (cat_items) is the result of a sort of vector multiplication of Titles (cat_titles) and CTGs plus any items in stock.

A "source" is usually a printed catalog from a supplier, though it can also be the manufacturer's web site on a particular date.

See VbzCart catalog building for queries and processes.

[edit] ctg_supp

Information about suppliers which isn't needed for customer web site 

CREATE TABLE `ctg_supp` (
   ID INT NOT NULL COMMENT "cat_supp.ID - supplier to which information applies",
   ID_PriceFunc INT DEFAULT NULL COMMENT "ctg_prc_funcs.ID - function to use when calculating prices for this supplier",
  PRIMARY KEY(`ID`)
)
ENGINE = MYISAM;

[edit] ctg_prc_funcs

Functions for calculating our retail price from the wholesale cost we pay:

retail = {(wholesale * PriceFactor) + PriceAddend} rounded up to next PriceRound 
CREATE TABLE `ctg_prc_funcs` (
  ID          INT NOT NULL AUTO_INCREMENT,
  Name        VARCHAR(63) NOT NULL COMMENT "descriptive name for this price function",
  PriceFactor DECIMAL(9,2) COMMENT "amount by which to multiple wholesale cost",
  PriceAddend DECIMAL(9,2) COMMENT "amount by which to pad wholesale cost",
  PriceRound  DECIMAL(9,2) COMMENT "round-up-to-next this amount",
  Notes       VARCHAR(255) COMMENT "usage notes etc.",
  PRIMARY KEY(`ID`)
)
ENGINE = MYISAM;


[edit] ctg_sources

CREATE TABLE `ctg_sources` (
  ID          INT NOT NULL AUTO_INCREMENT,
  Name        VARCHAR(63) NOT NULL COMMENT "name of source",
  Abbr        VARCHAR(15) NOT NULL COMMENT "abbreviation, for drop-down lists",
  ID_Supplier INT NOT NULL COMMENT "Suppliers.ID - supplier for this source",
  DateAvail   DATETIME DEFAULT NULL COMMENT "earliest date on which this source is valid",
  ID_Supercede INT DEFAULT NULL COMMENT "[Supplier Catalogs].ID of catalog which supercedes this one (NULL = this one is current)",
  isCloseOut  BOOL DEFAULT FALSE COMMENT "TRUE = this catalog is a list of discontinued items no longer in print (closeouts); FALSE = normal catalog",
  PRIMARY KEY(`ID`)
)
ENGINE = MYISAM;
  • To discontinue a supplier, set ID_Supercede=ID for all active sources.

[edit] ctg_groups

Master list of groups (CTGs) 

CREATE TABLE `ctg_groups` (
  ID          INT NOT NULL AUTO_INCREMENT,
  ID_Supplier INT DEFAULT NULL COMMENT "Suppliers.ID -- supplier to which this group applies (NULL = no fixed supplier)",
  Name        VARCHAR(63) DEFAULT NULL COMMENT "Name of this group (brief)",
  Descr       VARCHAR(127) DEFAULT NULL COMMENT "Description (for display; if NULL, use ItemType fields)",
  isActive    BOOL DEFAULT FALSE,
  Code        VARCHAR(7) DEFAULT NULL COMMENT "suffix for Items.CatNum",
  Sort        VARCHAR(31) DEFAULT NULL COMMENT "sorting key within group list",
  ID_Redir    INT DEFAULT NULL COMMENT "TGroups.ID -- if not null, redirect all references to the current ID to use ID_Redir instead",
PRIMARY KEY(`ID`)
)
ENGINE = MYISAM;

[edit] ctg_titles

Specifies which groups a title belongs to 

CREATE TABLE `ctg_titles` (
  ID          INT NOT NULL AUTO_INCREMENT,
  ID_Title    INT DEFAULT NULL COMMENT "cat_titles.ID",
  ID_Group    INT DEFAULT NULL COMMENT "ctg_groups.ID",
  ID_Source  INT DEFAULT NULL COMMENT "ctg_sources.ID - source which enables this selection",
  WhenDiscont DATETIME DEFAULT NULL COMMENT "non-sourced discontinuation",
  GroupCode   VARCHAR(7) DEFAULT NULL COMMENT "catalog code extension for this title group (optional)",
  GroupDescr  VARCHAR(127) DEFAULT NULL COMMENT "description extension for this title group (required if GroupCode is used)",
  GroupSort   VARCHAR(7) DEFAULT NULL COMMENT "sorting override",
  isActive    BOOL DEFAULT FALSE COMMENT "this group-title membership is active?",
  Supp_CatNum VARCHAR(15) DEFAULT NULL COMMENT "catalog # for restock from supplier",
  Notes       VARCHAR(255) DEFAULT NULL COMMENT "notes about this particular title's availability in this group",
  PRIMARY KEY(`ID`)
)
ENGINE = MYISAM;

[edit] ctg_items

Specifies which item varieties/prices are available in a group 

CREATE TABLE `ctg_items` (
  ID             INT NOT NULL AUTO_INCREMENT,
  ID_Group       INT NOT NULL COMMENT "ctg_groups.ID",
  ID_ItTyp       INT NOT NULL COMMENT "cat_ittyps.ID -- basic item type",
  ID_ItOpt       INT DEFAULT NULL COMMENT "cat_itopts.ID -- option for this item",
  Descr          VARCHAR(63) COMMENT "name or description for this item/option",
  PriceBuy       DECIMAL(9,2) DEFAULT NULL COMMENT "cost to us",
  PriceSell      DECIMAL(9,2) DEFAULT NULL COMMENT "our price to retail customer",
  PriceList      DECIMAL(9,2) DEFAULT NULL COMMENT "supplier's suggested retail price",
  ID_ShipCost    INT DEFAULT NULL COMMENT "cat_ship_costs.ID",
  isActive       BOOL DEFAULT FALSE COMMENT "this group/type/option is available",
  PRIMARY KEY(`ID`)
)
ENGINE = MYISAM;

[edit] ctg_updates

There may be a better way to do this, but haven't been able to figure it out yet. This table keeps track of the correspondence between {items as generated from the catalog title group data} and {items in cat_items}. Off the top of my head, I can't remember why we need to keep more fields than just ID_Item and IDS_Item, but I did look at this table very closely awhile back and ended up with all those extra fields still in it. TO DO: document why they're needed. 

CREATE TABLE `ctg_updates` (
  ID_Item        INT NOT NULL AUTO_INCREMENT COMMENT "cat_items.ID",
  IDS_Item       VARCHAR(63) NOT NULL UNIQUE COMMENT "cat_titles.ID + CatSfx",
  isActive       BOOL NOT NULL DEFAULT FALSE COMMENT "current sources do generate a record for this item",
  CatSfx         VARCHAR(31) DEFAULT NULL COMMENT "suffix which uniquely identifies this item's genome, so to speak, within all items for the same title",
  CatNum         VARCHAR(63) NOT NULL COMMENT "CatNum = Title.CatNum + CatSfx",
  ID_Title       INT DEFAULT NULL,
  ID_CTG_Group   INT DEFAULT NULL,
  ID_CTG_Title   INT DEFAULT NULL,
  ID_CTG_Item    INT DEFAULT NULL,
  ID_ItTyp       INT DEFAULT NULL COMMENT "needed for join with cat_items, even though it comes from ID_CTG_Item",
  ID_ItOpt       INT DEFAULT NULL COMMENT "needed for join with cat_items, even though it comes from ID_CTG_Item",
  PRIMARY KEY(`IDS_Item`),
  INDEX(`ID_Item`),
  INDEX(`CatNum`)
)
ENGINE = MYISAM;
  • isActive is necessary because otherwise there was no way to convey that a formerly-existing item is no longer active; while it should be possible to pull this information back out from qryCtg_Items_updates_joinable when updating cat_items from ctg_updates, the necessary join (showing all items in cat_items not found in qryCtg_Items_updates_joinable) seems to take a prohibitively long time to run. Perhaps this can be optimized later, which would also eliminate the need for most of the fields in ctg_updates.

[edit] ctg_upd1

Experimental, but hopefully will replace ctg_updates which never worked entirely properly. This table contains only the essential fields from qryCtg_src. 

CREATE TABLE `ctg_upd1` (
  CatSfx            VARCHAR(31),
  isCloseOut        BOOL,
  ID_CTG_Title      INT,
  ID_CTG_Item       INT,
  ID_Title          INT,
  ID_ItTyp          INT,
  ID_ItOpt          INT,
  ID_ShipCost       INT,
  PriceBuy          DECIMAL(9,2),
  PriceSell         DECIMAL(9,2),
  PriceList         DECIMAL(9,2),
  ItOpt_Descr_part  VARCHAR(63),
  NameSng           VARCHAR(63),
  GrpItmDescr       VARCHAR(63),
  TitleGroupDescr   VARCHAR(63),
  OptionDescr       VARCHAR(63),
  ItOpt_Sort        VARCHAR(31),
  GrpCode           VARCHAR(15),
  GrpDescr          VARCHAR(31),
  GrpSort           VARCHAR(31),
  IDS_Item          VARCHAR(31),
  CatNum            VARCHAR(31),
  ItOpt_Descr       VARCHAR(63),
  PRIMARY KEY(`IDS_Item`)
)
ENGINE = MYISAM;


[edit] ctg_upd2

Experimental, but hopefully will replace ctg_updates which never worked entirely properly. 

CREATE TABLE `ctg_upd2` (
  ID_Item        INT DEFAULT NULL            COMMENT "cat_items.ID",
  IDS_Item       VARCHAR(63) NOT NULL UNIQUE COMMENT "cat_titles.ID + CatSfx",
  isForSale      BOOL DEFAULT FALSE          COMMENT "to be recalculated (assume false, then set true if available or in stock)",
  QtyInStock     INT DEFAULT NULL            COMMENT "to be recalculated",
  Supp_CatNum    VARCHAR(32)                 COMMENT "preserved from cat_items",
  QtyMin_Stk     INT DEFAULT NULL            COMMENT "preserved from cat_items",
  cntDups        INT DEFAULT 0,
  PRIMARY KEY(`IDS_Item`),
  UNIQUE KEY(`ID_Item`)
)
ENGINE = MYISAM;


[edit] Ordering

The items a customer wants to order are saved in a cart. The cart also saves session information, e.g. customer's IP/domain, but not the customer's shipping or payment data. Later on, we'll allow customers to make changes to carts after the cart has already been assigned to an order, so will need session info stored separately, which is why it's in a separate table. See VbzCart ordering for details.

There is a handful of tables just for managing customer contact information, so I'm putting those on a separate page: VbzCart customer tables

[edit] Restocking

[edit] obsolete tables

[edit] Shopping

These tables store data generated by the user during the shopping process.

[edit] shop_carts

Shopping cart data is kept separate from order data because we end up with a lot of carts that never become orders; eventually they get cleaned out. Order data may eventually get cleaned out too, but with different criteria; for now, we are keeping order data indefinitely. 

DROP TABLE IF EXISTS `shop_carts`;
CREATE TABLE `shop_carts` (
  `ID`         INT NOT NULL AUTO_INCREMENT,
  `ID_Session` INT DEFAULT NULL       COMMENT "shop_sessions.ID, when implemented -- session in which cart was created",
  `WhenCreated` DATETIME NOT NULL     COMMENT "when the cart was first created",
  `WhenViewed`  DATETIME DEFAULT NULL COMMENT "when the cart's contents were last displayed by the customer",
  `WhenUpdated` DATETIME DEFAULT NULL COMMENT "when the cart's contents were last changed",
  `WhenOrdered` DATETIME DEFAULT NULL COMMENT "when the cart's contents were transferred to an order",
  `ID_Order`   INT DEFAULT NULL       COMMENT "core_orders.ID of order into which cart was transferred",
  `ID_Cust`    INT DEFAULT NULL       COMMENT "core_custs.ID of customer for this order",
   PRIMARY KEY(`ID`)
 ) ENGINE = MYISAM;
  • ID_Cust is for future use when customers can log in to retrieve their personal data
  • ID_Session is for future use when sessions are tracked

[edit] shop_cart_lines

DROP TABLE IF EXISTS `shop_cart_lines`;
CREATE TABLE `shop_cart_lines` (
  `ID`         INT NOT NULL AUTO_INCREMENT,
  `ID_Cart`    INT NOT NULL COMMENT "shop_carts.ID",
  `ID_Item`    INT NOT NULL COMMENT "cat_items.ID",
  `Qty`        INT NOT NULL COMMENT "quantity ordered; 0 = removed from order",
  `WhenAdded`  DATETIME NOT NULL COMMENT "when this item was first added to the order",
  `WhenEdited` DATETIME NOT NULL COMMENT "when the quantity for this item was last changed",
  `PriceItem`  DECIMAL(9,2) NOT NULL COMMENT "price of item quoted at order time",
  `PriceShItm` DECIMAL(9,2) NOT NULL COMMENT "per-item shipping quoted for this item at order time",
  `PriceShPkg` DECIMAL(9,2) NOT NULL COMMENT "per-package shipping quoted for this item at order time",
/* These fields are redundant, and are used mainly to speed up cart display. They also preserve a record of
  how the item was presented to the customer. */
  `CatNum`    VARCHAR(63) COMMENT "cat_items.CatNum of item as sold",
  `ID_Title`  INT NOT NULL COMMENT "cat_titles.ID",
  `DescText`  VARCHAR(255) NOT NULL COMMENT "plain-text description of item being ordered (e.g. for emails)",
  `DescHtml`  VARCHAR(255) NOT NULL COMMENT "HTML description of item being ordered, for web-page display",
  PRIMARY KEY(`ID`)
 ) ENGINE = MYISAM;

[edit] shop_cust_data

This is a simple table for retaining customer data entered at check-out time before converting the cart to an order. 

DROP TABLE IF EXISTS `shop_cust_data`;
CREATE TABLE `shop_cust_data` (
  `ID`         INT NOT NULL AUTO_INCREMENT,
  `ID_Cart`    INT NOT NULL COMMENT "shop_carts.ID",
  `ID_Key`     INT NOT NULL COMMENT "shop_cust_data_types.ID of data type",
  `Val`        VARCHAR(255) NOT NULL COMMENT "Value of data item",
  PRIMARY KEY(`ID`)
 ) ENGINE = MYISAM;

[edit] shop_cust_data_types

This table is probably unnecessary, but I can't convince myself of that. If nothing else, it will help prevent inconsistent usage. 

DROP TABLE IF EXISTS `shop_cust_data_types`;
CREATE TABLE `shop_cust_data_types` (
  `ID`         INT NOT NULL AUTO_INCREMENT,
  `Name`       VARCHAR(31) NOT NULL COMMENT "Short name, for reports or whatever",
  `Descr`      VARCHAR(255) DEFAULT NULL COMMENT "Longer description of data item, e.g. usage notes",
  PRIMARY KEY(`ID`)
 ) ENGINE = MYISAM;

[edit] shop_log

Under construction 

DROP TABLE IF EXISTS `shop_log`;
CREATE TABLE `shop_log` (
  `ID`         INT      NOT NULL AUTO_INCREMENT,
  `ID_Client`  INT      NOT NULL COMMENT "shop_clients.ID",
  `EvWhen`     DATETIME NOT NULL COMMENT "When this event occurred",
  `Code`       INT      NOT NULL COMMENT "type of event",
  `Descr`      VARCHAR(255) DEFAULT NULL COMMENT "Any details specific to this occurrence",
  `Notes`      VARCHAR(255) DEFAULT NULL COMMENT "Human-entered notes",
  PRIMARY KEY(`ID`)
 ) ENGINE = MYISAM;

[edit] shop_clients

Under construction 

DROP TABLE IF EXISTS `shop_clients`;
CREATE TABLE `shop_clients` (
  `ID`         INT NOT NULL AUTO_INCREMENT,
  `CRC`        INT UNSIGNED NOT NULL COMMENT "crc32(Address+Browser) - unique integer defined by client specs",
  `Address`    VARCHAR(15) NOT NULL COMMENT "IP address of client",
  `Domain`     VARCHAR(255) COMMENT "Domain name of client",
  `Browser`    VARCHAR(255) COMMENT "Browser USER_AGENT string",
  `WhenFirst`  DATETIME NOT NULL COMMENT "When this client was first seen",
  `WhenFinal`  DATETIME COMMENT "When this client was most recently seen",
  PRIMARY KEY(`ID`),
  UNIQUE KEY(`CRC`)
 ) ENGINE = MYISAM;
  • Decided to use CRC32 instead of compression, because the compressed strings always came out at least as long as the original string. They were probably designed to be effective for rather longer data.

[edit] shop_sessions

To be implemented. A new session should be created whenever the "same" client doesn't remember the session cookie (could be a different computer on the same IP address), or if the session has timed out.

[edit] Stock Management

[edit] Browsing

Topics and images

[edit] brs_topics

DROP TABLE IF EXISTS `brs_topics`;
CREATE TABLE `brs_topics` (
  `ID`        INT          NOT NULL AUTO_INCREMENT,
  `ID_Parent` INT          DEFAULT NULL COMMENT "brs_topics.ID of parent topic",
  `Name`      VARCHAR(128) NOT NULL     COMMENT "generic name (context-free, but as short as possible)",
  `NameTree`  VARCHAR(64)  DEFAULT NULL COMMENT "name within context of parent; defaults to Name",
  `NameFull`  VARCHAR(255) DEFAULT NULL COMMENT "descriptive standalone name (context free, can be long)",
  `Sort`      VARCHAR(15)  DEFAULT NULL COMMENT "optional sorting key",
  `Variants`  VARCHAR(255) DEFAULT NULL COMMENT "synonyms and other keywords which should find this topic",
  `Mispeled`  VARCHAR(255) DEFAULT NULL COMMENT "same as Variants, but these are WRONG spellings; avoid displaying",
  PRIMARY KEY(`ID`)
) ENGINE = MYISAM;


[edit] brs_titles_x_topics

DROP TABLE IF EXISTS `brs_titles_x_topics`;
CREATE TABLE `brs_titles_x_topics` (
  `ID_Title` INT NOT NULL COMMENT "cat_titles.ID",
  `ID_Topic` INT NOT NULL COMMENT "brs_topics.ID",
  PRIMARY KEY(`ID_Title`,`ID_Topic`)
) ENGINE = MYISAM;


[edit] Miscellaneous

  • stats – computed variables, cache timestamps, and other variable stuff 
 CREATE TABLE `stats` (
    `Name` VARCHAR(32) NOT NULL COMMENT 'name of variable',
    `Value` VARCHAR(255) COMMENT 'value of variable',
   PRIMARY KEY(`Name`)
 ) ENGINE = MYISAM;


  • updates – timestamps on tables so we know when update queries need to be run 
 CREATE TABLE `updates` (
    `Name` VARCHAR(32) NOT NULL COMMENT 'name of thing which gets updated',
    `Updated` DATETIME COMMENT 'when last updated',
   PRIMARY KEY(`Name`)
 ) ENGINE = MYISAM;


Function to record an update: 

 CREATE PROCEDURE SetUpdate(IN iName VARCHAR(32))
 BEGIN
   REPLACE INTO updates(Name,Updated) VALUES(iName, NOW());
 END


[edit] Calculated Tables

[edit] _depts

 DROP TABLE IF EXISTS `_depts`;
 CREATE TABLE `_depts` (
   `ID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
   `ID_Supp` INT(11) UNSIGNED NOT NULL COMMENT 'needed to simplify calculation of _titles',
   `CatNum` VARCHAR(63) NOT NULL,
   `CatKey` VARCHAR(7),
   `CatWeb_Dept` VARCHAR(63) NOT NULL,
   `CatWeb_Title` VARCHAR(63) NOT NULL,
   `cntForSale` INT DEFAULT NULL,
   `cntInPrint` INT DEFAULT NULL,
   `qtyInStock` INT DEFAULT NULL,
   PRIMARY KEY (`ID`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

[edit] ← depts x suppliers

SQL to fill _depts (except inPrint/inStock fields): 

  CREATE PROCEDURE Upd_Depts_fr_Depts_Suppliers()
    BEGIN
      DELETE FROM _depts;
      INSERT INTO _depts(`ID`,`ID_Supp`,`CatKey`,`CatNum`,`CatWeb_Dept`,`CatWeb_Title`)
        SELECT
          d.ID,
          d.ID_Supplier AS ID_Supp,
          UPPER(IFNULL(d.PageKey,d.CatKey)) AS CatKey,
          UPPER(CONCAT_WS("-",s.CatKey,d.CatKey)) AS CatNum,
          LOWER(CONCAT_WS("/",s.CatKey,IFNULL(d.PageKey,d.CatKey))) AS CatWeb_Dept,
          LOWER(CONCAT_WS("/",s.CatKey,d.CatKey)) AS CatWeb_Title
        FROM depts AS d LEFT JOIN suppliers AS s ON d.ID_Supplier=s.ID;
    END

[edit] ← _dept_ittyps by ID_Dept

  CREATE PROCEDURE Upd_Depts_fr_DeptIttyps()
    UPDATE _depts AS d LEFT JOIN (
      SELECT
        ID_Dept,
        SUM(di.cntForSale) AS cntForSale,
        SUM(di.cntInPrint) AS cntInPrint,
        SUM(di.qtyInStock) AS qtyInStock
      FROM _dept_ittyps AS di GROUP BY ID_Dept
      ) AS di ON di.ID_Dept=d.ID
      SET
        d.cntForSale = di.cntForSale,
        d.cntInPrint = di.cntInPrint,
        d.qtyInStock = di.qtyInStock;

[edit] _titles

 DROP TABLE IF EXISTS `_titles`;
 CREATE TABLE `_titles` (
   `ID` INT(11) UNSIGNED NOT NULL,
   `ID_Supp` INT(11) UNSIGNED NOT NULL COMMENT "avoids need for joining with cat_depts",
   `CatNum` VARCHAR(63) NOT NULL,
   `CatWeb` VARCHAR(63) NOT NULL,
   `cntForSale` INT DEFAULT NULL,
   `cntInPrint` INT DEFAULT NULL,
   `qtyInStock` INT DEFAULT NULL,
   `currMinSell` DECIMAL(9,2) DEFAULT NULL COMMENT "minimum selling price for an item of this title",
   `currMaxSell` DECIMAL(9,2) DEFAULT NULL COMMENT "maximum selling price for an item of this title",
   PRIMARY KEY (`ID`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

[edit] ← titles x _depts 

  CREATE PROCEDURE Upd_Titles_fr_Depts()
    REPLACE INTO _titles(ID,ID_Supp,CatNum,CatWeb)
    SELECT
      t.ID,
      d.ID_Supp,
      UPPER(CONCAT_WS("-",d.CatNum,t.CatKey)) AS CatNum,
      LOWER(CONCAT_WS("/",d.CatWeb_Title,t.CatKey)) AS CatWeb
    FROM titles AS t LEFT JOIN _depts AS d ON t.ID_dept=d.ID
    ORDER BY CatNum;

[edit] ← cat_items (grouped by ID_Title) 

  CREATE PROCEDURE Upd_Titles_fr_CatItems()
    UPDATE _titles AS t LEFT JOIN (
      SELECT
        ID_Title,
        SUM(IF(i.isForSale,1,0)) AS cntForSale,
        SUM(IF(i.isInPrint,1,0)) AS cntInPrint,
        SUM(i.qtyInStock) AS qtyInStock,
        MIN(i.PriceSell) AS currMinSell,
        MAX(i.PriceSell) AS currMaxSell
       FROM cat_items AS i GROUP BY ID_Title
      ) AS ig ON ig.ID_Title=t.ID
      SET
        t.cntForSale = ig.cntForSale,
        t.cntInPrint = ig.cntInPrint,
        t.qtyInStock = ig.qtyInStock;


[edit] ← _title_ittyps (grouped by ID_Title)

  CREATE PROCEDURE Upd_Titles_fr_TitleIttyps()
      UPDATE _titles AS t LEFT JOIN (
        SELECT
          ID_Title,
          SUM(ti.cntForSale) AS cntForSale,
          SUM(ti.cntInPrint) AS cntInPrint,
          SUM(ti.qtyInStock) AS qtyInStock
         FROM _title_ittyps AS ti GROUP BY ID_Title
        ) AS ti ON ti.ID_Title=t.ID
        SET
          t.cntForSale = ti.cntForSale,
          t.cntInPrint = ti.cntInPrint,
          t.qtyInStock = ti.qtyInStock;


[edit] _title_ittyps

list of available titles for each item type 

 DROP TABLE IF EXISTS `_title_ittyps`;
 CREATE TABLE `_title_ittyps` (
   `ID_Title` INT UNSIGNED NOT NULL,
   `ID_ItTyp` INT UNSIGNED NOT NULL,
   `ID_Dept` INT UNSIGNED NOT NULL,
   `TitleName` VARCHAR(127),
   `ItTypNameSng` VARCHAR(63) COMMENT "cat_ittyps.NameSng",
   `ItTypNamePlr` VARCHAR(63) COMMENT "cat_ittyps.NamePlr",
   `ItTypSort` VARCHAR(31) COMMENT "cat_ittyps.Sort",
   `cntForSale` INT COMMENT "# of different items available for type (either in stock or in print)",
   `cntInPrint` INT COMMENT "# of different items (for type) in print for this title",
   `cntInStock` INT COMMENT "# of different items (for type) which are for sale due to at least being in stock",
   `qtyInStock` INT COMMENT "-1 = some in stock, but don't know how many",
   `currMinPrice` DECIMAL(9,2) DEFAULT NULL COMMENT 'minimum price for this item type',
   `currMaxPrice` DECIMAL(9,2) DEFAULT NULL COMMENT 'maximum price for this item type',
   `CatNum` VARCHAR(63) DEFAULT NULL,
   `CatWeb` VARCHAR(63) DEFAULT NULL,
   `CatDir` VARCHAR(63) DEFAULT NULL,
   PRIMARY KEY (`ID_Title`,`ID_ItTyp`,`ID_Dept`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;


  • 2007-09-20 Added cntInStock field because we need to be able to figure out how many line items are available but not in stock (cntForSale-cntInStock)

[edit] ← cat_items (grouped by ID_Title)

This procedure initially fills the table. (This is a revised version which combines two earlier procedures.) (I had a note that "the ID_Dept field is informational, not a grouping", but I don't remember what that meant and it doesn't make sense.) 

  CREATE PROCEDURE Upd_TitleIttyps_fr_CatItems_Titles()
    REPLACE INTO _title_ittyps(
      ID_Title,
      ID_ItTyp,
      ID_Dept,
      TitleName,
      cntForSale,
      cntInPrint,
      cntInStock,
      qtyInStock,
      currMinPrice,
      currMaxPrice)
    SELECT
      i.ID_Title,
      i.ID_ItTyp,
      t.ID_Dept,
      t.Name AS TitleName,
      i.cntForSale,
      i.cntInPrint,
      i.cntInStock,
      i.qtyInStock,
      i.currMinPrice,
      i.currMaxPrice
     FROM (
       SELECT
         ID_Title, ID_ItTyp,
         SUM(IF(isForSale,1,0)) AS cntForSale,
         SUM(IF(isInPrint,1,0)) AS cntInPrint,
         SUM(IF(qtyInStock>0,1,0)) AS cntInStock,
         SUM(qtyInStock) AS qtyInStock,
         MIN(PriceSell) AS currMinPrice,
         MAX(PriceSell) AS currMaxPrice
       FROM cat_items AS i
       GROUP BY ID_Title, ID_ItTyp
     ) AS i LEFT JOIN titles AS t ON i.ID_Title=t.ID;
  • 2007-09-20
    • Removed "WHERE i.isForSale" because this prevented the availability of discontinued items from being properly zeroed out
    • Added cntInStock calculation; see table schema

[edit] ← _titles 

  CREATE PROCEDURE Upd_TitleIttyps_fr_Titles()
    UPDATE _title_ittyps AS ti LEFT JOIN _titles AS t ON t.ID=ti.ID_Title 
      SET 
        ti.CatNum = t.CatNum,
        ti.CatWeb = t.CatWeb;

[edit] ← cat_ittyps 

  CREATE PROCEDURE Upd_TitleIttyps_fr_CatIttyps()
      UPDATE _title_ittyps AS ti LEFT JOIN cat_ittyps AS it ON ti.ID_ItTyp=it.ID
        SET
          ti.ItTypNameSng = it.NameSng,
          ti.ItTypNamePlr = it.NamePlr,
          ti.ItTypSort = it.Sort;


[edit] _dept_ittyps

 DROP TABLE IF EXISTS `_dept_ittyps`;
 CREATE TABLE `_dept_ittyps` (
   `ID_ItTyp` INT UNSIGNED NOT NULL,
   `ID_Dept` INT UNSIGNED NOT NULL,
   `cntForSale` INT UNSIGNED NOT NULL,
   `cntInPrint` INT UNSIGNED NOT NULL,
   `qtyInStock` INT UNSIGNED NOT NULL,
   `ItTypNameSng` VARCHAR(64) DEFAULT NULL COMMENT 'cat_ittyps.NameSng',
   `ItTypNamePlr` VARCHAR(64) DEFAULT NULL COMMENT 'cat_ittyps.NamePlr; default to ItTypNameSng',
   PRIMARY KEY (`ID_ItTyp`,`ID_Dept`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

[edit] ← _title_ittyps

  CREATE PROCEDURE Upd_DeptIttyps_fr_TitleIttyps()
    REPLACE INTO _dept_ittyps(ID_ItTyp,ID_Dept,cntForSale,cntInPrint,qtyInStock)
    SELECT
      ID_ItTyp,
      ID_Dept,
      SUM(cntForSale) AS cntForSale,
      SUM(cntInPrint) AS cntInPrint,
      SUM(qtyInStock) AS qtyInStock
    FROM _title_ittyps
    GROUP BY ID_ItTyp, ID_Dept;

[edit] ← cat_ittyps

  CREATE PROCEDURE Upd_DeptIttyps_fr_CatIttyps()
    UPDATE _dept_ittyps AS di LEFT JOIN cat_ittyps AS it ON it.ID=di.ID_ItTyp
      SET
        di.ItTypNameSng = it.NameSng,
        di.ItTypNamePlr = IFNULL(it.NamePlr,it.NameSng);


[edit] _supplier_ittyps

 DROP TABLE IF EXISTS `_supplier_ittyps`;
 CREATE TABLE `_supplier_ittyps` (
   `ID` INT(11) UNSIGNED NOT NULL,
   `ItemType` VARCHAR(63) NOT NULL,
   `ItemCount` INT NOT NULL,
   `Name` VARCHAR(63) NOT NULL,
   `CatKey` VARCHAR(7) NOT NULL,
   PRIMARY KEY (`ID`,`ItemType`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

[edit] ← suppliers x _titles x _title_ittyps x cat_ittyps ...

  CREATE PROCEDURE Upd_SupplierIttyps()
    BEGIN
      DELETE FROM _supplier_ittyps;
      INSERT INTO _supplier_ittyps
      SELECT
        s.ID,
        if(Count(ti.ID_Title)=1,it.NameSng,it.NamePlr) AS ItemType,
        Count(ti.ID_Title) AS ItemCount,
        s.Name, s.CatKey
      FROM (
        (suppliers AS s LEFT JOIN _titles AS tc ON tc.ID_Supp=s.ID)
        LEFT JOIN _title_ittyps AS ti ON ti.ID_title=tc.ID)
        LEFT JOIN cat_ittyps AS it ON ti.ID_ItTyp=it.ID
      GROUP BY s.ID, s.Name, s.CatKey, it.NameSng, it.NamePlr, ID_Parent
      HAVING SUM(tc.cntForSale)
      ORDER BY s.Name, SUM(tc.cntForSale) DESC;
    END

Later we might refine this into a way to view unavailable suppliers as well... but that's very low priority, and it might be better to have a separate table for it anyway (keep "active" operations fast).

[edit] _stk_containers

Aggregate table listing all places where stock can be, for history: 

 DROP TABLE IF EXISTS `_stk_containers`;
 
 CREATE TABLE `_stk_containers` (
   `IDS` VARCHAR(31) NOT NULL COMMENT "Type + ID_forType: unique ID for this container, across all types",
   `Type` CHAR(1) NOT NULL COMMENT "identifies which type of container each record refers to",
   `ID_forType` INT NOT NULL COMMENT "record's unique ID for that type",
   `Name` VARCHAR(63) NOT NULL COMMENT "auto-generated but hopefully somewhat descriptive and human-readable name for this container",
   `WHEN` DATETIME COMMENT "applicable date, as a somewhat more human-readable cue for some of the container types",
   PRIMARY KEY(`IDS`)
 ) ENGINE = MYISAM;

[edit] ← stk_bins, cat_items...

SQL to update (replace/fill) _stk_containers (in progress; Packages and Restocks not yet migrated): 

CREATE PROCEDURE Upd_StkContainers()
  REPLACE INTO _stk_containers
  /*
  SELECT
      CONCAT("P.",p.ID) AS IDS,
      "P" AS Type,
      p.ID AS ID_forType,
      CONCAT(o.Number,"-",p.Seq) AS Name,
      s.WhenShipped AS TimeStamp
    FROM (Packages AS p LEFT JOIN Orders AS o ON p.ID_Order=o.ID) LEFT JOIN Shipments AS s ON p.ID_Shipment=s.ID;
    UNION /**/
  SELECT
      CONCAT("L.",l.ID) AS IDS,
      "L" AS Type,