Difference between revisions of "VbzCart/tables/cust cards"
Jump to navigation
Jump to search
(moved from "customer tables" page) |
(10/7 changes) |
||
Line 6: | Line 6: | ||
* '''History''': | * '''History''': | ||
** '''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''' | ||
+ | *** removed CryptAddr, renamed CryptCard as Encrypted | ||
+ | *** added ID_Name | ||
+ | * '''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== | ==SQL== | ||
<section begin=sql /><mysql>CREATE TABLE `cust_cards` ( | <section begin=sql /><mysql>CREATE TABLE `cust_cards` ( | ||
Line 11: | Line 18: | ||
`Name` VARCHAR(31) COMMENT "human-assigned name for this card", | `Name` VARCHAR(31) COMMENT "human-assigned name for this card", | ||
`ID_Cust` INT COMMENT "core_custs.ID", | `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", | `ID_Addr` INT COMMENT "cust_addrs.ID of card's billing address", | ||
`CardNum` VARCHAR(31) COMMENT "charge card number", | `CardNum` VARCHAR(31) COMMENT "charge card number", | ||
Line 17: | Line 25: | ||
`OwnerName` VARCHAR(127) COMMENT "cardholder name, as it appears on card", | `OwnerName` VARCHAR(127) COMMENT "cardholder name, as it appears on card", | ||
`Address` VARCHAR(255) COMMENT "text of address, for redundancy", | `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", | `isActive` BOOL DEFAULT FALSE COMMENT "FALSE = don't use this number", | ||
`WhenInvalid` DATETIME DEFAULT NULL COMMENT "deactivation timestamp", | `WhenInvalid` DATETIME DEFAULT NULL COMMENT "deactivation timestamp", |
Revision as of 14:27, 8 October 2009
About
- Purpose: for recording the information necessary to get paid by credit/debit cards
- Fields:
- if WhenInvalid is 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
- History:
- 2009-07-09 moved to separate wiki page; no design changes
- 2009-10-07
- removed CryptAddr, renamed CryptCard as Encrypted
- added ID_Name
- 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, `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", `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>