Difference between revisions of "VbzCart/tables/shop cart"
Jump to navigation
Jump to search
(decided Stash will be much easier to debug if it's TEXT instead of a BLOB) |
(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> | + | <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
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
- each shop_session has one or more shop_carts
- 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.
- Each user_session has zero or one shop_carts (currently stored in custom ID_Cart field, but will eventually be Stashed)
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>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>