Difference between revisions of "VbzCart/tables/shop cart"
Jump to navigation
Jump to search
(cart can be used in multiple sessions, so removing ID_Session field) |
(+ShipZone field) |
||
| Line 7: | Line 7: | ||
** 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}} | ||
* '''Fields''': | * '''Fields''': | ||
| − | ** '''ID_Cust''' is for future use when customers can log in to retrieve their personal data | + | ** '''ID_Cust''' is for future use when customers can log in to retrieve their personal data before checking out |
| − | ** '''ID_Session''' is for | + | ** '''ID_Session''' is the ID of the session ({{vbzcart|table|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?) | ||
| + | ** '''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} | ||
* '''History''': | * '''History''': | ||
** '''2009-06-16''' Changing names to singular (tables not in use yet, so this is the time to do it) | ** '''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 | ** '''2009-07-10''' removing ID_Session: each session ties to a cart, not vice-versa | ||
| + | ** '''2009-07-16''' added ShipZone field | ||
==SQL== | ==SQL== | ||
<section begin=sql /><mysql>DROP TABLE IF EXISTS `shop_cart`; | <section begin=sql /><mysql>DROP TABLE IF EXISTS `shop_cart`; | ||
CREATE TABLE `shop_cart` ( | CREATE TABLE `shop_cart` ( | ||
| − | `ID` INT | + | `ID` INT NOT NULL AUTO_INCREMENT, |
| − | `WhenCreated` DATETIME | + | `WhenCreated` DATETIME NOT NULL COMMENT "when the cart was first created", |
| − | `WhenViewed` DATETIME DEFAULT NULL COMMENT "when the cart's contents were last displayed by the customer", | + | `WhenViewed` DATETIME DEFAULT NULL COMMENT "when the cart's contents were last displayed by the customer", |
| − | `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", |
| − | `WhenOrdered` DATETIME DEFAULT NULL COMMENT "when the cart's contents were transferred to an order", | + | `WhenOrdered` DATETIME DEFAULT NULL COMMENT "when the cart's contents were transferred to an order", |
| − | `ID_Order` INT | + | `ID_Order` INT DEFAULT NULL COMMENT "core_orders.ID of order into which cart was transferred", |
| − | `ID_Cust` INT | + | `ID_Cust` INT DEFAULT NULL COMMENT "core_custs.ID of customer for this order", |
| + | `ShipZone` VARCHAR(3) DEFAULT NULL COMMENT "shipping cost zone", | ||
PRIMARY KEY(`ID`) | PRIMARY KEY(`ID`) | ||
) ENGINE = MYISAM;</mysql> | ) ENGINE = MYISAM;</mysql> | ||
<section end=sql /> | <section end=sql /> | ||
Revision as of 00:58, 17 July 2009
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_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
- Fields:
- ID_Cust is for future use when customers can log in to retrieve their personal data before checking out
- ID_Session 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?)
- 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}
- 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
- 2009-07-16 added ShipZone field
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", `WhenViewed` DATETIME DEFAULT NULL COMMENT "when the cart's contents were last displayed by the customer", `WhenUpdated` DATETIME DEFAULT NULL COMMENT "when the cart's contents were last changed", `WhenOrdered` DATETIME DEFAULT NULL COMMENT "when the cart's contents were transferred to an order", `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", PRIMARY KEY(`ID`) ) ENGINE = MYISAM;</mysql>