|
|
| Line 4: |
Line 4: |
| | * [[/customer]] tables | | * [[/customer]] tables |
| | | | |
| − | ==Tables== | + | ==Main Data== |
| − | *"#" indicates Primary Key fields
| |
| − | *"@" indicates autonumbered fields
| |
| | ===Catalog=== | | ===Catalog=== |
| | These tables describe and price the items displayed in the catalog pages and in shopping carts. | | These tables describe and price the items displayed in the catalog pages and in shopping carts. |
| Line 26: |
Line 24: |
| | ====future==== | | ====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.) | | 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.) |
| − | ====leftover stuff====
| |
| − | Are these still being used? To be checked. 2008-12-06
| |
| − |
| |
| − | 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 />
| |
| − |
| |
| − | <section begin=sql /><mysql>CREATE OR REPLACE VIEW _cat_pages AS
| |
| − | SELECT
| |
| − | CONCAT_WS('-','S',ID) AS AB,
| |
| − | LOWER(CatKey) AS Path,
| |
| − | ID,
| |
| − | 'S' AS Type
| |
| − | FROM cat_supp
| |
| − | UNION
| |
| − | SELECT
| |
| − | CONCAT_WS('-','D',ID) AS AB,
| |
| − | LOWER(CatWeb_Dept) AS Path,
| |
| − | ID,
| |
| − | 'D' AS Type
| |
| − | FROM qryCat_Depts
| |
| − | UNION
| |
| − | SELECT
| |
| − | CONCAT_WS('-','T',ID) AS AB,
| |
| − | REPLACE(LOWER(CatWeb),'-','/') AS Path,
| |
| − | ID,
| |
| − | 'T' AS Type
| |
| − | FROM qryCat_Titles
| |
| − | UNION
| |
| − | /* image pages (every size except thumbnail and small) */
| |
| − | SELECT
| |
| − | CONCAT_WS('-','I',i.ID) AS AB,
| |
| − | LOWER(CONCAT_WS('/',REPLACE(t.CatWeb,'-','/'),AttrFldr,i.Ab_Size)) AS Path,
| |
| − | i.ID,
| |
| − | 'I' AS Type
| |
| − | FROM cat_images AS i LEFT JOIN qryCat_Titles AS t ON i.ID_Title=t.ID WHERE (i.isActive) AND (i.Ab_Size NOT IN ('th','sm'));</mysql>
| |
| − | <section end=sql />
| |
| − |
| |
| − | <section begin=sql /><mysql>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 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. |
| Line 94: |
Line 30: |
| | | | |
| | See [[VbzCart catalog building]] for queries and processes. | | See [[VbzCart catalog building]] for queries and processes. |
| − | ====ctg_supp====
| + | * [[/ctg_supp]] |
| − | Information about suppliers which isn't needed for customer web site
| + | * [[/ctg_prc_funcs]] |
| − | <section begin=sql /><mysql>CREATE TABLE `ctg_supp` (
| + | * [[/ctg_sources]] |
| − | ID INT NOT NULL COMMENT "cat_supp.ID - supplier to which information applies",
| + | * [[/ctg_groups]] |
| − | ID_PriceFunc INT DEFAULT NULL COMMENT "ctg_prc_funcs.ID - function to use when calculating prices for this supplier",
| + | * [[/ctg_titles]] |
| − | PRIMARY KEY(`ID`)
| + | * [[/ctg_items]] |
| − | )
| + | * [[/ctg_updates]] |
| − | ENGINE = MYISAM;</mysql>
| + | * [[/ctg_upd1]] |
| − | <section end=sql />
| + | * [[/ctg_upd2]] |
| − | ====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),
| |
| − | PRIMARY KEY(`IDS_Item`)
| |
| − | )
| |
| − | ENGINE = MYISAM;</mysql>
| |
| − | <section end=sql />
| |
| − | | |
| − | ====ctg_upd2====
| |
| − | Experimental, but hopefully will replace ctg_updates which never worked entirely properly.
| |
| − | <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",
| |
| − | isForSale BOOL DEFAULT FALSE COMMENT "to be recalculated (assume false, then set true if available or in stock)",
| |
| − | QtyInStock INT DEFAULT NULL COMMENT "to be recalculated",
| |
| − | Supp_CatNum VARCHAR(32) COMMENT "preserved from cat_items",
| |
| − | QtyMin_Stk INT DEFAULT NULL COMMENT "preserved from cat_items",
| |
| − | cntDups INT DEFAULT 0,
| |
| − | PRIMARY KEY(`IDS_Item`),
| |
| − | UNIQUE KEY(`ID_Item`)
| |
| − | )
| |
| − | ENGINE = MYISAM;</mysql>
| |
| − | <section end=sql />
| |
| | | | |
| | ===Ordering=== | | ===Ordering=== |
| Line 275: |
Line 65: |
| | * [[/core_restocks]] - OBSOLETE | | * [[/core_restocks]] - OBSOLETE |
| | * [[/rstk_lines]] - OBSOLETE | | * [[/rstk_lines]] - OBSOLETE |
| − |
| |
| | ===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_carts]] |
| − | Shopping cart data is kept separate from order data because we end up with a lot of carts that never become orders; eventually they get cleaned out. Order data may eventually get cleaned out too, but with different criteria; for now, we are keeping order data indefinitely.
| + | * [[/shop_cart_lines]] |
| − | <section begin=sql /><mysql>DROP TABLE IF EXISTS `shop_carts`;
| + | * [[/shop_cust_data]] |
| − | CREATE TABLE `shop_carts` (
| + | * [[/shop_cust_data_types]] |
| − | `ID` INT NOT NULL AUTO_INCREMENT,
| + | * [[/shop_log]] |
| − | `ID_Session` INT DEFAULT NULL COMMENT "shop_sessions.ID, when implemented -- session in which cart was created",
| + | * [[/shop_clients]] |
| − | `WhenCreated` DATETIME NOT NULL COMMENT "when the cart was first created",
| + | * [[/shop_sessions]]: to be implemented. A new session should be created whenever the "same" client doesn't remember the session cookie (could be a different computer on the same IP address), or if the session has timed out. |
| − | `WhenViewed` DATETIME DEFAULT NULL COMMENT "when the cart's contents were last displayed by the customer",
| |
| − | `WhenUpdated` DATETIME DEFAULT NULL COMMENT "when the cart's contents were last changed",
| |
| − | `WhenOrdered` DATETIME DEFAULT NULL COMMENT "when the cart's contents were transferred to an order",
| |
| − | `ID_Order` INT DEFAULT NULL COMMENT "core_orders.ID of order into which cart was transferred",
| |
| − | `ID_Cust` INT DEFAULT NULL COMMENT "core_custs.ID of customer for this order",
| |
| − | PRIMARY KEY(`ID`)
| |
| − | ) ENGINE = MYISAM;</mysql>
| |
| − | <section end=sql />
| |
| − | * '''ID_Cust''' is for future use when customers can log in to retrieve their personal data | |
| − | * '''ID_Session''' is for future use when sessions are tracked
| |
| − | | |
| − | ====shop_cart_lines====
| |
| − | <section begin=sql /><mysql>DROP TABLE IF EXISTS `shop_cart_lines`;
| |
| − | CREATE TABLE `shop_cart_lines` (
| |
| − | `ID` INT NOT NULL AUTO_INCREMENT,
| |
| − | `ID_Cart` INT NOT NULL COMMENT "shop_carts.ID",
| |
| − | `ID_Item` INT NOT NULL COMMENT "cat_items.ID",
| |
| − | `Qty` INT NOT NULL COMMENT "quantity ordered; 0 = removed from order",
| |
| − | `WhenAdded` DATETIME NOT NULL COMMENT "when this item was first added to the order",
| |
| − | `WhenEdited` DATETIME NOT NULL COMMENT "when the quantity for this item was last changed",
| |
| − | `PriceItem` DECIMAL(9,2) NOT NULL COMMENT "price of item quoted at order time",
| |
| − | `PriceShItm` DECIMAL(9,2) NOT NULL COMMENT "per-item shipping quoted for this item at order time",
| |
| − | `PriceShPkg` DECIMAL(9,2) NOT NULL COMMENT "per-package shipping quoted for this item at order time",
| |
| − | /* These fields are redundant, and are used mainly to speed up cart display. They also preserve a record of
| |
| − | how the item was presented to the customer. */
| |
| − | `CatNum` VARCHAR(63) COMMENT "cat_items.CatNum of item as sold",
| |
| − | `ID_Title` INT NOT NULL COMMENT "cat_titles.ID",
| |
| − | `DescText` VARCHAR(255) NOT NULL COMMENT "plain-text description of item being ordered (e.g. for emails)",
| |
| − | `DescHtml` VARCHAR(255) NOT NULL COMMENT "HTML description of item being ordered, for web-page display",
| |
| − | PRIMARY KEY(`ID`)
| |
| − | ) ENGINE = MYISAM;</mysql>
| |
| − | <section end=sql />
| |
| − | ====shop_cust_data====
| |
| − | This is a simple table for retaining customer data entered at check-out time before converting the cart to an order.
| |
| − | <section begin=sql /><mysql>DROP TABLE IF EXISTS `shop_cust_data`;
| |
| − | CREATE TABLE `shop_cust_data` (
| |
| − | `ID` INT NOT NULL AUTO_INCREMENT,
| |
| − | `ID_Cart` INT NOT NULL COMMENT "shop_carts.ID",
| |
| − | `ID_Key` INT NOT NULL COMMENT "shop_cust_data_types.ID of data type",
| |
| − | `Val` VARCHAR(255) NOT NULL COMMENT "Value of data item",
| |
| − | PRIMARY KEY(`ID`)
| |
| − | ) ENGINE = MYISAM;</mysql>
| |
| − | <section end=sql />
| |
| − | ====shop_cust_data_types====
| |
| − | This table is probably unnecessary, but I can't convince myself of that. If nothing else, it will help prevent inconsistent usage.
| |
| − | <section begin=sql /><mysql>DROP TABLE IF EXISTS `shop_cust_data_types`;
| |
| − | CREATE TABLE `shop_cust_data_types` (
| |
| − | `ID` INT NOT NULL AUTO_INCREMENT,
| |
| − | `Name` VARCHAR(31) NOT NULL COMMENT "Short name, for reports or whatever",
| |
| − | `Descr` VARCHAR(255) DEFAULT NULL COMMENT "Longer description of data item, e.g. usage notes",
| |
| − | PRIMARY KEY(`ID`)
| |
| − | ) ENGINE = MYISAM;</mysql>
| |
| − | <section end=sql />
| |
| − | ====shop_log====
| |
| − | Under construction
| |
| − | <section begin=sql /><mysql>DROP TABLE IF EXISTS `shop_log`;
| |
| − | CREATE TABLE `shop_log` (
| |
| − | `ID` INT NOT NULL AUTO_INCREMENT,
| |
| − | `ID_Client` INT NOT NULL COMMENT "shop_clients.ID",
| |
| − | `EvWhen` DATETIME NOT NULL COMMENT "When this event occurred",
| |
| − | `Code` INT NOT NULL COMMENT "type of event",
| |
| − | `Descr` VARCHAR(255) DEFAULT NULL COMMENT "Any details specific to this occurrence",
| |
| − | `Notes` VARCHAR(255) DEFAULT NULL COMMENT "Human-entered notes",
| |
| − | PRIMARY KEY(`ID`)
| |
| − | ) ENGINE = MYISAM;</mysql>
| |
| − | <section end=sql />
| |
| − | ====shop_clients====
| |
| − | Under construction
| |
| − | <section begin=sql /><mysql>DROP TABLE IF EXISTS `shop_clients`;
| |
| − | CREATE TABLE `shop_clients` (
| |
| − | `ID` INT NOT NULL AUTO_INCREMENT,
| |
| − | `CRC` INT UNSIGNED NOT NULL COMMENT "crc32(Address+Browser) - unique integer defined by client specs",
| |
| − | `Address` VARCHAR(15) NOT NULL COMMENT "IP address of client",
| |
| − | `Domain` VARCHAR(255) COMMENT "Domain name of client",
| |
| − | `Browser` VARCHAR(255) COMMENT "Browser USER_AGENT string",
| |
| − | `WhenFirst` DATETIME NOT NULL COMMENT "When this client was first seen",
| |
| − | `WhenFinal` DATETIME COMMENT "When this client was most recently seen",
| |
| − | PRIMARY KEY(`ID`),
| |
| − | UNIQUE KEY(`CRC`)
| |
| − | ) ENGINE = MYISAM;</mysql>
| |
| − | <section end=sql />
| |
| − | * Decided to use CRC32 instead of compression, because the compressed strings always came out at least as long as the original string. They were probably designed to be effective for rather longer data.
| |
| − | | |
| − | ====shop_sessions====
| |
| − | To be implemented. A new session should be created whenever the "same" client doesn't remember the session cookie (could be a different computer on the same IP address), or if the session has timed out.
| |
| | | | |
| | ===Stock Management=== | | ===Stock Management=== |
| Line 381: |
Line 85: |
| | ===Browsing=== | | ===Browsing=== |
| | Topics and images | | Topics and images |
| − | ====brs_topics====
| + | * [[/brs_topics]] |
| − | <section begin=sql /><mysql>DROP TABLE IF EXISTS `brs_topics`;
| + | * [[/brs_titles_x_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===
| |
| − | * '''stats''' – computed variables, cache timestamps, and other variable stuff
| |
| − | <section begin=sql /><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>
| |
| − | <section end=sql />
| |
| − | | |
| − | * '''updates''' – 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== | | ==Calculated Tables== |
| − | ===_depts===
| + | 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 a lot less often than pages are viewed. |
| − | <section begin=sql /><mysql> DROP TABLE IF EXISTS `_depts`;
| + | * [[/_depts]] |
| − | CREATE TABLE `_depts` (
| + | ** ← depts x suppliers – {{vbzcart|proc|Upd_Depts_fr_Depts_Suppliers}} |
| − | `ID` int(11) unsigned NOT NULL auto_increment,
| + | ** ← _dept_ittyps by ID_Dept – {{vbzcart|proc|Upd_Depts_fr_DeptIttyps}} |
| − | `ID_Supp` int(11) unsigned NOT NULL COMMENT 'needed to simplify calculation of _titles',
| + | * [[/_titles]] |
| − | `CatNum` varchar(63) NOT NULL,
| + | ** ← titles x _depts – {{vbzcart|proc|Upd_Titles_fr_Depts}} |
| − | `CatKey` varchar(7),
| + | ** ← cat_items (grouped by ID_Title) – {{vbzcart|proc|Upd_Titles_fr_CatItems}} |
| − | `CatWeb_Dept` varchar(63) NOT NULL,
| + | ** ← _title_ittyps (grouped by ID_Title) – {{vbzcart|proc|Upd_Titles_fr_TitleIttyps}} |
| − | `CatWeb_Title` varchar(63) NOT NULL,
| + | * [[/_title_ittyps]] - list of available titles for each item type |
| − | `cntForSale` int DEFAULT NULL,
| + | ** ← cat_items (grouped by ID_Title), cat_titles – {{vbzcart|proc|Upd_TitleIttyps_fr_CatItems_Titles}} |
| − | `cntInPrint` int DEFAULT NULL,
| + | ** ← _titles – {{vbzcart|proc|Upd_TitleIttyps_fr_Titles}} |
| − | `qtyInStock` int DEFAULT NULL,
| + | ** ← cat_ittyps – {{vbzcart|proc|Upd_TitleIttyps_fr_CatIttyps}} |
| − | PRIMARY KEY (`ID`)
| + | * [[/_dept_ittyps]] |
| − | ) ENGINE=MyISAM DEFAULT CHARSET=latin1;</mysql>
| + | ** ← _title_ittyps – {{vbzcart|proc|Upd_DeptIttyps_fr_TitleIttyps}} |
| − | <section end=sql />
| + | ** ← cat_ittyps – {{vbzcart|proc|Upd_DeptIttyps_fr_CatIttyps}} |
| − | ====← depts x suppliers====
| + | * [[/_supplier_ittyps]] |
| − | SQL to fill _depts (except inPrint/inStock fields):
| + | ** ← suppliers x _titles x _title_ittyps x cat_ittyps ... – {{vbzcart|proc|Upd_SupplierIttyps}} |
| − | <section begin=sql /><mysql> CREATE PROCEDURE Upd_Depts_fr_Depts_Suppliers()
| + | * [[/_stk_containers]] |
| − | BEGIN
| + | ** ← stk_bins, cat_items... – {{vbzcart|proc|Upd_StkContainers}} |
| − | DELETE FROM _depts;
| + | ==Data Flow Management== |
| − | INSERT INTO _depts(`ID`,`ID_Supp`,`CatKey`,`CatNum`,`CatWeb_Dept`,`CatWeb_Title`)
| + | These tables are all maintained by hand (except for timestamps and data_log), but must match the actual dependencies in the SQL code. |
| − | SELECT
| + | * [[/data_tables]] - list of tables involved in data caching, either as a source or as a cache |
| − | d.ID,
| + | * [[/data_procs]] - list of [[../procs|stored procedures]] which update one table from others |
| − | d.ID_Supplier AS ID_Supp,
| + | * [[/data_flow]] - which procs update which caches from which sources |
| − | UPPER(IFNULL(d.PageKey,d.CatKey)) AS CatKey,
| + | * [[/data_log]] - log of all cache updates done automatically |
| − | 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 />
| |
| − | ====← _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 />
| |
| − | ==== ← 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 />
| |
| − | ==== ← 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 />
| |
| − | | |
| − | ==== ← _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) | |
| − | ==== ← 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
| |
| − | ==== ← _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 />
| |
| − | ==== ← 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 />
| |
| − | ====← _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 />
| |
| − | ====← 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 />
| |
| − | ====← 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 />
| |
| − | ==== ← 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== | |
| − | These tables are all maintained by hand, but must match what actually happens in the SQL code. | |
| − | ===data_tables===
| |
| − | <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== | | ==Miscellaneous== |
| − | ===error/message log===
| + | * [[/stats]] |
| − | This is a log specifically of administration activity (shipping, catalog updates, etc.) so should probably be renamed admin_log or similar. There will be a separate log for shopping activity.
| + | * [[/event_log]] |
| − | <section begin=sql /><mysql>DROP TABLE IF EXISTS `event_log`;
| + | * [[/discarded]] - discarded tables |
| − |
| |
| − | 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
| |
| − | | |
| − | ==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.
| |