VbzCart/docs/tables/orders

About

• Future:
  • Get rid of PassCode. It was originally supposed to be a way to let customers log in to manage their orders, but it was never used and doesn't seem like the way to do things.
  • Some fields to rename (wait until after final migration of current order data):
    • 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.
• 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
• 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 are actually some additional fields for storing legacy data until we figure out what to do with it:
    • 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

<section begin=sql /><mysql>CREATE TABLE `core_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",
 `PassCode`       VARCHAR(15) DEFAULT NULL COMMENT "order's security passcode - useful if user has not created an account",
 `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_NameBuyer`   INT DEFAULT NULL COMMENT "cust_names.ID -- purchaser, contact for discussing order",
 `ID_NameRecip`   INT DEFAULT NULL COMMENT "cust_names.ID -- shipping destination",
 `ID_ContactAddrRecip` INT DEFAULT NULL COMMENT "cust_addrs.ID -- shipping address to use (NULL = recipient's default address)",
 `ID_ContactPhone`     INT DEFAULT NULL COMMENT "cust_phones.ID -- phone number for this order",
 `ID_ContactEmail`     INT DEFAULT NULL COMMENT "cust_emails.ID -- email address for this order",
 `PayType`             INT DEFAULT NULL COMMENT "aux_pay_types.code -- payment method used for this order",
 `ID_ChargeCard`       INT DEFAULT NULL COMMENT "cust_cards.ID -- bank card used to pay for this order",
 `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",
 `WhenPrepped`         DATETIME DEFAULT NULL COMMENT "when order record was completed (import complete), ready to handle",
 `WhenEdited`          DATETIME DEFAULT NULL COMMENT "when order was last edited in-store",
 `WhenNeeded`          DATETIME DEFAULT NULL COMMENT "when customer needs to receive order (NULL = no deadline)",
 `WhenClosed`          DATETIME DEFAULT NULL COMMENT "when order was completed -- all items shipped, cancelled, or unavailable",
 PRIMARY KEY(`ID`)

) ENGINE = MYISAM;</mysql> <section end=sql />

Legacy SQL

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