VbzCart/tables/orders

About

Fields

• WhenPlaced indicates when order the order was officially submitted by the customer. If this isn't NULL, then the customer should receive a new order record if they checkout again within the same session (or the code should ask if they're updating an existing order; it should never assume they're doing that).
• WhenClosed is set automatically when the order-processing code notices that all items ordered have been dealt with. If an order needs to be closed manually (either because the code for automatic closure did not work or because it had not yet been written), then the order should be Pulled with a pull-type of "Completed". (Should this field be renamed to "WhenComplete"? Or should this field be set whenever an order is Pulled, and cleared whenever the active Pull is cleared? In the latter scenario, need to do something about the orders where this field is set but there is no corresponding Pull.)
• WhenRevised is when the order was last modified by the customer.
• WhenEdited is when the order record was last modified by anyone (auto-update)
• WhenHeld is set as long as there's an active hold on the order; if NULL, then none are set

Future

• The fact that this only records cust_name.ID for buyer and seller, rather than core_cust.ID, makes the code more complicated in certain ways, and may be something to think about when we're remediating this table:
  • pulling up a list of orders for a given customer
  • providing links from orders to the corresponding customer records
  • looking up which credit cards are appropriate choices for an order
• Some fields to rename (wait until after final migration of current order data): These fields have now been removed:
  • ID_NameBuyer -> ID_BuyerName
  • ID_NameRecip -> ID_RecipName
  • ID_ContactAddrRecip -> ID_RecipAddr
  • ID_ContactPhone -> ID_RecipPhone
  • ID_ContactEmail -> ID_RecipEmail
• We should probably add email and phone fields for the buyer too, since they might conceivably want to be able to receive order confirmations for different orders at different email addresses... ask Board of Advisors about this. See history, 2009-10-29; also see note about using core_custs.ID instead of cust_name.ID. 2009-11-28: decided to eventually replace ID_NameBuyer and ID_NameRecip with ID_Buyer and ID_Recip -- see History.

History

