Difference between revisions of "VbzCart/tables/shop cart"
Jump to navigation
Jump to search
(WhenOrdered -> WhenPorted) |
(removed WhenViewed) |
||
Line 33: | Line 33: | ||
* '''2016-03-07''' added '''FieldData''' field | * '''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-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== | ==SQL== | ||
<mysql>DROP TABLE IF EXISTS `shop_cart`; | <mysql>DROP TABLE IF EXISTS `shop_cart`; | ||
Line 38: | Line 39: | ||
`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", | ||
− | |||
`WhenUpdated` DATETIME DEFAULT NULL COMMENT "when the cart's contents were last changed", | `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", | `WhenPorted` DATETIME DEFAULT NULL COMMENT "when the cart's contents were transferred to an order", |
Revision as of 13:50, 24 June 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:
- 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
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` BLOB DEFAULT NULL COMMENT "other values associated with the cart (serialized)", PRIMARY KEY(`ID`) ) ENGINE = INNODB;</mysql>