VbzCart/docs/tables/shop cart: Difference between revisions

From Woozle Writes Code
< VbzCart‎ | docs‎ | tables
Jump to navigation Jump to search
imported>Woozle
(decided Stash will be much easier to debug if it's TEXT instead of a BLOB)
imported>Woozle
(some rules moved from Ferreteria user_session page)
Line 16: Line 16:
** each {{vbzcart|table|shop_cart}} has zero or one {{vbzcart|table|core_custs}} record (it's only zero until the order is placed, but ID_Cust isn't necessarily filled in when this happens)
** each {{vbzcart|table|shop_cart}} has zero or one {{vbzcart|table|core_custs}} record (it's only zero until the order is placed, but ID_Cust isn't necessarily filled in when this happens)
** each {{vbzcart|table|core_custs}} record has one or more {{vbzcart|table|shop_cart}}s and one or more {{vbzcart|table|core_orders}}
** each {{vbzcart|table|core_custs}} record has one or more {{vbzcart|table|shop_cart}}s and one or more {{vbzcart|table|core_orders}}
* '''Rules''':
** Each {{vbzcart|table|user_session}} has zero or one {{vbzcart|table|shop_cart}}s (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===
===Fields===
* '''ID_Cust''' is for future use when customers can log in to retrieve their personal data before checking out
* '''ID_Cust''' is for future use when customers can log in to retrieve their personal data before checking out
Line 35: Line 41:
* '''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.
* '''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==
==SQL==
<mysql>DROP TABLE IF EXISTS `shop_cart`;
<mysql>CREATE TABLE `shop_cart` (
CREATE TABLE `shop_cart` (
   `ID`          INT            NOT NULL AUTO_INCREMENT,
   `ID`          INT            NOT NULL AUTO_INCREMENT,
   `WhenCreated` DATETIME      NOT NULL COMMENT "when the cart was first created",
   `WhenCreated` DATETIME      NOT NULL COMMENT "when the cart was first created",

Revision as of 11:17, 10 February 2017

<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:
  • 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

<mysql>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>
Retrieved from "https://wooz.dev/mw/index.php?title=VbzCart/docs/tables/shop_cart&oldid=6107"