• 2008-11-20 Changing field names:
  • WhenOpened -> WhenImported (this won't be set once order data is kept live)
  • WhenClosed -> WhenClosed_old (we want "WhenClosed" to be a timestamp for when the order was finished, and we don't know what this legacy field was actually for)
  • WhenUpdated -> WhenUpdated_old (no longer being used)
• 2009-10-04 WhenClosed_old and WhenUpdated_old fields removed (from Rizzo version of db); data moved to core_orders_legacy -- now we can finally upload the latest data to the main server
• 2009-10-19 added ID_Cart field
• 2009-10-22
  • renamed WhenImported to WhenPrepped, changed definition slightly
• 2009-10-29 We never used the ID_ContactPhone and ID_ContactEmail fields, so they are being removed until we find is some compelling design reason to start using them. Board of advisors says different phone/email for different orders doesn't really make sense; should always use whatever phone/email is current.
• 2009-11-28 added ID_Buyer and ID_Recip to point directly to core_custs; this will eventually replace ID_NameBuyer and ID_NameRecip.
• 2013-11-07 significant revision now in progress
  • renamed from core_orders to orders
  • going to start recording shipping details-as-used on a per-order basis
    • adding BuyerName, RecipName, RecipAddr
    • removing ID_NameBuyer. ID_NameRecip, ID_ContactAddrRecip
• 2014-09-14 Changes to accommodate user selection of pre-existing data:
  • renamed ID_ChargeCard -> ID_BuyerCard
  • added ID_RecipAddr
• 2015-09-04 Changing WhenPrepped to WhenPlaced
  • This used to be the timestamp for when the order was converted from cart data to order data, but we're doing that automatically (when the customer confirms the order) now and the field is no longer used. We do need a timestamp to indicate that an order has been placed (so don't re-use the order record -- at least, not without asking a human), so that's what this field is going to be now. The meaning is similar enough that it seemed reasonable to use the same field.
• 2016-03-06
  • Migrating data from MYISAM engine to INNODB. In the process, cleaned some things up:
    • Got rid of obsolete PassCode field.
    • Renamed WhenEdited to WhenRevised, and adding a new WhenEdited that means something slightly different.
    • See migration for more.
• 2016-06-23 Rewriting cart-to-order import, so fixing some timestamps:
  • WhenPorted is when the Cart was converted to an Order
    • (renamed WhenStarted to WhenPorted, because this used to be the definition of WhenStarted)
  • WhenCarted is when the cart was created (i.e. copy Cart.WhenCreated)
• 2017-06-03 replacing ID_Pull with WhenHeld (order-pull system substantially reworked)

Notes

• Normal sorting order is: CONCAT(IFNULL(SortPfx,''),Number)
  • 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.
• 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
• There should eventually be an event log for orders so a complete activity history can be pulled up, which may render some of the When* fields obsolete.
• If not dealing with legacy data, WhenStarted should be NOT NULL.
• As implemented for vbz, there were originally some additional fields for storing legacy data; these were eventually discarded when the data didn't seem to be relevant:
  • WhenClosed_old: it's not clear what this field actually did
  • WhenUpdated_old: this was originally supposed to be set if the order was changed before being imported, but it doesn't seem to have been used since 2001-02-09. This information would be better stored in a log file anyhow.

SQL

CREATE TABLE `orders` (
  `ID`             INT NOT NULL AUTO_INCREMENT,
  `Number`         VARCHAR(63)      NOT NULL COMMENT "order number for human reference; generated by web site",
  `SortPfx`        VARCHAR(7)   DEFAULT NULL COMMENT "sorting prefix -- earlier prefix schemes sort wrong, and this overrides that",
  `ID_Cart`                 INT DEFAULT NULL COMMENT "ID of cart used to create this order",
  `ID_Pull`                 INT DEFAULT NULL COMMENT "ord_pulls.ID: ID of the currently active Pull for this order (NULL = order is active)",
  `ID_Buyer`                INT DEFAULT NULL COMMENT "core_custs.ID -- purchaser, contact for discussing order",
  `ID_Recip`                INT DEFAULT NULL COMMENT "core_custs.ID -- shipping destination",
  `BuyerName`      VARCHAR(127) DEFAULT NULL COMMENT "name of purchaser, contact for discussing order",
  `RecipName`      VARCHAR(127) DEFAULT NULL COMMENT "name of recipient, for shipping address",
  `RecipAddr`      VARCHAR(255) DEFAULT NULL COMMENT "shipping address, as used/formatted",
  `PayType`             INT DEFAULT NULL COMMENT "aux_pay_types.code -- payment method used for this order",
  `ID_BuyerCard`        INT DEFAULT NULL COMMENT "cust_cards.ID -- bank card which should be debited for this order",
  `ID_RecipAddr`        INT DEFAULT NULL COMMENT "cust_addrs.ID -- ship-to address",
  `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",
  `WhenCreated`         DATETIME NOT NULL NULL COMMENT "when the order record was first created (blank)",
  `WhenCarted`          DATETIME  DEFAULT NULL COMMENT "when corresponding cart record was created",
  `WhenPorted`          DATETIME  DEFAULT NULL COMMENT "when cart record was converted to an order",
  `WhenPlaced`          DATETIME  DEFAULT NULL COMMENT "when customer officially placed the order",
  `WhenRevised`         DATETIME  DEFAULT NULL COMMENT "when order was last edited by the customer",
  `WhenEdited`          TIMESTAMP NULL ON UPDATE NOW() COMMENT "when order record was last modified by anyone",
  `WhenNeeded`          DATETIME  DEFAULT NULL COMMENT "when customer needs to receive order (NULL = no deadline)",
  `WhenHeld`            DATETIME  DEFAULT NULL COMMENT "most recent timestamp of an order hold (NULL = none active)",
  `WhenClosed`          DATETIME  DEFAULT NULL COMMENT "when order was completed (all items shipped, cancelled, or unavailable)",
  PRIMARY KEY(`ID`)
)
ENGINE = INNODB;

Legacy data

This is just for storing some legacy fields which we will probably never need to refer to, but I hesitate to just back up the data and delete them. Design created 2009-10-02.

CREATE TABLE `core_orders_legacy` (
  `ID_Order`        INT NOT NULL,
  `WhenClosed_old`  DATETIME DEFAULT NULL COMMENT "possibly indicated that import was complete?",
  `WhenUpdated_old` DATETIME DEFAULT NULL COMMENT "was only set in 8 records; maybe indicates admin revision during import?",
  PRIMARY KEY(`ID_Order`)
)
ENGINE = MYISAM;

INSERT INTO `core_orders_legacy` (ID_Order, WhenClosed_old, WhenUpdated_old)
SELECT
  ID,
  WhenClosed_old,
  WhenUpdated_old
FROM core_orders
WHERE (WhenClosed_old IS NOT NULL) OR (WhenUpdated_old IS NOT NULL);