VbzCart/tables/shop cart

About

• Purpose: 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.
• Relations:
  • each shop_session has one or more shop_carts
    • a single session can discard one cart and create another
    • sessions only use carts they create, never reusing one created by another session
  • each shop_cart exactly one shop_session
  • each shop_cart has zero or one core_orders record (it's only zero until the order is placed)
  • each core_orders record has exactly one shop_cart
  • each shop_cart has zero or one core_custs record (it's only zero until the order is placed, but ID_Cust isn't necessarily filled in when this happens)
  • each core_custs record has one or more shop_carts and one or more core_orders
• Rules:
  • Each user_session has zero or one shop_carts (currently stored in custom ID_Cart field, but will eventually be Stashed)
    • A single session may discard one cart and start a new one.
    • Sessions only use carts they create, never reusing one created by another session (but see Security, below)
    • Each session knows only the cart it is currently using.

Fields

• ID_Cust is for future use when customers can log in to retrieve their personal data before checking out
• ID_Sess is the ID of the session (shop_session) where the cart was created & accessed
  • Different sessions cannot access the same cart. (Is there a good reason for this, or does it just sound good?)
  • One session may have multiple carts.
• ShipZone represents the string-code for the shipping zone, which is used to determine shipping cost
  • currently, this is hard-coded to apply a different multiplier for each zone - {US shipping cost}x{zone multiplier}
• FieldData is a serialized array of the data formerly stored in shop_cart_data. This is an experimental field which will become permanent if it works out.

History

• 2009-06-16 Changing names to singular (tables not in use yet, so this is the time to do it)
• 2009-07-10 removing ID_Session: each session ties to a cart, not vice-versa
  • ...except that we still have ID_Sess
• 2009-07-16 added ShipZone field
• 2009-07-18 added ID_Sess field (field documentation seems to think it was already there...)
• 2011-03-27 added WhenVoided field so we never zero out ID_Cart
• 2016-03-07 added FieldData field
• 2016-06-23 renaming WhenOrdered to WhenPorted: when Cart record was used to create an Order (not when the Order was formally submitted)
• 2016-06-24 removing WhenViewed, since code never sets it (NULL in all existing records) and the purpose for which it was probably intended (diagnostic) is better served by the event log.

SQL

CREATE TABLE `shop_cart` (
  `ID`          INT            NOT NULL AUTO_INCREMENT,
  `WhenCreated` DATETIME       NOT NULL COMMENT "when the cart was first created",
  `WhenUpdated` DATETIME   DEFAULT NULL COMMENT "when the cart's contents were last changed",
  `WhenPorted`  DATETIME   DEFAULT NULL COMMENT "when the cart's contents were transferred to an order",
  `WhenVoided`  DATETIME   DEFAULT NULL COMMENT "when this cart was discarded",
  `ID_Sess`     INT            NOT NULL COMMENT "shop_session.ID which used this cart",
  `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",
  `ShipZone`    VARCHAR(3) DEFAULT NULL COMMENT "shipping cost zone",
  `FieldData`   TEXT       DEFAULT NULL COMMENT "other values associated with the cart (serialized)",
   PRIMARY KEY(`ID`)
 ) ENGINE = INNODB;