Difference between revisions of "VbzCart/tables/cust cards"
Jump to navigation
Jump to search
(added WhenEnt and WhenUpd) |
(CardSafe; tidying) |
||
| Line 1: | Line 1: | ||
==About== | ==About== | ||
| − | * '''Purpose''': for recording the information necessary to get paid | + | * '''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: T-''nnnn'' x''nnnn'' p''postal_code'' |
| − | + | ==History== | |
| − | + | * '''2009-07-09''' moved to separate wiki page; no design changes | |
| − | + | * '''2009-10-07''' | |
| − | + | * '''2011-11-22''' added WhenEnt and WhenUpd fields | |
| − | + | ** removed CryptAddr, renamed CryptCard as Encrypted | |
| − | * ''' | + | ** added ID_Name |
| − | + | * '''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== | ==SQL== | ||
<section begin=sql /><mysql>CREATE TABLE `cust_cards` ( | <section begin=sql /><mysql>CREATE TABLE `cust_cards` ( | ||
`ID` INT NOT NULL AUTO_INCREMENT, | `ID` INT NOT NULL AUTO_INCREMENT, | ||
| − | `WhenEnt` DATETIME | + | `WhenEnt` DATETIME NOT NULL COMMENT "date when record was first created", |
| − | `WhenUpd` DATETIME DEFAULT NULL COMMENT "date when record was last updated", | + | `WhenUpd` DATETIME DEFAULT NULL COMMENT "date when record was last updated", |
| − | `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_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", |
| − | `CardExp` DATE COMMENT "expiration date", | + | `CardExp` DATE COMMENT "expiration date", |
| − | `CardCVV` VARCHAR(7) DEFAULT NULL COMMENT "CVV(2) code", | + | `CardCVV` VARCHAR(7) DEFAULT NULL COMMENT "CVV(2) code", |
| − | `OwnerName` VARCHAR(127) COMMENT "cardholder name, as it appears on card", | + | `CardSafe` VARCHAR(127) NOT NULL COMMENT "unencrypted 'safe' card info in human-readable format", |
| − | `Address` VARCHAR(255) COMMENT "text of address, for redundancy", | + | `OwnerName` VARCHAR(127) COMMENT "cardholder name, as it appears on card", |
| − | `Encrypted` VARCHAR(127) COMMENT "encrypted card number, CVV, expiration", | + | `Address` VARCHAR(255) COMMENT "text of address, for redundancy", |
| − | `isActive` BOOL DEFAULT FALSE COMMENT "FALSE = don't use this number", | + | `Encrypted` VARCHAR(127) COMMENT "encrypted card number, CVV, expiration", |
| − | `WhenInvalid` DATETIME DEFAULT NULL COMMENT "deactivation timestamp", | + | `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", | `Notes` VARCHAR(255) DEFAULT NULL COMMENT "human-entered notes about this card", | ||
PRIMARY KEY(`ID`) | PRIMARY KEY(`ID`) | ||
Revision as of 21:49, 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: T-nnnn xnnnn ppostal_code
History
- 2009-07-09 moved to separate wiki page; no design changes
- 2009-10-07
- 2011-11-22 added WhenEnt and WhenUpd fields
- removed CryptAddr, renamed CryptCard as Encrypted
- added ID_Name
- 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>