Difference between revisions of "VbzCart/tables"

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
Jump to navigation Jump to search
(→‎cat_depts: renamed table in SQL; note about eventual obsolescence)
(→‎Ordering: first pass at rstk_items; some name changes)
Line 232: Line 232:
 
===Ordering===
 
===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.
 
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.
−
====Main data tables====
+
====core_orders====
 +
====ord_lines====
 +
 
 +
===Restocking===
 +
====rstk_items====
 +
<mysql>CREATE TABLE `rstk_lines` (
 +
  `ID`          INT NOT NULL AUTO_INCREMENT,
 +
  `ID_Restock`  INT COMMENT "core_restocks.ID of restock this item belongs to",
 +
  `ID_Item`    INT COMMENT "cat_items.ID of item being restocked",
 +
  `ID_Order`    INT COMMENT "core_orders.ID of order which generated this line",
 +
  `Descr`      VARCHAR(255) DEFAULT NULL "Item description as given at time of shopping",
 +
  `QtyNeed`    INT COMMENT "quantity needed, either for an order or to keep stock at desired level",
 +
  `QtyOrd`      INT COMMENT "quantity actually ordered",
 +
  `QtyExp`      INT COMMENT "quantity actually expected, if supplier doesn't have enough available to fill the order",
 +
  `isGone`      BOOL COMMENT "YES = item discontinued, no more available", /* data from invoice */
 +
  `InvcLineNo`  DECIMAL(3,3) COMMENT "line number (use decimals for multiple restock lines as single invoice line)",
 +
  `InvcQtyOrd`  INT COMMENT "quantity ordered",
 +
  `InvcQtySent` INT COMMENT "quantity shipped to us",
 +
/* data from receiving the items */
 +
  `QtyRecd`    INT COMMENT "quantity actually received",
 +
  `QtyFiled`    INT COMMENT "quantity moved into stock",
 +
/* Cost information, in cents */
 +
  `CostExp`    INT COMMENT "expected per-item cost",
 +
  `CostAct`    INT COMMENT "actual per-item cost, from invoice",
 +
  `CostInvTot`  INT COMMENT "invoice line total (CostExp x InvcQtySent) for this item",
 +
  `CostTot`    INT COMMENT "actual line total, using best figures available",
 +
  `CostBal`    INT COMMENT "running total; may be unnecessary anywhere except Access",
 +
  `Notes`      VARCHAR(255) COMMENT "human-entered notes on this line item",
 +
  PRIMARY KEY(`ID`)
 +
)
 +
ENGINE = MYISAM;</mysql>
 +
* if '''isGone''' is TRUE, then ideally:
 +
** don't carry items forward into next restock
 +
** mark item as discontinued in catalog
 +
===Shopping===
 +
These tables store data generated by the user during the shopping process.
 +
====shop_carts====
 
*'''carts''' -- shopping [[VbzCart Cart|cart]]s
 
*'''carts''' -- shopping [[VbzCart Cart|cart]]s
 
{| style="background: #eeeeee;"
 
{| style="background: #eeeeee;"
Line 248: Line 284:
 
|'''WhenLocked''' || timestamp || when this cart was finished / marked read-only (added to order)
 
|'''WhenLocked''' || timestamp || when this cart was finished / marked read-only (added to order)
 
|}
 
|}
−
====Collection/link tables====
+
====shop_sessions====
−
*'''ord_cart_sessions''' -- a given cart might be accessed from different sessions, when that functionality is available
+
''Not yet implemented; further design work needed''
 +
*'''shop_sessions''' -- a given cart might be accessed from different sessions, when that functionality is available
 
**'''ID_Cart'''# - int(4)
 
**'''ID_Cart'''# - int(4)
 
**'''ID_Sess'''# - int(4)
 
**'''ID_Sess'''# - int(4)
Line 262: Line 299:
 
**'''ShipMin''' - currency -- quoted minimum standard shipping cost (e.g. if sent with a lot of other items)
 
**'''ShipMin''' - currency -- quoted minimum standard shipping cost (e.g. if sent with a lot of other items)
 
**'''ShipMax''' - currency -- quoted maximum standard shipping cost (e.g. if sent by itself)
 
**'''ShipMax''' - currency -- quoted maximum standard shipping cost (e.g. if sent by itself)
 +
 
===Stock Management===
 
===Stock Management===
 
====stk_items====
 
====stk_items====

Revision as of 01:26, 29 October 2007

Navigation

