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, search
(Investigation)
 
(11 intermediate revisions by the same user not shown)
Line 2: Line 2:
 
* '''Fields''':
 
* '''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.
 
** '''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.
** '''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.
+
** '''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)
 
** '''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).
 
** '''Full''': the complete address formatted for printing (e.g. on a label).
Line 9: Line 9:
 
** '''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).
 
** '''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
 
** '''State''' probably needs to be separate fields for as-entered and code-if-known
* '''History''':
+
==History==
** '''2009-07-09''' Moved to separate wiki page; no design changes
+
* '''2009-07-09''' Moved to separate wiki page; no design changes
** '''2011-11-22''' Added '''WhenEnt''' and '''WhenUpd'''
+
* '''2011-11-22''' Added '''WhenEnt''' and '''WhenUpd'''
** '''2012-01-03''' Added '''WhenVoid'''
+
* '''2012-01-03''' Added '''WhenVoid'''
** '''2012-01-11''' Tentative definitions for '''Full''' and  '''Search'''
+
* '''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 {{l/vc/table|cust_names}} table, and renaming some fields
 +
** '''Full''' → '''AddrFull'''
 +
** '''Search''' → '''AddrSearch'''
 +
** '''NameSearch''': NEW
 +
** '''Search_raw''': REMOVED
 +
* '''2019-10-26''' renaming from <code>cust_addrs</code> to <code>cust_addr</code>
 
==SQL==
 
==SQL==
<section begin=sql /><mysql>CREATE TABLE `cust_addrs` (
+
<source lang=mysql>CREATE TABLE `cust_addr` (
 
   `ID`        INT NOT NULL AUTO_INCREMENT,
 
   `ID`        INT NOT NULL AUTO_INCREMENT,
 
   `ID_Cust`    INT COMMENT "core_custs.ID",
 
   `ID_Cust`    INT COMMENT "core_custs.ID",
   `Name`       VARCHAR(31)           COMMENT "human-assigned name for this address",
+
   `Label`     VARCHAR(31)         COMMENT "human-assigned label for this address",
 
   `WhenAct`    DATETIME              COMMENT "date when first active/usable; NULL = always",
 
   `WhenAct`    DATETIME              COMMENT "date when first active/usable; NULL = always",
 
   `WhenExp`    DATETIME              COMMENT "date when no longer usable; NULL = never",
 
   `WhenExp`    DATETIME              COMMENT "date when no longer usable; NULL = never",
Line 24: Line 32:
 
   `WhenUpd`    DATETIME DEFAULT NULL COMMENT "when record was last updated",
 
   `WhenUpd`    DATETIME DEFAULT NULL COMMENT "when record was last updated",
 
   `WhenVoid`  DATETIME DEFAULT NULL COMMENT "when this record was voided",
 
   `WhenVoid`  DATETIME DEFAULT NULL COMMENT "when this record was voided",
   `Full`      VARCHAR(255)          COMMENT "Full address (street, town, zip, country)",
+
   `Name`      VARCHAR(255)          COMMENT "name of addressee",
   `Search`     VARCHAR(255)          COMMENT "Full address, normalized for searching",
+
  `NameSearch` VARCHAR(255)          COMMENT "name with delimiters removed, for searching",
   `Search_raw` VARCHAR(255)          COMMENT "Text used to create Search field",
+
   `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.)",
 
   `Street`    VARCHAR(127)          COMMENT "address within town (street, apt., etc.)",
 
   `Town`      VARCHAR(63)          COMMENT "name of town",
 
   `Town`      VARCHAR(63)          COMMENT "name of town",
 
   `State`      VARCHAR(63)          COMMENT "state or province name/code",
 
   `State`      VARCHAR(63)          COMMENT "state or province name/code",
 
   `Zip`        VARCHAR(31)          COMMENT "zipcode or postal code",
 
   `Zip`        VARCHAR(31)          COMMENT "zipcode or postal code",
   `Country`    VARCHAR(63) COMMENT "name of country",
+
   `Country`    VARCHAR(63)           COMMENT "name of country as entered by customer",
   `Extra`      VARCHAR(63) COMMENT "additional instructions to always write on pkg",
+
   `Extra`      VARCHAR(63)           COMMENT "additional instructions to always write on pkg",
   `Descr`      VARCHAR(255) COMMENT "notes on this address",
+
   `Descr`      VARCHAR(255)         COMMENT "notes on this address",
 
   PRIMARY KEY(`ID`)
 
   PRIMARY KEY(`ID`)
 
)
 
)
ENGINE = MYISAM;</mysql>
+
ENGINE = InnoDB;</source>
<section end=sql />
+
==Migration==
 +
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>
 +
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:
 +
<source lang=mysql>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;</source>
 +
 
 +
==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==
 +
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.
 +
<source lang=mysql>
 +
  `ID_Country` INT NOT NULL          COMMENT "ref_country.ID",
 +
</source>

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