- 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
- 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
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;
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;
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);
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",