{{#lst:VbzCart|navbar}}: tables

Tables

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

Catalog

These tables describe the items available for sale and correctly describe and price items added to the shopping cart.

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)

<mysql>CREATE TABLE `cat_supp` ( 

  `ID` INT  NOT NULL AUTO_INCREMENT,
  `Name` varchar(64) COMMENT 'name of supplier as displayed',
  `CatKey` varchar(4) COMMENT 'unique code, used for building catalog numbers',
  `isActive` BOOL COMMENT 'if FALSE, hide from casual listings',
  PRIMARY KEY(`ID`)
)
ENGINE = MYISAM;</mysql>
  • Fields which may be added later (or maybe they should be in a separate table):
    • MinCostPerOrd - currency -- supplier's minimum order, dollar amount
    • MinQtyPerDesign - int(4) -- supplier's default minimum order per design (can be overridden for specific Titles)
  • Additional contact data should probably be wiki-based. We'll have a namespace for vbz catalog data, probably "vbzcat:", and supplier information will be stored in pages named something like "vbzcat:lb.page", "vbzcat:lb.address", "vbzcat:lb.phone", and so on.
  • There are additional fields in the Access version of this table, but they will be added only when it becomes clear that they are necessary and that there isn't someplace better for them to go.
  • view "suppliers" is a temporary alias of cat_supp, until all views/code have been updated to use cat_supp

cat_depts

Eventually Departments and Suppliers should all be handled within 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.) <mysql> CREATE TABLE `cat_depts` ( 

  `ID` INT  NOT NULL AUTO_INCREMENT,
  `Name` varchar(64) COMMENT 'name of department as displayed',
  `ID_Supplier` INT DEFAULT NULL COMMENT 'cat_suppliers.ID',
  `isActive` BOOL COMMENT 'items.ID' COMMENT 'department is available for adding titles',
  `Sort` varchar(8) COMMENT 'listing order; default to CatKey',
  `CatKey` varchar(4) COMMENT 'use for building title catalog numbers',
  `PageKey` varchar(4) COMMENT 'use for building URL of Dept page',
  `Descr` varchar(255) COMMENT 'descriptive text for Dept page',
  PRIMARY KEY(`ID`)
)
ENGINE = MYISAM;</mysql>
  • This table may eventually be obsolete, as it could be better handled through Topics. Some infrastructure needs to be developed first, however; see #cat_supp for details.

cat_titles

catalog titles

