Difference between revisions of "VbzCart/tables/shop cart"
Jump to navigation
Jump to search
(changing to singular) |
(cart can be used in multiple sessions, so removing ID_Session field) |
||
| Line 1: | Line 1: | ||
==About== | ==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. | * '''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 {{vbzcart|table|shop_cart}} has zero or one {{vbzcart|table|core_orders}} record (it's only zero until the order is placed) | ||
| + | ** each {{vbzcart|table|core_orders}} record has exactly one {{vbzcart|table|shop_cart}} | ||
| + | ** 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}} | ||
* '''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 | ||
| Line 6: | Line 11: | ||
* '''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 | ||
==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` | + | `ID` INT NOT NULL AUTO_INCREMENT, |
| − | + | `WhenCreated` DATETIME NOT NULL COMMENT "when the cart was first created", | |
| − | `WhenCreated` DATETIME NOT NULL | ||
`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` | + | `ID_Order` INT DEFAULT NULL COMMENT "core_orders.ID of order into which cart was transferred", |
| − | `ID_Cust` | + | `ID_Cust` INT DEFAULT NULL COMMENT "core_custs.ID of customer for this order", |
PRIMARY KEY(`ID`) | PRIMARY KEY(`ID`) | ||
) ENGINE = MYISAM;</mysql> | ) ENGINE = MYISAM;</mysql> | ||
<section end=sql /> | <section end=sql /> | ||
Revision as of 19:15, 10 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
- ID_Session is for future use when sessions are tracked
- 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
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", PRIMARY KEY(`ID`) ) ENGINE = MYISAM;</mysql>