Difference between revisions of "VbzCart/tables/shop cart"

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
Jump to navigation Jump to search
(removed WhenViewed)
(decided Stash will be much easier to debug if it's TEXT instead of a BLOB)
Line 46: Line 46:
 
   `ID_Cust`    INT        DEFAULT NULL COMMENT "core_custs.ID of customer for this order",
 
   `ID_Cust`    INT        DEFAULT NULL COMMENT "core_custs.ID of customer for this order",
 
   `ShipZone`    VARCHAR(3) DEFAULT NULL COMMENT "shipping cost zone",
 
   `ShipZone`    VARCHAR(3) DEFAULT NULL COMMENT "shipping cost zone",
   `FieldData`  BLOB       DEFAULT NULL COMMENT "other values associated with the cart (serialized)",
+
   `FieldData`  TEXT       DEFAULT NULL COMMENT "other values associated with the cart (serialized)",
 
   PRIMARY KEY(`ID`)
 
   PRIMARY KEY(`ID`)
 
  ) ENGINE = INNODB;</mysql>
 
  ) ENGINE = INNODB;</mysql>

Revision as of 12:48, 23 December 2016

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:

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

<mysql>DROP TABLE IF EXISTS `shop_cart`; 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;</mysql>