VbzCart/docs/tables/shop cart

<hide> thing type::table design [[project::VbzCart]] subsystem::shopping status::active </hide>

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 Template:Vbzcart has one or more Template:Vbzcarts
    • a single session can discard one cart and create another
    • sessions only use carts they create, never reusing one created by another session
  • each Template:Vbzcart exactly one Template:Vbzcart
  • each Template:Vbzcart has zero or one Template:Vbzcart record (it's only zero until the order is placed)
  • each Template:Vbzcart record has exactly one Template:Vbzcart
  • each Template:Vbzcart has zero or one Template:Vbzcart record (it's only zero until the order is placed, but ID_Cust isn't necessarily filled in when this happens)
  • each Template:Vbzcart record has one or more Template:Vbzcarts and one or more Template:Vbzcart
• Rules:
  • Each Template:Vbzcart has zero or one Template:Vbzcarts (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 (Template:Vbzcart) 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 Template:L/vc/table. 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;