VbzCart/docs/tables/cust cards: Difference between revisions
Jump to navigation
Jump to search
imported>Woozle (→Fields: not a rigorous format) |
imported>Woozle m (more tidying) |
||
| Line 9: | Line 9: | ||
* '''2009-07-09''' moved to separate wiki page; no design changes | * '''2009-07-09''' moved to separate wiki page; no design changes | ||
* '''2009-10-07''' | * '''2009-10-07''' | ||
** removed CryptAddr, renamed CryptCard as Encrypted | ** removed CryptAddr, renamed CryptCard as Encrypted | ||
** added ID_Name | ** added ID_Name | ||
* '''2011-11-22''' added WhenEnt and WhenUpd fields | |||
* '''2013-09-01''' added '''CardSafe''' | * '''2013-09-01''' added '''CardSafe''' | ||
==Future== | ==Future== | ||
| Line 18: | Line 18: | ||
* '''isActive''' should eventually go away; probably the best thing is to wait until all isActive=FALSE cards have expired (end of Feb 2010), then set WhenInvalid to each card's expiry date. | * '''isActive''' should eventually go away; probably the best thing is to wait until all isActive=FALSE cards have expired (end of Feb 2010), then set WhenInvalid to each card's expiry date. | ||
==SQL== | ==SQL== | ||
<mysql>CREATE TABLE `cust_cards` ( | |||
`ID` INT NOT NULL AUTO_INCREMENT, | `ID` INT NOT NULL AUTO_INCREMENT, | ||
`WhenEnt` DATETIME NOT NULL COMMENT "date when record was first created", | `WhenEnt` DATETIME NOT NULL COMMENT "date when record was first created", | ||
| Line 39: | Line 39: | ||
) | ) | ||
ENGINE = MYISAM;</mysql> | ENGINE = MYISAM;</mysql> | ||
Revision as of 22:13, 1 September 2013
About
- Purpose: for recording the information necessary to get paid with credit/debit cards
Fields
- WhenInvalid: if NOT NULL, then this number has been permanently destroyed/deactivated; any use of this number is therefore suspicious. Date is the earliest date it was known to be bad. Overrides isActive.
- Address may be no longer necessary, except for handling legacy data
- CardSafe: human-readable (and searchable) summary of "safe" card data. Format subject to change.
History
- 2009-07-09 moved to separate wiki page; no design changes
- 2009-10-07
- removed CryptAddr, renamed CryptCard as Encrypted
- added ID_Name
- 2011-11-22 added WhenEnt and WhenUpd fields
- 2013-09-01 added CardSafe
Future
- Address should go away, probably without any need to verify that ID_Addr agrees with it
- Records with OwnerName need to have ID_Name set, and then the OwnerName field can be removed
- isActive should eventually go away; probably the best thing is to wait until all isActive=FALSE cards have expired (end of Feb 2010), then set WhenInvalid to each card's expiry date.
SQL
<mysql>CREATE TABLE `cust_cards` (
`ID` INT NOT NULL AUTO_INCREMENT, `WhenEnt` DATETIME NOT NULL COMMENT "date when record was first created", `WhenUpd` DATETIME DEFAULT NULL COMMENT "date when record was last updated", `Name` VARCHAR(31) COMMENT "human-assigned name for this card", `ID_Cust` INT COMMENT "core_custs.ID", `ID_Name` INT COMMENT "cust_names.ID", `ID_Addr` INT COMMENT "cust_addrs.ID of card's billing address", `CardNum` VARCHAR(31) COMMENT "charge card number", `CardExp` DATE COMMENT "expiration date", `CardCVV` VARCHAR(7) DEFAULT NULL COMMENT "CVV(2) code", `CardSafe` VARCHAR(127) NOT NULL COMMENT "unencrypted 'safe' card info in human-readable format", `OwnerName` VARCHAR(127) COMMENT "cardholder name, as it appears on card", `Address` VARCHAR(255) COMMENT "text of address, for redundancy", `Encrypted` VARCHAR(127) COMMENT "encrypted card number, CVV, expiration", `isActive` BOOL DEFAULT FALSE COMMENT "FALSE = don't use this number", `WhenInvalid` DATETIME DEFAULT NULL COMMENT "deactivation timestamp", `Notes` VARCHAR(255) DEFAULT NULL COMMENT "human-entered notes about this card", PRIMARY KEY(`ID`)
) ENGINE = MYISAM;</mysql>