Interim SQL: <mysql> CREATE TABLE `titles` ( 

 `ID` int(11) unsigned NOT NULL auto_increment,
 `Name` varchar(127),
 `CatKey` varchar(63) NOT NULL,
 `ID_Dept` int(11) unsigned NOT NULL default '0',
 `ID_License` int(11) default NULL COMMENT 'ID of agency from which the image was licensed - DEPRECATED; fileferret will track as topic',
 `DateAdded` datetime default NULL,
 `DateChecked` datetime default NULL COMMENT 'DEPRECATED',
 `DateUnavail` datetime default NULL,
 `RstkMin` int(11) unsigned default NULL,
 `Supplier_CatNum` varchar(31),
 `Supplier_CatNum_Alt` varchar(31) COMMENT 'DEPRECATED',
 `Desc` tinytext COMMENT 'descriptive text for display on web - DEPRECATED (use wiki)',
 `Search` varchar(255) COMMENT 'additional keywords for searching, but not displayed',
 `Notes` varchar(255) COMMENT 'internal notes; not displayed or searched - DEPRECATED (use wiki)',
 PRIMARY KEY(`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;</mysql>

cat_items

catalog items SQL for table definition: <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",
  `qtyInStock` INT COMMENT "number of pieces currently in stock and for sale, calculated from stk_items",
  `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",
  `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",
  `ID_ShipCost` INT COMMENT "applicable shipping cost calculation data",
  `PriceBuy`  INT COMMENT "wholesale price from supplier",
  `PriceSell` INT COMMENT "price to customer",
  `PriceList` INT 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>
  • 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.

SQL for updating availability from current stock:

<mysql> 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);</mysql>

cat_ittyps

catalog item types

  • ID#@ - int(4)
  • ID_Parent - int(4) -- cat_itemtypes.ID of parent, if any
  • NameSng - varchar(32) -- word for single item of this type (e.g. "shirt","box")
  • NamePlr - varchar(32) -- word for multiple items of this type (e.g. "shirts","boxes")
  • Descr - text(255) -- longer name, e.g. "compact disc" instead of "CD". If blank, use NameSng
  • Sort - text(31) -- sorting key (optional)
  • isType - flag -- if TRUE, this type may be used for actual items; if not, it is a folder (type category)

Note: should "Descr" be plural? Or will we need DescrSng and DescrPlr?

SQL: <mysql> DROP TABLE IF EXISTS `cat_ittyps`; 

CREATE TABLE `cat_ittyps` (
 `ID` INT  NOT NULL AUTO_INCREMENT,
 `ID_Parent` INT COMMENT '(self).ID of parent type; mainly for organizational purposes',
 `NameSng` varchar(63) NOT NULL COMMENT 'word for single item of this type (e.g. "shirt", "box")',
 `NamePlr` varchar(63) COMMENT 'word for multiple items of this type (e.g. "shirts", "boxes"); NULL = use singular',
 `Descr` varchar(255) COMMENT 'longer name (singular), e.g. "compact disc" instead of "CD". NULL = use singular',
 `Sort` varchar(31) COMMENT "optional sorting key",
 `isType` BOOL COMMENT 'FALSE = just a folder, so omit it from drop-down lists of types to choose from',
 PRIMARY KEY(`ID`)
) ENGINE = MYISAM;</mysql>

cat_ioptns

catalog options

  • ID#@ - int(4)
  • CatKey - varchar(8) -- appended to catalog number (with dash prefix)
  • Descr - varchar(64) -- appended to item description (with separator of some kind)
  • Sort - varchar(8) -- sorting order when options for a title appear together in a list

SQL: <mysql> CREATE TABLE `cat_ioptns` ( 

  `ID` INT NOT NULL AUTO_INCREMENT,
  `CatKey` varchar(7) NOT NULL COMMENT 'appended to catalog number (with dash prefix)',
  `Descr` varchar(127) NOT NULL COMMENT 'appended to item description (with separator of some kind)',
  `Sort` varchar(7) COMMENT 'sorting order when options for a title appear together in a list',
 PRIMARY KEY(`ID`)
) ENGINE = MYISAM;</mysql>

cat_images

SQL: <mysql> CREATE TABLE `cat_images` ( 

 `ID` INT NOT NULL AUTO_INCREMENT,
 `Spec` varchar(255) NOT NULL COMMENT 'relative filespec to image file',
 `ID_Title` INT NOT NULL COMMENT 'titles.ID of title to which this image applies',
 `Ab_Size` varchar(7) COMMENT 'ImgSizes.Ab: abbreviation for image size class',
 `AttrFldr` varchar(15) COMMENT 'folder (key) name for images with this attribute',
 `AttrDispl` varchar(64) COMMENT 'description to display (e.g. in image tooltip) for this attribute',
 `AttrSort` varchar(15) COMMENT 'sorting order for this attribute',
 PRIMARY KEY(`ID`)
)
ENGINE = MYISAM;</mysql>

cat_pages

This turned out to be the only non-klugey way to translate between requested URL and which page to display. It's also the data-driven equivalent of the old static-page-generation system, upon which the URL scheme is still based – so it's inherently less likely to result in URL-decoding anomalies. I think.

SQL to define tables: <mysql> DROP TABLE IF EXISTS `cat_pages`; 

CREATE TABLE `cat_pages` (
 `AB` varchar(31) NOT NULL COMMENT "Type+ID_Row: unique identifier",
 `Path` varchar(63) NOT NULL COMMENT 'URI of page',
 `ID_Row` INT NOT NULL COMMENT 'ID of row from appropriate table',
 `Type` CHAR NOT NULL COMMENT 'type of page: S=supplier, D=dept, T=title, I=image',
 PRIMARY KEY(`AB`),
 UNIQUE KEY(`Path`)
);

DROP TABLE IF EXISTS `cat_pages_old`;
CREATE TABLE `cat_pages_old` (
 `Path` varchar(63) NOT NULL COMMENT 'URI of page to redirect from',
 `AB_Page` INT NOT NULL COMMENT "cat_pages.AB of page to redirect to",
 `WhenSet` DATETIME COMMENT "optional timestamp of when this redirect was created",
 `Notes` varchar(255) COMMENT "optional notes on why this redirect is being maintained",
 PRIMARY KEY (`Path`)
);</mysql>

<mysql> CREATE PROCEDURE Upd_CatPages() 

   REPLACE INTO cat_pages
   SELECT
       CONCAT_WS('-','S',ID) AS AB,
       LOWER(CatKey) AS Path,
       ID,
       'S' AS Type
     FROM suppliers
     UNION
   SELECT
       CONCAT_WS('-','D',ID) AS AB,
       LOWER(CatWeb_Dept) AS Path,
       ID,
       'D' AS Type
     FROM _depts
     UNION
   SELECT
       CONCAT_WS('-','T',ID) AS AB,
       REPLACE(LOWER(CatWeb),'-','/') AS Path,
       ID,
       'T' AS Type
     FROM _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 _titles AS t ON i.ID_Title=t.ID WHERE (i.Ab_Size NOT IN ('th','sm'));</mysql>

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)

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.

core_orders

ord_lines

Restocking

rstk_items

<mysql>CREATE TABLE `rstk_lines` ( 

  `ID`          INT NOT NULL AUTO_INCREMENT,
  `ID_Restock`  INT COMMENT "core_restocks.ID of restock this item belongs to",
  `ID_Item`     INT COMMENT "cat_items.ID of item being restocked",
  `ID_Order`    INT COMMENT "core_orders.ID of order which generated this line",
  `Descr`       VARCHAR(255) DEFAULT NULL "Item description as given at time of shopping",
  `QtyNeed`     INT COMMENT "quantity needed, either for an order or to keep stock at desired level",
  `QtyOrd`      INT COMMENT "quantity actually ordered",
  `QtyExp`      INT COMMENT "quantity actually expected, if supplier doesn't have enough available to fill the order",
  `isGone`      BOOL COMMENT "YES = item discontinued, no more available", /* data from invoice */
  `InvcLineNo`  DECIMAL(3,3) COMMENT "line number (use decimals for multiple restock lines as single invoice line)",
  `InvcQtyOrd`  INT COMMENT "quantity ordered",
  `InvcQtySent` INT COMMENT "quantity shipped to us",

/* data from receiving the items */ 

  `QtyRecd`     INT COMMENT "quantity actually received",
  `QtyFiled`    INT COMMENT "quantity moved into stock",

/* Cost information, in cents */ 

  `CostExp`     INT COMMENT "expected per-item cost",
  `CostAct`     INT COMMENT "actual per-item cost, from invoice",
  `CostInvTot`  INT COMMENT "invoice line total (CostExp x InvcQtySent) for this item",
  `CostTot`     INT COMMENT "actual line total, using best figures available",
  `CostBal`     INT COMMENT "running total; may be unnecessary anywhere except Access",
  `Notes`       VARCHAR(255) COMMENT "human-entered notes on this line item",
  PRIMARY KEY(`ID`)
)
ENGINE = MYISAM;</mysql>
  • if isGone is TRUE, then ideally:
    • don't carry items forward into next restock
    • mark item as discontinued in catalog

Shopping

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

shop_carts

ID#@ int(4)
ID_Session int(4) session in which this cart was created
WhenCreated timestamp when this cart was created
WhenViewed timestamp when this cart was last viewed
WhenUpdated timestamp when cart was last modified
WhenLocked timestamp when this cart was finished / marked read-only (added to order)

shop_sessions

Not yet implemented; further design work needed

  • shop_sessions -- a given cart might be accessed from different sessions, when that functionality is available
    • ID_Cart# - int(4)
    • ID_Sess# - int(4)
  • ord_cart_items -- items in a cart
    • ID_Cart# - int(4)
    • ID_Item# - int(4)
    • Qty - int(4)
    • CatNum - varchar(32) -- catalog number as sold
    • Descr - text -- description as sold
    • Price - currency -- price as sold
    • ShipMin - currency -- quoted minimum standard shipping cost (e.g. if sent with a lot of other items)
    • ShipMax - currency -- quoted maximum standard shipping cost (e.g. if sent by itself)

Stock Management

stk_items

listing of what is now and what used to be in stock; depends on stk_bins and cat_items tables <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>

stk_bins

containers in which stock may be found; depends on #stk_places <mysql> DROP TABLE IF EXISTS `stk_bins`; 

CREATE TABLE `stk_bins` (
 `ID` int(11) NOT NULL auto_increment,
 `ID_Place` int(11) NOT NULL COMMENT 'stk_places.ID',
 `Code` varchar(15) NOT NULL COMMENT 'code name, e.g. NC01 -- must appear on outside of box',
 `Descr` varchar(63) default NULL COMMENT 'brief summary of contents',
 `WhenCreated` datetime default NULL COMMENT 'date when container was added to the database',
 `WhenVoided` datetime default NULL COMMENT 'date when container was destroyed or removed from usage',
 `isForSale` tinyint(1) default NULL COMMENT "TRUE = this item is visible to customers as stock",
 `isForShip` tinyint(1) default NULL COMMENT "TRUE = this item is available for filling orders",
 `Notes` text,
 PRIMARY KEY  (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=111 DEFAULT CHARSET=latin1;</mysql>
  • WhenCreated can be NOT NULL if you don't have any legacy data to deal with.
  • isForShip got eventually added because I kept forgetting to use WhenVoided, and I finally decided that it wasn't redundant to have a flag specifically for this attribute

stk_places

places where containers may be found, organized hierarchically <mysql> DROP TABLE IF EXISTS `stk_places`; 

CREATE TABLE `stk_places` (
  `ID` INT  NOT NULL AUTO_INCREMENT,
  `ID_Parent` INT COMMENT 'self.ID of place in which this place is found; may be NULL',
  `Name` VARCHAR(63) NOT NULL COMMENT 'brief descriptive name for listings and tree-paths',
  `Descr` VARCHAR(127) COMMENT '(optional) description of this place, so it can be located',
  PRIMARY KEY(`ID`)
) ENGINE = MYISAM;</mysql>

Both stock Bins and order Packages can be in Places. Later, Places may have attributes to tell us whether we can ship items directly from there (or at all) and whether or not they are effectively "local" to us, but for now they don't: 

  `isLocal` BOOL COMMENT `FALSE = items aren't here, so they must either be shipped here first or shipped to customer in separate pkg`,
  `isWhse` BOOL COMMENT `TRUE = items have to be fetched for shipping; some delay involved, and may want to print out lists of stuff to fetch`,
  `ID_Supp` INT COMMENT `NOT NULL = this location is a supplier's stock, and items must be ordered from it in order to be shippable`,

stk_history

log of all stock movement (depends on #stk_containers and #cat_items): <mysql>DROP TABLE IF EXISTS `stk_history`;

CREATE TABLE `stk_history` ( 

 `ID` INT  NOT NULL AUTO_INCREMENT,
 `ID_Stock` INT NOT NULL COMMENT "stk_items.ID of item being moved",
 `ID_Item` INT /* NOT NULL */ COMMENT "cat_items.ID of item being moved",
 `Qty` INT NOT NULL COMMENT "number of items moved (Qty fields at targ/dest may change value later)",
 `When` DATETIME NOT NULL COMMENT "when the move happened",
 `IDS_ContSrce` varchar(31) /* NOT NULL */ COMMENT "_stk_containers.IDS of where the item was moved from",
 `IDS_ContDest` varchar(31) /* NOT NULL */ COMMENT "_stk_containers.IDS of where the item was moved to",
 `IDS_LineSrce` varchar(31) /* NOT NULL */ COMMENT "hypothetical _stk_lines.IDS of where item came from",
 `IDS_LineDest` varchar(31) /* NOT NULL */ COMMENT "hypothetical _stk_lines.IDS of where item was moved to",
 `Notes` varchar(255) DEFAULT NULL COMMENT "optional explanatory notes",
 PRIMARY KEY(`ID`)

) ENGINE = MYISAM;</mysql>

The IDS_Line* fields are somewhat redundant, but I decided that redundancy was a goal: one of the purposes of history data (including stk_history) is to help reconstruct what happened when something goes wrong. Maintenance of the _stk_containers table is also somewhat redundant, but should make displaying meaningful history reports quicker (i.e. it's basically a sort of cache, as are all the _* tables).

Commented-out bits can be included if you have no incomplete legacy data to deal with.

stk_bin_history

<mysql>DROP TABLE IF EXISTS `stk_bin_history`;

CREATE TABLE `stk_bin_history` ( 

 `ID` INT  NOT NULL AUTO_INCREMENT,
 `ID_Bin` INT NOT NULL COMMENT "stk_bins.ID of bin being moved",
 `WhenDone` DATETIME NOT NULL COMMENT "when the move happened",
 `ID_Srce` INT COMMENT "stk_places.ID of where the bin came from (NULL = new bin)",
 `ID_Dest` INT COMMENT "stk_places.ID of where the bin was moved to (NULL = bin destroyed)",
 `Notes` varchar(255) DEFAULT NULL COMMENT "optional explanatory notes",
 PRIMARY KEY(`ID`)

) ENGINE = MYISAM;</mysql>

Browsing

Topics and images

brs_topics

<mysql>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;</mysql>

brs_titles_x_topics

<mysql>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;</mysql>

Miscellaneous

  • stats – computed variables, cache timestamps, and other variable stuff

<mysql> CREATE TABLE `stats` ( 

   `Name` varchar(32) NOT NULL COMMENT 'name of variable',
   `Value` varchar(255) COMMENT 'value of variable',
  PRIMARY KEY(`Name`)
) ENGINE = MYISAM;</mysql>
  • updates – timestamps on tables so we know when update queries need to be run

<mysql> 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;</mysql>

Function to record an update: <mysql> CREATE PROCEDURE SetUpdate(IN iName varchar(32)) 

BEGIN
  REPLACE INTO updates(Name,Updated) values(iName, NOW());
END</mysql>

Calculated Tables

_depts

<mysql> 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;</mysql>

← depts x suppliers

SQL to fill _depts (except inPrint/inStock fields): <mysql> 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</mysql>

← _dept_ittyps by ID_Dept

<mysql> 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;</mysql>

_titles

<mysql> DROP TABLE IF EXISTS `_titles`; 

CREATE TABLE `_titles` (
  `ID` int(11) unsigned NOT NULL,
  `ID_Supp` int(11) unsigned NOT NULL COMMENT 'needed to avoid having to join with depts table',
  `CatNum` varchar(63) NOT NULL,
  `CatWeb` varchar(63) NOT NULL,
  `cntForSale` int DEFAULT NULL,
  `cntInPrint` int DEFAULT NULL,
  `qtyInStock` int DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;</mysql>

← titles x _depts

<mysql> 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;</mysql>

← cat_items (grouped by ID_Title)

<mysql> 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
      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;</mysql>

← _title_ittyps (grouped by ID_Title)

<mysql> 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;</mysql>

_title_ittyps

list of available titles for each item type <mysql> 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` int DEFAULT NULL COMMENT 'minimum price for this item type',
  `currMaxPrice` int 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;</mysql>
  • 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)

← 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.) <mysql> 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;</mysql>
  • 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

← _titles

<mysql> 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;</mysql>

← cat_ittyps

<mysql> 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;</mysql>

_dept_ittyps

<mysql> 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;</mysql>

← _title_ittyps

<mysql> 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;</mysql>

← cat_ittyps

<mysql> 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);</mysql>

_supplier_ittyps

<mysql> 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;</mysql>

← suppliers x _titles x _title_ittyps x cat_ittyps ...

<mysql> 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</mysql>

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).

_stk_containers

Aggregate table listing all places where stock can be, for history: <mysql> 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;</mysql>

← stk_bins, cat_items...

SQL to update (replace/fill) _stk_containers (in progress; Packages and Restocks not yet migrated): <mysql>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,
     l.ID AS ID_forType,
     l.Code AS Name,
     l.WhenCreated AS TimeStamp
   FROM stk_bins AS l
   UNION
 /*
 SELECT
     CONCAT("R.",r.ID) AS IDS,
     "R" AS Type,
     r.ID AS ID_forType,
     "po"&r.PurchOrdNum&"/ord"&SuppOrdNum&"/inv"&SuppInvcNum AS Name,
     RestockEffDate(r.ID) AS TimeStamp
   FROM Restocks AS r
   UNION /**/
 SELECT
     CONCAT("M.",i.ID) AS IDS,
     "M" AS Type,
     i.ID AS ID_forType,
     i.CatNum AS Name,
     NULL AS TimeStamp
   FROM cat_items AS i WHERE i.IsMaster;</mysql>

Data Management

These tables are all maintained by hand, but must match what actually happens in the SQL code.

data_tables

<mysql>DROP TABLE IF EXISTS `data_tables`;

CREATE TABLE `data_tables` ( 

   `ID` INT NOT NULL AUTO_INCREMENT,
   `Name` varchar(63) NOT NULL COMMENT "name of table being tracked",
   `WhenUpdated` DATETIME DEFAULT NULL COMMENT "when the table's data was last modified",
   `Notes` varchar(255) DEFAULT NULL COMMENT "descriptive notes",
  PRIMARY KEY(`ID`)
) ENGINE = MYISAM;</mysql>

data_procs

List of stored procedures which update one table from others <mysql>DROP TABLE IF EXISTS `data_procs`;

CREATE TABLE `data_procs` ( 

   `ID` INT NOT NULL AUTO_INCREMENT COMMENT "procedure ID",
   `Name` varchar(127) NOT NULL COMMENT "name of SQL stored procedure",
   `isActive` BOOL COMMENT "FALSE = skip this record",
   `doesClear` BOOL COMMENT "TRUE = clears all data from table before starting",
   `Notes` varchar(255) DEFAULT NULL COMMENT "explanatory notes",
   PRIMARY KEY (`ID`)
) ENGINE = MYISAM;</mysql>

data_flow

Keeps track of data dependencies between tables <mysql>DROP TABLE IF EXISTS `data_flow`;

CREATE TABLE `data_flow` ( 

   ID_Srce INT NOT NULL COMMENT "data_tables.ID of SOURCE table",
   ID_Dest INT NOT NULL COMMENT "data_tables.ID of DESTINATION table",
   ID_Proc INT NOT NULL COMMENT "data_procs.ID of stored procedure which calculates Dest data from Srce data",
   Notes varchar(255) COMMENT "loose explanatory or descriptive notes",
   PRIMARY KEY (`ID_Srce`,`ID_Dest`,`ID_Proc`)
) ENGINE = MYISAM;</mysql>

data_log

Log of all data updates performed internally <mysql>DROP TABLE IF EXISTS `data_log`;

CREATE TABLE `data_log` ( 

   ID INT NOT NULL AUTO_INCREMENT COMMENT "log line identifier",
   WhenStarted DATETIME NOT NULL COMMENT "when this event was started",
   WhenFinished DATETIME COMMENT "when it was completed",
   ID_TableDest INT NOT NULL COMMENT "data_tables.ID of table being updated",
   ID_TableSrce INT NOT NULL COMMENT "data_tables.ID of triggering/source table",
   ID_Proc INT NOT NULL COMMENT "data_procs.ID of stored procedure used",
   Caller varchar(63) COMMENT "identifying string from code which caused the update",
   Notes varchar(255) COMMENT "historical human-created notes",
   PRIMARY KEY (`ID`)
) ENGINE = MYISAM;</mysql>

Miscellaneous

error/message log

<mysql>DROP TABLE IF EXISTS `event_log`;

CREATE TABLE `event_log` ( 

   ID INT NOT NULL AUTO_INCREMENT COMMENT "log line identifier",
   EvWhen DATETIME NOT NULL COMMENT "when the event started",
   EvWhere varchar(255) COMMENT "where in the code the event happened (suitable for filtering)",
   Params varchar(255) COMMENT "any relevant parameters",
   Descr varchar(255) COMMENT "description of error",
   Code INT DEFAULT NULL COMMENT "numeric error code unique to location (EvWhere)",
   VbzUser varchar(127) COMMENT "VbzCart username, for when we have a user system",
   SysUser varchar(127) COMMENT "who logged into the operating system",
   Machine varchar(64) COMMENT "network name of machine from which the event was initiated, if applicable",
   isError BOOL COMMENT "FALSE = this is just a message or normal event; TRUE = there is a problem to fix",
   isSevere BOOL COMMENT "TRUE = important enough to send email to admin immediately",
   Notes varchar(255) DEFAULT NULL COMMENT "manually-entered notes",
   PRIMARY KEY (`ID`)
) ENGINE = MYISAM;</mysql>
  • Have to use "Ev" prefix because When and Where are keywords.
  • Decided not to have "WhenStarted" and "WhenFinished" because:
    • There are relatively few events that "complete"
    • This leaves no place to record completion status (e.g. how many records were affected)
    • It makes the code more complicated and less elegant

Views

"Views" in MySQL are essentially identical to stored queries in MS Access.

v_titles

<mysql>CREATE OR REPLACE VIEW v_titles AS 

 SELECT
   t.*,
   tx.ID_Supp,
   tx.CatNum,
   tx.CatWeb,
   tx.cntForSale,
   tx.cntInPrint,
   tx.qtyInStock
     FROM titles AS t LEFT JOIN _titles AS tx on t.ID=tx.ID;</mysql>

This will later reference cat_titles instead of titles, when that table is fully migrated.

v_items

<mysql>CREATE OR REPLACE VIEW `v_items` AS 

 SELECT
   i.ID,
   i.CatNum,
   i.isForSale,
   i.isMaster,
   i.qtyInStock,
   i.isInPrint,
   i.isCloseOut,
   i.isPulled,
   i.isDumped,
   i.ID_Title,
   i.ID_ItTyp,
   i.ID_ItOpt,
   i.ItOpt_Descr,
   i.ID_ShipCost,
   i.PriceBuy,
   i.PriceSell,
   i.PriceList,
   i.Supp_CatNum,
   io.Sort AS OptSort
 FROM (
   `cat_items` AS `i` LEFT JOIN 
   `cat_ioptns` AS `io` ON
     ((`i`.`ID_ItOpt` = `io`.`ID`)))</mysql>

Used by clsItemsExt

v_stk_lines_remaining

View listing stk_items actually in stock, which is actually different things:

  • "forSale": items which are visible as "in stock" to customers
  • "forShip": items which are available for shipping
  • items neither for shipping nor for sale (probably some kind of recordkeeping purpose)

<mysql>CREATE OR REPLACE VIEW v_stk_lines_remaining AS 

 SELECT
   st.ID,
   st.ID_Bin,
   st.ID_Item,
   IF(sb.isForSale,st.Qty,0) AS QtyForSale,
   IF(sb.isForShip,st.Qty,0) AS QtyForShip,
   st.Qty AS QtyExisting,
   st.CatNum,
   st.WhenAdded,
   st.WhenChanged,
   st.WhenCounted,
   st.Notes,
   sb.ID_Place,
   sp.Name AS WhName
   FROM
     (
       stk_items AS st
       LEFT JOIN stk_bins AS sb
         ON sb.ID=st.ID_Bin
      )
      LEFT JOIN stk_places AS sp
        ON sb.ID_Place=sp.ID
   WHERE (st.WhenRemoved IS NULL) AND (sb.WhenVoided IS NULL) AND (st.Qty <> 0);</mysql>

v_stk_items_remaining

<mysql>CREATE OR REPLACE VIEW v_stk_items_remaining AS 

 SELECT
   ID_Item,
   SUM(QtyForSale) AS QtyForSale,
   SUM(QtyForShip) AS QtyForShip,
   SUM(QtyExisting) AS QtyExisting
 FROM v_stk_lines_remaining
 GROUP BY ID_Item;</mysql>

v_stk_byItemAndBin

<mysql>CREATE OR REPLACE VIEW v_stk_byItemAndBin AS 

 SELECT
   ID_Item,
   ID_Bin,
   SUM(QtyForSale) AS QtyForSale,
   SUM(QtyForShip) AS QtyForShip,
   SUM(QtyExisting) AS QtyExisting
 FROM v_stk_lines_remaining
 GROUP BY ID_Item, ID_Bin
 HAVING SUM(QtyExisting)>0;</mysql>

v_stk_byItemAndBin_wItemInfo

<mysql>CREATE OR REPLACE VIEW v_stk_byItemAndBin_wItemInfo AS 

 SELECT * FROM v_stk_byItemAndBin AS s LEFT JOIN qryCat_Items AS i ON s.ID_Item=i.ID;</mysql>

v_stk_byItemAndBin_wInfo

<mysql>CREATE OR REPLACE VIEW v_stk_byItemAndBin_wInfo AS 

 SELECT
   s.*,
   i.*,
   b.ID_Place,
   b.Code AS BinCode,
   b.Descr AS BinDescr
 FROM
 (
   v_stk_byItemAndBin AS s LEFT JOIN qryCat_Items AS i ON s.ID_Item=i.ID
 ) LEFT JOIN stk_bins AS b ON s.ID_Bin=b.ID;</mysql>

v_data_flow

<mysql> CREATE OR REPLACE VIEW v_data_flow AS 

   SELECT
     df.ID_Srce,
     df.ID_Dest,
     df.ID_Proc,
     dfx.doesClear
   FROM data_flow AS df LEFT JOIN data_procs AS dfx ON df.ID_Proc=dfx.ID
   ORDER BY dfx.doesClear;</mysql>


Future Changes

This is probably obsolete, and may have been partially implemented. Review later.

Shipping code should be tied to the item Option (i.e. no ID_ShipCode field in cat_items), which would include size and other variants (i.e. cat_itypes and cat_iopts will be combined). Each Supplier should have its own list of item types, to allow for different variation (e.g. LB tie-dye shirts of a given size weigh more than ZR shirts of the same size because they use thicker cloth). I'm using the schema as it is because converting the existing data will be a complicated task and we need to get this thing working ASAP, but I picture something like this in the near future:

  • cat_itypes - catalog item types
    • ID#@ - int(4)
    • Name - text
    • ID_Parent - int(4) -- cat_itypes.ID of parent type, if any
  • cat_ioptns - catalog item options
    • ID#@ - int(4)
    • Key - string(8) -- Supplier.CatKey-Title.CatKey[-Key] = complete catalog number (Key can be blank)
    • Descr - string(64) -- description of this option (e.g. "eXtra Large")
  • cat_itype_ioptns - options available for each item type, per supplier
    • ID - int(4) -- unique identifier
    • ID_Supplier# - int(4)
    • ID_IType# - int(4)
    • ID_IOptn# - int(4)
    • ID_ShipCode - int(4) -- shipping cost type for this combination
    • Descr - string(128) -- description of this option/type combination (e.g. "eXtra Large shortsleeve t-shirt")

We would then remove the ID_IType and ID_ShipType fields from cat_items; ID_IOpt would point to cat_itype_ioptns instead of cat_ioptns... or we could rename it ID_ITypeOptn, but that's... a bit ugly. I'll have to talk myself into it. Also, I think I used a scheme something like this in an earlier revision of the MS Access version of VbzCart and ended up changing it to what it is now; that may have been for a good reason, or it may have been because it seemed too unwieldy... I think I was generating a cat_itype_ioptns entry for every possible combination of IType and IOptn, and that doesn't make sense. You just need a tool to help manage them, and to copy existing sets for tweaking. It also didn't have an ID_Supplier key.

Retrieved from "https://htyp.org/mw/index.php?title=VbzCart/tables&oldid=8529"