VbzCart/tables/customer

From HTYP, the free directory anyone can edit

Jump to: navigation, search

Contents

[edit] Navigation

computing: software: web: shopping carts: VbzCart: tables: customer tables

[edit] Overview

There ought to be a better way to organize this stuff, but I haven't been able to think of one that doesn't cause worse problems. Each type of customer data has its own set of fields and its own search-optimization, and combining them results in the awkward possibility of pulling up the wrong type of data under certain circumstances. So, until something better comes along...

[edit] Tables

[edit] core_custs

Core customer table – all the other customer data tables point to this one 

CREATE TABLE `core_custs` (
  `ID`          INT NOT NULL AUTO_INCREMENT,
  `ID_Name`     INT NOT NULL COMMENT "cust_names.ID of default name",
  `ID_Addr`     INT DEFAULT NULL COMMENT "cust_addrs.ID of default address",
  `Notes`       VARCHAR(255) COMMENT "admin-entered notes about this customer",
  `ID_Repl`     INT DEFAULT NULL COMMENT "core_custs.ID of replacement",
  `WhenCreated` DATETIME COMMENT "when this record was created",
  `WhenChanged` DATETIME DEFAULT NULL COMMENT "when this record was last edited",
  PRIMARY KEY(`ID`)
)
ENGINE = MYISAM;


  • ID_Repl: If non-null, then this customer ID has been consolidated with the customer whose ID is in ID_Repl; whenever/wherever possible, replace occurrences of this ID with the value of the corresponding ID_Repl.
  • For systems with no legacy data, WhenCreated should be NOT NULL

[edit] cust_addrs

