VbzCart/docs/tables/cust addr: Difference between revisions

From Woozle Writes Code
< VbzCart‎ | docs‎ | tables
Jump to navigation Jump to search
imported>Woozle
(Name->Tag, +Name; tidying)
m (Woozle moved page VbzCart/VbzCart/tables/cust addr to VbzCart/docs/tables/cust addr without leaving a redirect: part 3/5)
 
(10 intermediate revisions by one other user not shown)
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'''; added ID_Country
* '''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
* '''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''' &rarr; '''AddrFull'''
** '''Search''' &rarr; '''AddrSearch'''
** '''NameSearch''': NEW
** '''Search_raw''': REMOVED
* '''2019-10-26''' renaming from <code>cust_addrs</code> to <code>cust_addr</code>
==SQL==
==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",
Line 25: 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)",
  `Search`    VARCHAR(255)          COMMENT "full address, normalized for searching",
  `Search_raw` VARCHAR(255)          COMMENT "text used to create Search field",
   `Name`      VARCHAR(255)          COMMENT "name of addressee",
   `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.)",
   `Street`    VARCHAR(127)          COMMENT "address within town (street, apt., etc.)",
   `Town`      VARCHAR(63)          COMMENT "name of town",
   `Town`      VARCHAR(63)          COMMENT "name of town",
Line 34: Line 41:
   `Zip`        VARCHAR(31)          COMMENT "zipcode or postal code",
   `Zip`        VARCHAR(31)          COMMENT "zipcode or postal code",
   `Country`    VARCHAR(63)          COMMENT "name of country as entered by customer",
   `Country`    VARCHAR(63)          COMMENT "name of country as entered by customer",
  `ID_Country` INT NOT NULL          COMMENT "ref_country.ID",
   `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>
==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 01:56, 25 February 2024

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 Template:L/vc/table 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",