VbzCart/tables/cust addr

About

• Fields:
  • 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...) but this turned out to be kind of a stupid idea. Consequently, the [Contacts x Addrs] table in the Access version also went away.
  • Tag 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.
  • WhenVoid is when the record was voided -- distinct from WhenExp, which would be set for when a valid address record would no longer be appropriate (e.g. customer moving out on a set date)
  • Full: the complete address formatted for printing (e.g. on a label).
    • Given that postage-printing apps seem to require seperate fields, and handle the formatting themselves, this field may not be as useful as it was when labels were separate from postage.
  • Search: the complete address, with all punctuation (including spaces) stripped out - so that changes in punctuation, capitalization, spaces, etc. don't prevent a match from being found. This is recalculated automatically from edits to Name, Street, Town, State, Zip, Country.
  • 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 - it's currently (I think) just Name, Street, Town, State, Zip, and Country fields appended together (with linebreaks).
  • State probably needs to be separate fields for as-entered and code-if-known

History

• 2009-07-09 Moved to separate wiki page; no design changes
• 2011-11-22 Added WhenEnt and WhenUpd
• 2012-01-03 Added WhenVoid
• 2012-01-11 Tentative definitions for Full and Search; added ID_Country
• 2013-11-26 Renamed Name to Label; added 255-char Name field for addressee name
• 2014-08-30 Removed ID_Country for now -- not yet implemented in database, and we're not ready for it yet.
• 2019-10-25 Folding in the cust_names table, and renaming some fields
  • Full → AddrFull
  • Search → AddrSearch
  • NameSearch: NEW
  • Search_raw: REMOVED
• 2019-10-26 renaming from cust_addrs to cust_addr

SQL

CREATE TABLE `cust_addr` (
  `ID`         INT NOT NULL AUTO_INCREMENT,
  `ID_Cust`    INT COMMENT "core_custs.ID",
  `Label`      VARCHAR(31)          COMMENT "human-assigned label for this address",
  `WhenAct`    DATETIME              COMMENT "date when first active/usable; NULL = always",
  `WhenExp`    DATETIME              COMMENT "date when no longer usable; NULL = never",
  `WhenEnt`    DATETIME     NOT NULL COMMENT "when record was first created",
  `WhenUpd`    DATETIME DEFAULT NULL COMMENT "when record was last updated",
  `WhenVoid`   DATETIME DEFAULT NULL COMMENT "when this record was voided",
  `Name`       VARCHAR(255)          COMMENT "name of addressee",
  `NameSearch` VARCHAR(255)          COMMENT "name with delimiters removed, for searching",
  `AddrFull`   VARCHAR(255)          COMMENT "full address (street, town, zip, country)",
  `AddrSearch` VARCHAR(255)          COMMENT "AddrFull+Street+Town+State, normalized for searching",
  `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 as entered by customer",
  `Extra`      VARCHAR(63)           COMMENT "additional instructions to always write on pkg",
  `Descr`      VARCHAR(255)          COMMENT "notes on this address",
  PRIMARY KEY(`ID`)
)
ENGINE = InnoDB;

Migration

Rename the old table to prevent code from accidentally using it:

ALTER TABLE `vbz-vc-dev`.`cust_addrs` 
RENAME TO  `vbz-vc-dev`.`cust_addrs_old` ;

Next, try to fill in as many blank timestamps as possible:

UPDATE cust_addrs_old AS ca
        LEFT JOIN
    orders AS o ON o.ID_Buyer = ca.ID_Cust 
SET 
    ca.WhenEnt = o.WhenPorted
WHERE
    (ca.WhenEnt = 0) AND (o.WhenPorted IS NOT NULL);

UPDATE cust_addrs_old AS ca
        LEFT JOIN
    orders AS o ON o.ID_Recip = ca.ID_Cust 
SET 
    ca.WhenEnt = o.WhenPorted
WHERE
    (ca.WhenEnt = 0) AND (o.WhenPorted IS NOT NULL);

Since we're working with legacy data here, which is simply missing a lot of timestamp info, it is now necessary to override the canonical definition of WhenEnt by allowing it to be NOT NULL:

ALTER TABLE `cust_addr` 
CHANGE COLUMN `WhenEnt` `WhenEnt` DATETIME NULL COMMENT 'when record was first created' ;

Then import the main data:

INSERT `cust_addr` (
  `ID`,
  `ID_Cust`,
  `Label`,
  `WhenAct`,
  `WhenExp`,
  `WhenEnt`,
  `WhenUpd`,
  `WhenVoid`,
  `Name`,
  `NameSearch`,
  `AddrFull`,
  `AddrSearch`,
  `Street`,
  `Town`,
  `State`,
  `Zip`,
  `Country`,
  `Extra`,
  `Descr`)
SELECT 
  `ID`,
  `ID_Cust`,
  `Label`,
  `WhenAct`,
  `WhenExp`,
  IF(WhenEnt = 0,NULL,WhenEnt),
  `WhenUpd`,
  `WhenVoid`,
  `Name`,
  NULL,
  `Full`,
  `Search`,
  `Street`,
  `Town`,
  `State`,
  `Zip`,
  `Country`,
  `Extra`,
  `Descr`
  FROM cust_addrs_old;

Investigation

This seemed useful originally, but at the moment I'm not sure what it's good for:

SELECT 
        COUNT(cn.ID) AS NameCount,
        GROUP_CONCAT(cn.Name) AS Names,
        ca.*
    FROM
        cust_addr AS ca
    LEFT JOIN cust_names AS cn ON cn.ID_Cust = ca.ID_Cust
    GROUP BY ca.ID
    HAVING COUNT(cn.ID) > 1

This finds order records where the name is known and matches a customer-name record but the name record is not identified. Problem, though: could be more than one customer with the same name.

SELECT 
    cn.ID, o.*
FROM
    `vbz-vc-dev`.orders AS o
        LEFT JOIN
    cust_names AS cn ON cn.Name = o.BuyerName
WHERE
    (cn.ID IS NOT NULL)
        AND (o.ID_Buyer IS NULL);

Discarded

Might use this in the future, but things get tricky. Do we get a complete list of countries from somewhere, or do we allow customers to enter new ones? In the latter case, NULL would need to be acceptable.

  `ID_Country` INT NOT NULL          COMMENT "ref_country.ID",