CREATE TABLE `cust_addrs` (
  `ID`         INT NOT NULL AUTO_INCREMENT,
  `ID_Cust`    INT COMMENT "core_custs.ID",
  `Name`       VARCHAR(31) COMMENT "human-assigned name for this address",
  `WhenAct`    DATETIME COMMENT "date when first active/usable; NULL = always",
  `WhenExp`    DATETIME COMMENT "date when no longer usable; NULL = never",
  `Full`       VARCHAR(255) COMMENT "Full address (street, town, zip, country)",
  `Search`     VARCHAR(255) COMMENT "Full address, normalized for searching",
  `Search_raw` VARCHAR(255) COMMENT "Text used to create Search field",
  `Street`     VARCHAR(127) COMMENT "address within town (street, apt., etc.)",
  `Town`       VARCHAR(63) COMMENT "name of town",
  `State`      VARCHAR(63) COMMENT "state or province name/code",
  `Zip`        VARCHAR(31) COMMENT "zipcode or postal code",
  `Country`    VARCHAR(63) COMMENT "name of country",
  `Extra`      VARCHAR(63) COMMENT "additional instructions to always write on pkg",
  `Descr`      VARCHAR(255) COMMENT "notes on this address",
  PRIMARY KEY(`ID`)
)
ENGINE = MYISAM;
  • Name will at first be set only by us, but later on customers will be able to set it and will be increasingly in charge of it.
  • Search_raw should probably be replaced by a hash at some point, because it's just a value to make it easier to detect when Search needs recalculation
  • State probably needs to be separate fields for as-entered and code-if-known
  • ID_Cust is a new field for the migration; I had this wacky idea that customers might need to share addresses (one customer buys a present for a friend; friend sets up an account and then moves, first friend buys another present but doesn't get the updated address... egh). (Consequently, the [Contacts x Addrs] table in the Access version will be going away too.)

[edit] cust_cards

for recording charge (credit/check) card information 

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_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",
  `CryptCard`  VARCHAR(127) COMMENT "encrypted card number & expiration",
  `CryptAddr`  VARCHAR(255) COMMENT "encrypted card billing address",
  `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;
  • 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

[edit] cust_emails

CREATE TABLE `cust_emails` (
  `ID`         INT NOT NULL AUTO_INCREMENT,
  `Name`       VARCHAR(31) COMMENT "human-assigned name for this address (work, home...)",
  `ID_Cust`    INT COMMENT "core_custs.ID",
  `Email`      VARCHAR(255) NOT NULL COMMENT "the email address itself",
  `isActive`   BOOL DEFAULT FALSE COMMENT "NO=former address; do not use",
  PRIMARY KEY(`ID`)
)
ENGINE = MYISAM;

[edit] cust_names

CREATE TABLE `cust_names` (
  `ID`         INT NOT NULL AUTO_INCREMENT,
  `ID_Cust`    INT COMMENT "core_custs.ID",
  `Name`       VARCHAR(127) COMMENT "customer's name, for shipping label",
  `NameSrch`   VARCHAR(127) COMMENT "name with delimiters removed, for matching",
  `isActive`   BOOL DEFAULT FALSE COMMENT "NO=wrong spelling or name; use for searching",
  PRIMARY KEY(`ID`)
)
ENGINE = MYISAM;

[edit] cust_phones

CREATE TABLE `cust_phones` (
  `ID`         INT NOT NULL AUTO_INCREMENT,
  `ID_Cust`    INT COMMENT "core_custs.ID",
  `Name`       VARCHAR(31) COMMENT "human-assigned name (home, work...)",
  `Phone`      VARCHAR(31) COMMENT "phone number as entered/corrected",
  `PhoneSrch`  VARCHAR(31) COMMENT "Phone number in searchable form (no delimiters, no prefixes)",
  `Descr`      VARCHAR(63) COMMENT "any other notes (best times, etc.)",
  `isActive`   BOOL DEFAULT FALSE,
  PRIMARY KEY(`ID`)
)
ENGINE = MYISAM;

[edit] cust_charges

Charges to customer bank cards 

CREATE TABLE `cust_charges` (
  `ID`         INT NOT NULL AUTO_INCREMENT,
  `ID_Batch`   INT DEFAULT NULL COMMENT "batch within which this payment was processed",
  `ID_Order`   INT COMMENT "core_orders.ID of order for which this charge is being made",
  `ID_Card`    INT COMMENT "cust_cards.ID of charge card debited",
  `ID_Trxact`  INT COMMENT "cust_trxacts.ID of transaction generated by this charge",
  `AmtTrx`     DECIMAL(9,2) COMMENT "amount transacted (debited or refunded; negative number = refund)",
  `AmtSold`    DECIMAL(9,2) COMMENT "amount for items sold, before shipping/tax",
  `AmtShip`    DECIMAL(9,2) COMMENT "amount for shipping",
  `AmtTax`     DECIMAL(9,2) COMMENT "amount for tax",
  `CardNumExp`   VARCHAR(31) COMMENT "card number and expiration as used for this charge",
  `CardBillAddr` VARCHAR(255) COMMENT "card billing address as used for this charge",
  `WhenEntered`  DATETIME COMMENT "when charge was entered",
  `WhenXmitted`  DATETIME COMMENT "when processed via credit card service provider",
  `WhenHeldUp`   DATETIME COMMENT "when this charge was suspended (do not process)",
  `isSuccess`    BOOL DEFAULT FALSE COMMENT "did the charge go through?",
  `isTest`       BOOL DEFAULT FALSE COMMENT "YES = this is a test charge, to be voided if successful",
  `Confirmation` VARCHAR(31) DEFAULT NULL COMMENT "confirmation code returned by card service",
  `AVSRespCode`  VARCHAR(1) DEFAULT NULL COMMENT "Address Verification System response code",
  `AVSAuthChar`  VARCHAR(1) DEFAULT NULL COMMENT "'Auth. Char. Ind.' in PTC",
  `WhenDecided`  DATETIME DEFAULT NULL COMMENT "when we decided whether or not to accept this charge",
  `isAccepted`   BOOL DEFAULT FALSE COMMENT "NO = we decided not to accept the charge; it should be voided",
  `WhenVoided`   DATETIME DEFAULT NULL COMMENT "when the charge was voided",
  `Notes`        VARCHAR(255) COMMENT "human-entered notes on this charge",
  PRIMARY KEY(`ID`)
)
ENGINE = MYISAM;
  • Batches are needed for batch-mode card processing. This was useful when credit cards were debited using PTC, which dialed in the charges using a modem (and hence you wanted to do as much offline as possible), but it's not necessary for PayPal. Will migrate later if it seems useful. Non-batch-mode charges can leave ID_Batch set to NULL.
  • AmtSold and AmtShip are necessary for PayPal Virtual Terminal, which wants to know these amounts separately. Presumably other card processing systems may also want this information.
  • AVSAuthChar: it's not clear whether any software besides PTC returns this code. It's also not clear what it means. Only Visa cards ever seem to return this code.
  • WhenDecided and isAccepted was WhenAuthorized and WhenRefused in Access
  • I've decided the Access field Resolved should be a separate table listing unresolved charge records; this shouldn't be something that needs to happen when code is working properly.
  • WhenHeldUp is specifically for old orders where the data which would complete the charge record is missing, and I don't want to fill it in with bogus data just to get it out of the queue. I can imagine that it might be useful for suspending charges due to technical difficulties, but I can't think of a scenario in which some other solution wouldn't be more appropriate. Still: set the timestamp in this field (and make a note!) if you need to pull a charge out of the queue without completing it.
Retrieved from "http://htyp.org/VbzCart/tables/customer"
Personal tools