Difference between revisions of "VbzCart/tables/cust addr"

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
Jump to navigation Jump to search
 
(3 intermediate revisions by the same user not shown)
Line 49: Line 49:
 
Rename the old table to prevent code from accidentally using it:<source lang=mysql>ALTER TABLE `vbz-vc-dev`.`cust_addrs`  
 
Rename the old table to prevent code from accidentally using it:<source lang=mysql>ALTER TABLE `vbz-vc-dev`.`cust_addrs`  
 
RENAME TO  `vbz-vc-dev`.`cust_addrs_old` ;</source>
 
RENAME TO  `vbz-vc-dev`.`cust_addrs_old` ;</source>
 +
Next, try to fill in as many blank timestamps as possible:
 +
<source lang=mysql>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);</source>
 +
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:
 +
<source lang=mysql>ALTER TABLE `cust_addr`
 +
CHANGE COLUMN `WhenEnt` `WhenEnt` DATETIME NULL COMMENT 'when record was first created' ;</source>
 +
 
Then import the main data:
 
Then import the main data:
 
<source lang=mysql>INSERT `cust_addr` (
 
<source lang=mysql>INSERT `cust_addr` (
Line 76: Line 96:
 
   `WhenAct`,
 
   `WhenAct`,
 
   `WhenExp`,
 
   `WhenExp`,
   `WhenEnt`,
+
   IF(WhenEnt = 0,NULL,WhenEnt),
 
   `WhenUpd`,
 
   `WhenUpd`,
 
   `WhenVoid`,
 
   `WhenVoid`,
Line 91: Line 111:
 
   `Descr`
 
   `Descr`
 
   FROM cust_addrs_old;</source>
 
   FROM cust_addrs_old;</source>
To be fixed: Error Code: 1292. Incorrect datetime value: '0000-00-00 00:00:00' for column 'WhenEnt' at row 1
+
 
 +
==Investigation==
 +
This seemed useful originally, but at the moment I'm not sure what it's good for:
 +
<source lang=mysql>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</source>
 +
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.
 +
<source lang=mysql>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);</source>
 +
 
 
==Discarded==
 
==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.
 
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.

Latest revision as of 21:14, 27 October 2019

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
    • FullAddrFull
    • SearchAddrSearch
    • 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",