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
m (→‎ctg_upd2: note about isActive)
(→‎Event Logging: more obsolete tables, so I can close the tabs)
 
(90 intermediate revisions by the same user not shown)
Line 1: Line 1:
−
==Navigation==
+
==Current Tables==
−
<section begin=navbar />{{#lst:VbzCart|navbar}}: [[VbzCart tables|tables]]<section end=navbar />
 
−
===Related Pages===
 
−
* [[VbzCart customer tables]]
 
−
 
 
−
==Tables==
 
−
*"#" indicates Primary Key fields
 
−
*"@" indicates autonumbered fields
 
 
===Catalog===
 
===Catalog===
−
These tables describe the items available for sale and correctly describe and price items added to the shopping cart.
+
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.
 
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.
Line 14: Line 7:
 
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.
 
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====
+
* [[/cat_supp]]: catalog suppliers (i.e. manufacturers, wholesalers)
−
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
−
<section begin=sql /><mysql>CREATE TABLE `cat_supp` (
+
* [[/cat_items]]: items within a title - a particular version of a title
−
  `ID` INT  NOT NULL AUTO_INCREMENT,
+
* [[/cat_ittyps]]: item types - every item has one, but they are often all the same
−
  `Name` varchar(64) COMMENT 'name of supplier as displayed',
+
* [[/cat_ioptns]]: these typically distinguish items within a title
−
  `CatKey` varchar(4) COMMENT 'unique code, used for building catalog numbers',
+
* [[/cat_topic]]: categorization
−
  `isActive` BOOL COMMENT 'if FALSE, hide from casual listings',
+
** [[/cat_title_x_topic]]: titles for each topic
−
  PRIMARY KEY(`ID`)
+
* [[/cat_ship_cost]]: shipping costs for different types of item
−
)
+
* [[/cat_pages]]: mapping URLs to various catalog entities (suppliers, depts, titles)
−
ENGINE = MYISAM;</mysql>
+
* [[/cat_pages_old]]: catalog designations sometimes change; where possible, this lets us redirect old URLs
−
<section end=sql />
+
====future====
−
* Fields which may be added later (or maybe they should be in a separate table):
+
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 &ndash; like "catkey" &ndash; to any topic, so that each Department topic and Supplier topic can refer to the proper catalog number.)
−
** '''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 &ndash; like "catkey" &ndash; to any topic, so that each Department topic and Supplier topic can refer to the proper catalog number.)
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
 
 
−
* 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
 
−
 
 
−
<section begin=sql /><mysql> CREATE TABLE `cat_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>
 
−
<section end=sql />
 
−
 
 
−
* The design of this table will probably change some, as there some deprecated fields to be removed
 
−
 
 
−
====cat_items====
 
−
catalog items
 
−
SQL for table definition:
 
−
<section begin=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",
 
−
  `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",
 
−
  `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",
 
−
  `QtyMin_Stk` INT COMMENT "minimum quantity to keep in stock",
 
−
  PRIMARY KEY(`ID`)
 
−
)
 
−
ENGINE = MYISAM;</mysql>
 
−
<section end=sql />
 
−
 
 
−
* '''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.
 
−
 
 
−
SQL for updating availability from current stock:
 
−
 
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
 
 
−
====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:
 
−
<section begin=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>
 
−
<section end=sql />
 
  
−
====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:
 
−
<section begin=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>
 
−
<section end=sql />
 
−
 
−
====cat_images====
 
−
SQL:
 
−
<section begin=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>
 
−
<section end=sql />
 
−
====cat_ship_costs====
 
−
This table defines the numbers used to calculate shipping costs for different types of items. Eventually it should have fields for tracking weight and co-packaging compatibility so we might have the option of just feeding the estimated shipping weight to shipper [[API]]s and getting back an actual shipping estimate -- but for now, it's based on:
 
−
* '''$ per package''': roughly, the cost of shipping the smallest package which can hold this item, without the actual item. Or, more practically: The cost difference between shipping two of the item and shipping one of the item, subtracted from the cost of shipping one of the item.
 
−
* '''$ per item''': roughly, the cost which the actual item adds to the package. (The rise of shipping cost as weight goes up is approximately linear, but only very approximately.)
 
−
<section begin=sql /><mysql>CREATE TABLE `cat_ship_costs` (
 
−
  `ID`      INT NOT NULL AUTO_INCREMENT,
 
−
  `Descr`  VARCHAR(63) NOT NULL COMMENT "brief description, for internal use",
 
−
  `PerItem` DECIMAL(9,2) COMMENT "s/h cost differential per item",
 
−
  `PerPkg`  DECIMAL(9,2) COMMENT "s/h cost for packaging this item",
 
−
  `Sort`    FLOAT DEFAULT NULL COMMENT "optional sorting, for internal use",
 
−
  `CartMsg` VARCHAR(255) DEFAULT NULL COMMENT "messages regarding shipping for items using this code",
 
−
  PRIMARY KEY(`ID`)
 
−
);</mysql>
 
−
<section end=sql />
 
−
* '''CartMsg''' is a message which should appear near (or in) the shopping cart whenever an item with the corresponding shipping code is ordered. There was also an '''isApprox''' field, but the message seems adequate to serve this function. (The flag never did anything beyond displaying a message.)
 
−
 
−
====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 &ndash; so it's inherently less likely to result in URL-decoding anomalies. I think.
 
−
 
−
SQL to define tables:
 
−
<section begin=sql /><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`)
 
−
);
 
−
 
−
CREATE OR REPLACE VIEW _cat_pages AS
 
−
  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
 
−
    WHERE cntForSale
 
−
  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'));
 
−
 
−
CREATE PROCEDURE Upd_CatPages()
 
−
  REPLACE INTO cat_pages
 
−
  SELECT * FROM _cat_pages
 
−
  ORDER BY Path;</mysql>
 
−
<section end=sql />
 
−
 
−
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)
 
 
===Catalog Entry===
 
===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.
+
These are tables used to make it easier to update the {{l/vc/piece|catalog/local|local catalog}}; see {{l/vc/piece|catalog/supplier}} and {{l/vc/piece|catalog/building}}.
−
 
 
−
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.
 
−
====ctg_supp====
 
−
Information about suppliers which isn't needed for customer web site
 
−
<section begin=sql /><mysql>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;</mysql>
 
−
<section end=sql />
 
−
====ctg_prc_funcs====
 
−
Functions for calculating our retail price from the wholesale cost we pay:
 
−
: retail = {(wholesale * PriceFactor) + PriceAddend} rounded up to next PriceRound
 
−
<section begin=sql /><mysql>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;</mysql>
 
−
<section end=sql />
 
−
 
 
−
====ctg_sources====
 
−
<section begin=sql /><mysql>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;</mysql>
 
−
<section end=sql />
 
−
* To discontinue a supplier, set ID_Supercede=ID for all active sources.
 
−
====ctg_groups====
 
−
Master list of groups (CTGs)
 
−
<section begin=sql /><mysql>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;</mysql>
 
−
<section end=sql />
 
−
====ctg_titles====
 
−
Specifies which groups a title belongs to
 
−
<section begin=sql /><mysql>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;</mysql>
 
−
<section end=sql />
 
−
====ctg_items====
 
−
Specifies which item varieties/prices are available in a group
 
−
<section begin=sql /><mysql>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;</mysql>
 
−
<section end=sql />
 
−
====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.
 
−
<section begin=sql /><mysql>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;</mysql>
 
−
<section end=sql />
 
−
* '''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.
 
−
====ctg_upd1====
 
−
Experimental, but hopefully will replace ctg_updates which never worked entirely properly. This table contains only the essential fields from qryCtg_src.
 
−
<section begin=sql /><mysql>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),
 
−
  cntDups          INT,
 
−
  PRIMARY KEY(`IDS_Item`)
 
−
)
 
−
ENGINE = MYISAM;</mysql>
 
−
<section end=sql />
 
−
 
 
−
====ctg_upd2====
 
−
Experimental, but hopefully will replace ctg_updates which never worked entirely properly. It looks like isActive can go away, but at least one query will need some modification.
 
−
<section begin=sql /><mysql>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",
 
−
  isActive      BOOL NOT NULL DEFAULT FALSE COMMENT "current sources do generate a record for this item",
 
−
  cntDups        INT DEFAULT 0,
 
−
  PRIMARY KEY(`IDS_Item`)
 
−
)
 
−
ENGINE = MYISAM;</mysql>
 
−
<section end=sql />
 
  
 +
* <s>[[/ctg_supp]]</s> - deprecated
 +
* [[/ctg_prc_funcs]]
 +
* [[/ctg_sources]]
 +
* [[/ctg_groups]]
 +
* [[/ctg_titles]]
 +
* [[/ctg_items]]
 +
* [[/ctg_upd1]]
 +
* [[/ctg_upd2]]
 +
===File Management===
 +
* [[/fm_node]]: a filesystem entity (could be either file or folder)
 +
* [[/fm_file]]: files of any type
 +
* [[/fm_folder]]: places where files can be stored
 +
* [[/cat_images]]: files that are images, with additional metadata
 
===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.
+
Cart data and line-items are converted to orders and order lines when the customer confirms the order. Orders become the focal point for a lot of other activity, such as shipping, transactions, and tracking of status changes.
  
−
There is a handful of tables just for managing customer contact information, so  I'm putting those on a separate page: [[VbzCart customer tables]]
+
* [[/orders]]
−
====core_orders====
+
** [[/ord_lines]]
−
<section begin=sql /><mysql>CREATE TABLE `core_orders` (
+
** [[/ord_pkgs]]
−
  `ID`            INT NOT NULL AUTO_INCREMENT,
+
** [[/ord_pkg_lines]]
−
  `Number`        VARCHAR(63) NOT NULL COMMENT "order number for human reference; generated by web site",
+
** [[/ord_shipmt]]
−
  `SortPfx`        VARCHAR(7) DEFAULT NULL COMMENT "sorting prefix -- earlier prefix schemes sort wrong, and this overrides that",
+
** [[/ord_trxact]] - order transactions
−
  `PassCode`      VARCHAR(15) DEFAULT NULL COMMENT "order's security passcode - useful if user has not created an account",
+
** [[/ord_trx_type]] - order transaction types
−
  `ID_Pull`        INT DEFAULT NULL COMMENT "ord_pulls.ID: ID of the currently active Pull for this order (NULL = order is active)",
+
** [[/ord_msg]]
−
  `ID_NameBuyer`  INT DEFAULT NULL COMMENT "cust_names.ID -- purchaser, contact for discussing order",
+
*** [[/ord_msg_media]]
−
  `ID_NameRecip`  INT DEFAULT NULL COMMENT "cust_names.ID -- shipping destination",
+
** ''see also [[#Event Logging]]''
−
  `ID_ContactAddrRecip` INT DEFAULT NULL COMMENT "cust_addrs.ID -- shipping address to use (NULL = recipient's default address)",
+
* [[/card_auth_code]] - reference table for payment status codes; this should probably be a hard-coded array, not a table
−
  `ID_ContactPhone`    INT DEFAULT NULL COMMENT "cust_phones.ID -- phone number for this order",
+
* '''obsolete''':
−
  `ID_ContactEmail`    INT DEFAULT NULL COMMENT "cust_emails.ID -- email address for this order",
+
** [[/ord_pull]]
−
  `PayType`            INT DEFAULT NULL COMMENT "aux_pay_types.code -- payment method used for this order",
+
*** [[/ord_pull_type]]
−
  `ID_ChargeCard`      INT DEFAULT NULL COMMENT "cust_cards.ID -- bank card used to pay for this order",
+
** [[/ord_event]] - internal events not generally affecting order status
−
  `WebTotal_Merch`      DECIMAL(9,2) DEFAULT NULL COMMENT "total amount quoted for merchandise",
 
−
  `WebTotal_Ship`      DECIMAL(9,2) DEFAULT NULL COMMENT "total quoted for additional charges (S/H, tax)",
 
−
  `WebTotal_Final`      DECIMAL(9,2) DEFAULT NULL COMMENT "final total charge quoted",
 
−
  `WhenStarted`        DATETIME DEFAULT NULL COMMENT "when order record was created",
 
−
  `WhenOpened`          DATETIME DEFAULT NULL COMMENT "date/time entered (downloaded) to database (or entered manually) and ready for processing",
 
−
  `WhenClosed`          DATETIME DEFAULT NULL COMMENT "date/time order was completed -- all items shipped, cancelled, or unavailable",
 
−
  `WhenUpdated`        DATETIME DEFAULT NULL COMMENT "date/time order info last changed via the web site",
 
−
  `WhenEdited`          DATETIME DEFAULT NULL COMMENT "date/time order was last edited in-store",
 
−
  `WhenNeeded`          DATETIME DEFAULT NULL COMMENT "date customer needs to receive order (NULL = no deadline)",
 
−
  PRIMARY KEY(`ID`)
 
−
)
 
−
ENGINE = MYISAM;</mysql>
 
−
<section end=sql />
 
  
−
* Normal sorting order is: '''CONCAT(IFNULL(SortPfx,<nowiki>''</nowiki>),Number)'''
+
===Customers===
−
** The fields have to be concatenated, else the non-NULL SortPfx records always get sorted after the rest. "IFNULL(SortPfx,Number),Number" also works; not sure which is computationally cheaper.
+
There ought to be a better way to organize this stuff, but I haven't been able to think of one that doesn't cause worse problems. Each type of customer data has its own set of fields and its own search-optimization, and combining them results in the awkward possibility of pulling up the wrong type of data under certain circumstances. So, until something better comes along...
−
* The customer contact information schema needs to be significantly reworked:
 
−
** Orders should save a string of the actual name used
 
−
** There should be some kind of "customer" record which contains the current defaults, as well as alternatives, for names & contact info
 
−
* '''WhenOpened''', '''WhenClosed''', and other When* fields may be obsolete; there should be an event log for orders so a complete activity history can be pulled up.
 
−
* If not dealing with legacy data, '''WhenStarted''' should be NOT NULL.
 
  
−
====ord_lines====
+
* [[/cust]] - main customer records
−
<section begin=sql /><mysql>CREATE TABLE `ord_lines` (
+
* [[/cust_addr]] - customer mailing addresses (shipping and credit card)
−
  `ID`            INT NOT NULL AUTO_INCREMENT,
+
* [[/cust_cards]] - customer credit card numbers - '''sensitive data'''
−
  `ID_Order`      INT NOT NULL COMMENT "core_orders.ID",
+
* [[/cust_emails]] - customer email addresses
−
  `Seq`            INT DEFAULT NULL COMMENT "sequence in which items were added to order",
+
* <s>[[/cust_names]] - customer names/aliases</s> - deprecated
−
  `ID_Item`        INT NOT NULL COMMENT "cat_item.ID",
+
* [[/cust_phones]] - customer phone numbers
−
  `CatNum`        VARCHAR(63) DEFAULT NULL COMMENT "catalog number as ordered",
+
* [[/cust_charges]] - credit card charges - '''sensitive data''' (includes card #s)
−
  `Descr`          VARCHAR(255) DEFAULT NULL COMMENT "description as ordered",
 
−
  `QtyOrd`        INT DEFAULT NULL COMMENT "quantity ordered",
 
−
  `Price`          DECIMAL(9,2) DEFAULT NULL COMMENT "price quoted for this item",
 
−
  `ShipPkg`        DECIMAL(9,2) DEFAULT NULL COMMENT "per-package shipping quoted",
 
−
  `ShipItm`        DECIMAL(9,2) DEFAULT NULL COMMENT "per-item shipping quoted",
 
−
  `isShipEst`      BOOL DEFAULT FALSE COMMENT "TRUE = shipping charge is an estimate",
 
−
  `WhenAdded`      DATETIME DEFAULT NULL COMMENT "when this item was added to the order",
 
−
  `Notes`          VARCHAR(255) DEFAULT NULL COMMENT "admin-added notes",
 
−
  `VbzUser`        VARCHAR(127) COMMENT "VbzCart username, for when we have a user system",
 
−
  `Machine`        VARCHAR(64) COMMENT "network name (or IP address) of machine from which the event was initiated, if applicable",
 
−
  PRIMARY KEY(`ID`)
 
−
)
 
−
ENGINE = MYISAM;</mysql>
 
−
<section end=sql />
 
−
* For installations without legacy data to accomodate, the following fields should probably be NOT NULL: '''CatNum, Descr, QtyOrd, Price, ShipPkg, ShipItem, WhenAdded'''
 
−
* The whole '''isShipEst''' thing is awkward and not very useful. We might just want to ditch it and state that all shipping charges are subject to revision but we will wait for customer approval if the shipping charge is over the price quoted.
 
  
−
====ord_pkgs====
+
===Users===
−
<section begin=sql /><mysql>CREATE TABLE `ord_pkgs` (
+
Customers can be users, but only when they've created a user account. Users can also be admins and (eventually) suppliers and vendors.
−
  `ID`            INT NOT NULL AUTO_INCREMENT,
+
* [[/user]]
−
  `Seq`            INT COMMENT "number used to make user-friendly pkg #",
+
** [[/user_tokens]] - for emailing password-reset links
−
  `ID_Order`      INT NOT NULL COMMENT "core_orders.ID",
+
** [[/user_client]]
−
  `WhenStarted`    DATETIME COMMENT "when package record was created",
+
** [[/user_session]]
−
  `WhenFinished`  DATETIME, /* need to check code for actual purpose of field */
+
** '''access control''':
−
  `WhenChecked`    DATETIME, /* need to check code for actual purpose of field */
+
*** [[/ugroup]] - groups to which users may belong
−
  `WhenVoided`    DATETIME COMMENT "when package was emptied; can be reused later",
+
*** [[/uperm]] - permissions a group may have
−
  `isReturn`      BOOL COMMENT "TRUE = this package is being returned, not shipped out",
+
*** [[/user_x_ugroup]] - groups to which each user belongs
−
  `ID_Shipment`    INT COMMENT "core_shipments.ID",
+
*** [[/ugroup_x_uperm]] - permissions each group has
−
  `ShipCost`      DECIMAL(9,2) COMMENT "cost of shipping (postage)",
 
−
  `PkgCost`        DECIMAL(9,2) COMMENT "cost of packaging / insurance",
 
−
  `ShipPounds`    FLOAT COMMENT "shipping weight in pounds (whole or fractional)",
 
−
  `ShipOunces`    FLOAT COMMENT "shipping weight in ounces, less ShipPounds",
 
−
  `ShipNotes`      VARCHAR(255) COMMENT "human-entered notes about this pkg",
 
−
  `ShipTracking`  VARCHAR(127) COMMENT "shipper's tracking number for pkg",
 
−
  `WhenArrived`    DATETIME COMMENT "when arrived at customer, if known",
 
−
  PRIMARY KEY(`ID`)
 
−
)
 
−
ENGINE = MYISAM;</mysql>
 
−
<section end=sql />
 
−
* The field "ArrivalNotes" should be added to the message log; leaving it out of the migration for this table.
 
−
* '''WhenVoided''': We're not deleting packages anymore. Instead, we mark an emptied, unsent package as "VOID", and log it in the container record. Voided package records can be re-used if they aren't assigned to a closed shipment; log the un-voiding prior to re-use. (Should there be a "WhenLocked" field to represent the shipment being closed? Maybe WhenFinished does this...) A package should never be voided without first removing all line items.
 
−
 
 
−
====ord_pkg_lines====
 
−
<section begin=sql /><mysql>CREATE TABLE `ord_pkg_lines` (
 
−
  `ID`          INT NOT NULL AUTO_INCREMENT,
 
−
  `ID_Pkg`      INT NOT NULL COMMENT "ord_pkgs.ID",
 
−
  `ID_OrdLine`  INT COMMENT "ord_lines.ID",
 
−
  `ID_Item`      INT NOT NULL COMMENT "cat_item",
 
−
  `QtyShipped`  INT COMMENT "quantity shipped/charged in this package",
 
−
  `QtyExtra`    INT COMMENT "quantity tossed in as freebies in this pkg",
 
−
  `QtyKilled`    INT COMMENT "quantity fulfilled by being cancelled",
 
−
  `QtyNotAvail`  INT COMMENT "quantity which can't be filled",
 
−
  `QtyFromStock` INT COMMENT "quantity which was moved from stock",
 
−
  `Notes`        VARCHAR(255),
 
−
  PRIMARY KEY(`ID`)
 
−
)
 
−
ENGINE = MYISAM;</mysql>
 
−
<section end=sql />
 
−
* '''QtyFromStock''' may be an obsolete field, as ''all'' package quantities should now come from stock.
 
−
====ord_event_types====
 
−
<section begin=sql /><mysql>CREATE TABLE `ord_event_types` (
 
−
  `ID`          INT NOT NULL AUTO_INCREMENT,
 
−
  `Display`      VARCHAR(63) COMMENT "brief name for this event type",
 
−
  `Descr`        VARCHAR(127) COMMENT "longer description of usage",
 
−
  `isActive`    BOOL COMMENT "TRUE = order is open or on hold",
 
−
/* these fields all have 3 possible values:
 
−
0 = no change
 
−
-1 = FORBID / TURN OFF
 
−
+1 = ALLOW / TURN ON
 
−
*/
 
−
  `doRestock`  TINYINT COMMENT "allow/forbid restocking this order's items",
 
−
  `doCharge`  TINYINT COMMENT "allow/forbid charging bank card for this order",
 
−
  `doContact`  TINYINT COMMENT "turn on/off need-to-contact flag for this order",
 
−
  `doExamine`  TINYINT COMMENT "turn on/off need-to-examine flag for this order",
 
−
  PRIMARY KEY(`ID`)
 
−
)
 
−
ENGINE = MYISAM;</mysql>
 
−
<section end=sql />
 
−
====ord_events====
 
−
<section begin=sql /><mysql>CREATE TABLE `ord_events` (
 
−
  `ID`        INT NOT NULL AUTO_INCREMENT,
 
−
  `ID_Ord`    INT NOT NULL COMMENT "core_orders.ID",
 
−
  `ID_Type`  INT NOT NULL COMMENT "ord_event_types.ID",
 
−
  `WhenDone`  DATETIME COMMENT "when the event happened",
 
−
  `Notes`    VARCHAR(255) COMMENT "human-entered notes, if needed",
 
−
  PRIMARY KEY(`ID`)
 
−
)
 
−
ENGINE = MYISAM;</mysql>
 
−
<section end=sql />
 
  
 
===Restocking===
 
===Restocking===
−
====core_restocks====
+
* [[/rstk_req]] - restock request master record (was [[/core_restock_new]])
−
<section begin=sql /><mysql>CREATE TABLE `core_restocks` (
+
* [[/rstk_req_item]] - catalog item in a restock request
−
  `ID`            INT NOT NULL AUTO_INCREMENT,
+
* [[/rstk_ord_line]] - list of customer orders needing those items (distribution)
−
  `ID_Supplier`    INT NOT NULL COMMENT "cat_supp.ID of supplier",
+
* [[/rstk_rcd]] - restock shipment received
−
  `ID_Warehouse`  INT COMMENT "cat_supp.ID of receiving warehouse",
+
* [[/rstk_rcd_line]] - items received in a restock shipment
−
  `PurchOrdNum`    VARCHAR(63) COMMENT "our purchase order number",
+
* [[/rstk_req_item_minima]] - items types with minimum supplier orders
−
  `SuppOrdNum`    VARCHAR(63) COMMENT "supplier's order number",
 
−
  `SuppInvcNum`    VARCHAR(63) COMMENT "supplier's invoice number",
 
−
  `CarrierDescr`  VARCHAR(63) COMMENT "shipping carrier (UPS, USPS...)",
 
−
/* eventually there will be a table of carriers so we can retrieve tracking info or at least pull it up from the web */
 
−
  `TrackingCode`  VARCHAR(63) COMMENT "carrier's tracking number",
 
−
  `TotalCalcMerch` DECIMAL(9,2) COMMENT "total cost of merchandise calculated by us",
 
−
  `TotalEstFinal`  DECIMAL(9,2) COMMENT "estimate of final charge",
 
−
  /* amounts from supplier invoice */
 
−
  `TotalInvMerch`  DECIMAL(9,2) COMMENT "total cost of merchandise invoiced",
 
−
  `TotalInvShip`  DECIMAL(9,2) COMMENT "total shipping cost invoiced",
 
−
  `TotalInvAdj`    DECIMAL(9,2) COMMENT "total invoice adjustments",
 
−
/* Final = Merch + Ship + Adj */
 
−
  `TotalInvFinal`  DECIMAL(9,2) COMMENT "final total on invoice (must match amt paid)",
 
−
  `PayMethod`      VARCHAR(63) COMMENT "how payment was made (typically: which credit card?)",
 
−
  `WhenCreated`    DATETIME DEFAULT NULL COMMENT "when restock order was started (created)",
 
−
  `WhenOrdered`    DATETIME DEFAULT NULL COMMENT "when order was placed with supplier",
 
−
  `WhenConfirmed`  DATETIME DEFAULT NULL COMMENT "when order was confirmed with supplier",
 
−
  `WhenKilled`    DATETIME DEFAULT NULL COMMENT "when order was negated, cancelled, or otherwise terminated unsuccessfully",
 
−
  `WhenShipped`    DATETIME DEFAULT NULL COMMENT "when supplier shipped the package",
 
−
  `WhenReceived`  DATETIME DEFAULT NULL COMMENT "when package was received from supplier",
 
−
  `WhenDebited`    DATETIME DEFAULT NULL COMMENT "when charge for order was debited",
 
−
  `WhenExpectedOrig` DATETIME DEFAULT NULL COMMENT "when we originally expected to receive order",
 
−
  `WhenExpectedFinal` DATETIME DEFAULT NULL COMMENT "most recent ETA",
 
−
  `isLocked`      BOOL DEFAULT FALSE COMMENT "TRUE = items may not be edited",
 
−
  `isInvcComplete` BOOL DEFAULT FALSE COMMENT "NO = part of invoice is missing (lost, not filed, etc.); YES = we have complete invoice",
 
−
  `isInvcPartial`  BOOL DEFAULT FALSE COMMENT "NO = invoice is all together; YES = part of invoice is missing",
 
−
  `isInvcAbsent`  BOOL DEFAULT FALSE COMMENT "YES = no invoice paperwork from supplier (invoice is known to exist from bank records or earlier data entry)",
 
−
  `Notes`          TEXT DEFAULT NULL COMMENT "human-entered notes",
 
−
  PRIMARY KEY(`ID`)
 
−
)
 
−
ENGINE = MYISAM;</mysql>
 
−
<section end=sql />
 
−
* '''TotalEstFinal''' has been largely unnecessary, but it may become useful again for budget planning (especially during heavy buying seasons)
 
−
* '''isInvc*''' fields are meaningless if restock package has not yet been received; one or both of WhenReceived and WhenDebited should be non-NULL in this case, but there may be some situations where we don't know either of those -- so there should probably be an isRecd flag (add this later).
 
−
 
 
−
====rstk_items====
 
−
<section begin=sql /><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 COMMENT "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 */
 
−
  `CostExpPer`  DECIMAL(9,2) COMMENT "expected per-item cost",
 
−
  `CostInvPer`  DECIMAL(9,2) COMMENT "invoiced per-item cost",
 
−
  `CostInvTot`  DECIMAL(9,2) COMMENT "invoice line total (CostExpPer x InvcQtySent) for this item",
 
−
  `CostActTot`  DECIMAL(9,2) COMMENT "actual (best) line total as used for reconciling",
 
−
  `CostActBal`  DECIMAL(9,2) COMMENT "running total, calculated from CostActTot; may be unnecessary anywhere except Access",
 
−
  `Notes`      VARCHAR(255) COMMENT "human-entered notes on this line item",
 
−
  PRIMARY KEY(`ID`)
 
−
)
 
−
ENGINE = MYISAM;</mysql>
 
−
<section end=sql />
 
−
* if '''isGone''' is TRUE, then ideally:
 
−
** don't carry items forward into next restock
 
−
** mark item as discontinued in catalog
 
−
 
 
 
===Shopping===
 
===Shopping===
 
These tables store data generated by the user during the shopping process.
 
These tables store data generated by the user during the shopping process.
−
====shop_carts====
+
* [[/shop_cart]]
−
*'''carts''' -- shopping [[VbzCart Cart|cart]]s
+
** [[/shop_cart_line]]
−
{| style="background: #eeeeee;"
+
** [[/shop_cart_event]]
−
|-
+
** [[/cart_data]]
−
|'''ID'''#@ || int(4)
+
** [[/shop_cart_data]] - OBSOLETE
−
|-
 
−
|'''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===
 
===Stock Management===
−
====stk_items====
+
* [[/stk_lines]]
−
listing of what is now and what used to be in stock; depends on '''stk_bins''' and '''cat_items''' tables
+
* [[/stk_bins]]
−
<section begin=sql /><mysql> DROP TABLE IF EXISTS `stk_items`;
+
** [[/stk_bin_history]]
−
CREATE TABLE `stk_items` (
+
* [[/stk_places]]
−
  `ID` INT  NOT NULL AUTO_INCREMENT,
+
* [[/stk_history]]
−
  `ID_Bin` INT DEFAULT NULL COMMENT 'stk_bins.ID: which bin these items are in',
+
* [[/stk_whse]]: warehouses
−
  `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 />
 
  
−
====stk_bins====
+
===Caching===
−
containers in which stock may be found; depends on [[#stk_places]]
+
====Management====
−
<section begin=sql /><mysql> DROP TABLE IF EXISTS `stk_bins`;
+
See [[User:Woozle/datamgr.php]] for the code that handles this.
−
+
* [[/cache_tables]] - list of tables involved in data caching, either as a source or as a cache
−
CREATE TABLE `stk_bins` (
+
* [[/cache_queries]] - list of [[../procs|stored procedures]] which update one table from others
−
  `ID` int(11) NOT NULL auto_increment,
+
* [[/cache_flow]] - which queries update which caches from which sources
−
  `ID_Place` int(11) NOT NULL COMMENT 'stk_places.ID',
+
* [[/cache_log]] - log of all cache updates done within cache management
−
  `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>
 
−
<section end=sql />
 
−
* '''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====
+
====Calculated Tables====
−
places where containers may be found, organized hierarchically
+
These are used for caching data which takes a long time (more than 0.3 seconds or so) to calculate and which depends on things which change a lot less often than pages are viewed.
−
<section begin=sql /><mysql> DROP TABLE IF EXISTS `stk_places`;
+
* [[/_depts]]
−
+
** &larr; depts x suppliers &ndash; {{vbzcart|proc|Upd_Depts_fr_Depts_Suppliers}}
−
CREATE TABLE `stk_places` (
+
** &larr; _dept_ittyps by ID_Dept &ndash; {{vbzcart|proc|Upd_Depts_fr_DeptIttyps}}
−
  `ID` INT  NOT NULL AUTO_INCREMENT,
+
* [[/_titles]]
−
  `ID_Parent` INT COMMENT 'self.ID of place in which this place is found; may be NULL',
+
* [[/_title_ittyps]] - list of available titles for each item type
−
  `Name` VARCHAR(63) NOT NULL COMMENT 'brief descriptive name for listings and tree-paths',
+
** &larr; cat_items (grouped by ID_Title), cat_titles &ndash; {{vbzcart|proc|Upd_TitleIttyps_fr_CatItems_Titles}}
−
  `Descr` VARCHAR(127) COMMENT '(optional) description of this place, so it can be located',
+
** &larr; _titles &ndash; {{vbzcart|proc|Upd_TitleIttyps_fr_Titles}}
−
  PRIMARY KEY(`ID`)
+
** &larr; cat_ittyps &ndash; {{vbzcart|proc|Upd_TitleIttyps_fr_CatIttyps}}
−
) ENGINE = MYISAM;</mysql>
+
* [[/_dept_ittyps]]
−
<section end=sql />
+
** &larr; _title_ittyps &ndash; {{vbzcart|proc|Upd_DeptIttyps_fr_TitleIttyps}}
−
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:
+
** &larr; cat_ittyps &ndash; {{vbzcart|proc|Upd_DeptIttyps_fr_CatIttyps}}
−
  `isLocal` BOOL COMMENT `FALSE = items aren't here, so they must either be shipped here first or shipped to customer in separate pkg`,
+
* [[/_supplier_ittyps]]
−
  `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`,
+
** &larr; suppliers x _titles x _title_ittyps x cat_ittyps ... &ndash; {{vbzcart|proc|Upd_SupplierIttyps}}
−
  `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_containers]]
−
====stk_history====
+
** &larr; stk_bins, cat_items... &ndash; {{vbzcart|proc|Upd_StkContainers}}
−
log of all stock movement (depends on [[#stk_containers]] and [[#cat_items]]):
 
−
<section begin=sql /><mysql>DROP TABLE IF EXISTS `stk_history`;
 
  
−
CREATE TABLE `stk_history` (
+
===Event Logging===
−
  `ID` INT  NOT NULL AUTO_INCREMENT,
+
* {{l/ferreteria}} tables:
−
  `ID_Stock` INT NOT NULL COMMENT "stk_items.ID of item being moved",
+
** {{l/ferreteria/table|event}}: EventPlex core
−
  `ID_Item` INT /* NOT NULL */ COMMENT "cat_items.ID of item being moved",
+
** {{l/ferreteria/table|event_done}}
−
  `Qty` INT NOT NULL COMMENT "number of items moved (Qty fields at targ/dest may change value later)",
+
** {{l/ferreteria/table|event_in_table}}
−
  `When` DATETIME NOT NULL COMMENT "when the move happened",
+
** {{l/ferreteria/table|event_notes}}
−
  `IDS_ContSrce` varchar(31) /* NOT NULL */ COMMENT "_stk_containers.IDS of where the item was moved from",
+
* Custom EventPlex extensions
−
  `IDS_ContDest` varchar(31) /* NOT NULL */ COMMENT "_stk_containers.IDS of where the item was moved to",
+
** [[/event_vc_legacy]] - old log data
−
  `IDS_LineSrce` varchar(31) /* NOT NULL */ COMMENT "hypothetical _stk_lines.IDS of where item came from",
+
** [[/event_vc_bin]] - stock bin log
−
  `IDS_LineDest` varchar(31) /* NOT NULL */ COMMENT "hypothetical _stk_lines.IDS of where item was moved to",
+
** [[/event_vc_ord_hold]] - order status log
−
  `Notes` varchar(255) DEFAULT NULL COMMENT "optional explanatory notes",
+
*** [[/ord_hold_type]]
−
  PRIMARY KEY(`ID`)
+
** to be converted:
−
) ENGINE = MYISAM;</mysql>
+
*** [[/stk_history]]
−
<section end=sql />
+
* obsolete:
−
 
+
** [[/event_log]] - now renamed <code>OLD event_log</code>, soon to be removed
−
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).
+
** [[/shop_cart_event]] - now renamed <code>OLD shop_cart_event</code>, to be removed after checking that data has been migrated
−
 
+
** [[/ord_pull]]
−
Commented-out bits can be included if you have no incomplete legacy data to deal with.
+
*** [[/ord_pull/migration]]
−
====stk_bin_history====
+
** [[/shop_cart_event]]
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
===Browsing===
 
−
Topics and images
 
−
====brs_topics====
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
 
 
−
====brs_titles_x_topics====
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
  
 
===Miscellaneous===
 
===Miscellaneous===
−
* '''stats''' &ndash; computed variables, cache timestamps, and other variable stuff
+
* [[/ref_country]] - list of countries
−
<section begin=sql /><mysql> CREATE TABLE `stats` (
+
** [[/ref_country_alias]] - list of possible country name spellings
−
    `Name` varchar(32) NOT NULL COMMENT 'name of variable',
+
* [[/var_global]]
−
    `Value` varchar(255) COMMENT 'value of variable',
+
* {{l/vc|archive/tables}} - discarded tables
−
  PRIMARY KEY(`Name`)
 
−
) ENGINE = MYISAM;</mysql>
 
−
<section end=sql />
 
−
 
 
−
* '''updates''' &ndash; timestamps on tables so we know when update queries need to be run
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
 
 
−
Function to record an update:
 
−
<section begin=sql /><mysql> CREATE PROCEDURE SetUpdate(IN iName varchar(32))
 
−
BEGIN
 
−
  REPLACE INTO updates(Name,Updated) values(iName, NOW());
 
−
END</mysql>
 
−
<section end=sql />
 
−
 
 
−
==Calculated Tables==
 
−
===_depts===
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
====&larr; depts x suppliers====
 
−
SQL to fill _depts (except inPrint/inStock fields):
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
====&larr; _dept_ittyps by ID_Dept====
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
===_titles===
 
−
<section begin=sql /><mysql> 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;</mysql>
 
−
<section end=sql />
 
−
==== &larr; titles x _depts ====
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
==== &larr; cat_items (grouped by ID_Title) ====
 
−
<section begin=sql /><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,
 
−
        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;</mysql>
 
−
<section end=sql />
 
−
 
 
−
==== &larr; _title_ittyps (grouped by ID_Title)====
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
 
 
−
===_title_ittyps===
 
−
list of available titles for each item type
 
−
<section begin=sql /><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` 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;</mysql>
 
−
<section end=sql />
 
−
 
 
−
* '''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)
 
−
==== &larr; 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.)
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
* '''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
 
−
==== &larr; _titles ====
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
==== &larr; cat_ittyps ====
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
 
 
−
===_dept_ittyps===
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
====&larr; _title_ittyps====
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
====&larr; cat_ittyps====
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
 
 
−
===_supplier_ittyps===
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
====&larr; suppliers x _titles x _title_ittyps x cat_ittyps ...====
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
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:
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
==== &larr; stk_bins, cat_items... ====
 
−
SQL to update (replace/fill) _stk_containers (in progress; Packages and Restocks not yet migrated):
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
  
−
==Data Management==
+
==Revisions Under Consideration==
−
These tables are all maintained by hand, but must match what actually happens in the SQL code.
+
===2011-09-26===
−
===data_tables===
+
The [[/cat_depts]] table is seeming increasingly useless and an obstacle to good data design. Suppliers are prone to assigning items to different departments every year as their offerings change, and few or none of them use departments as part of their catalog key. In cases where they do, the amount of time saved by not having to key in 2-4 extra characters per catalog number simply isn't worth the extra maintenance and coding to make this work reliably.
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
===data_procs===
 
−
List of stored procedures which update one table from others
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
===data_flow===
 
−
Keeps track of data dependencies between tables
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
===data_log===
 
−
Log of all data updates performed internally
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
==Miscellaneous==
 
−
===error/message log===
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
* 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
 
  
 +
The function of the cat_depts table can be replaced by the [[/brs_topics]] table (which should be renamed '''cat_topics''').
  
 +
Once you start thinking about life after cat_depts, however, one has to ask whether [[/cat_supp]] should also be rolled into brs_topics. Obviously suppliers do, unlike departments, have some data significance: suppliers have catalogs, catalog groups, price functions, restock history... most of which data goes in other tables, not cat_supp itself, so it wouldn't be hard to use topics for suppliers too.
  
−
==Future Changes==
+
That would make it easier to allow the same item to be purchasable from multiple suppliers -- but how do you handle catalog numbers then? The same item can have different catalog numbers depending on where it was ordered from? Possibly this makes sense, but it still would cause trouble: each item still only has one item record, and that record needs a catalog number. Do you then move catalog numbers into a separate table that records item-to-supplier relationships?
−
''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:
+
Is there any stopping point, short of "going all the way", that seems sensible? Can't we just eliminate departments for now, and worry about further enhancements later after mopping up all the code changes that that alone will require? That's probably a sensible compromise.
−
*'''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.
 

Latest revision as of 13:47, 3 March 2020

Current Tables

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_topic: categorization
  • /cat_ship_cost: shipping costs for different types of item
  • /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

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

Catalog Entry

These are tables used to make it easier to update the local catalog; see catalog/supplier and catalog/building.

File Management

  • /fm_node: a filesystem entity (could be either file or folder)
  • /fm_file: files of any type
  • /fm_folder: places where files can be stored
  • /cat_images: files that are images, with additional metadata

Ordering

Cart data and line-items are converted to orders and order lines when the customer confirms the order. Orders become the focal point for a lot of other activity, such as shipping, transactions, and tracking of status changes.

Customers

There ought to be a better way to organize this stuff, but I haven't been able to think of one that doesn't cause worse problems. Each type of customer data has its own set of fields and its own search-optimization, and combining them results in the awkward possibility of pulling up the wrong type of data under certain circumstances. So, until something better comes along...

Users

Customers can be users, but only when they've created a user account. Users can also be admins and (eventually) suppliers and vendors.

Restocking

Shopping

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

Stock Management

Caching

Management

See User:Woozle/datamgr.php for the code that handles this.

Calculated Tables

These are used for caching data which takes a long time (more than 0.3 seconds or so) to calculate and which depends on things which change a lot less often than pages are viewed.

Event Logging

Miscellaneous

Revisions Under Consideration

2011-09-26

The /cat_depts table is seeming increasingly useless and an obstacle to good data design. Suppliers are prone to assigning items to different departments every year as their offerings change, and few or none of them use departments as part of their catalog key. In cases where they do, the amount of time saved by not having to key in 2-4 extra characters per catalog number simply isn't worth the extra maintenance and coding to make this work reliably.

The function of the cat_depts table can be replaced by the /brs_topics table (which should be renamed cat_topics).

Once you start thinking about life after cat_depts, however, one has to ask whether /cat_supp should also be rolled into brs_topics. Obviously suppliers do, unlike departments, have some data significance: suppliers have catalogs, catalog groups, price functions, restock history... most of which data goes in other tables, not cat_supp itself, so it wouldn't be hard to use topics for suppliers too.

That would make it easier to allow the same item to be purchasable from multiple suppliers -- but how do you handle catalog numbers then? The same item can have different catalog numbers depending on where it was ordered from? Possibly this makes sense, but it still would cause trouble: each item still only has one item record, and that record needs a catalog number. Do you then move catalog numbers into a separate table that records item-to-supplier relationships?

Is there any stopping point, short of "going all the way", that seems sensible? Can't we just eliminate departments for now, and worry about further enhancements later after mopping up all the code changes that that alone will require? That's probably a sensible compromise.

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