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
Line 21: Line 21:
 
** '''NameSearch''': NEW
 
** '''NameSearch''': NEW
 
** '''Search_raw''': REMOVED
 
** '''Search_raw''': REMOVED
 +
* '''2019-10-26''' renaming from <code>cust_addrs</code> to <code>cust_addr</code>
 
==SQL==
 
==SQL==
<source lang=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 46: Line 47:
 
ENGINE = InnoDB;</source>
 
ENGINE = InnoDB;</source>
 
==Migration==
 
==Migration==
<source lang=mysql>INSERT `cust_addrs_new` (
+
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>
 +
Then import the main data:
 +
<source lang=mysql>INSERT `cust_addr` (
 
   `ID`,
 
   `ID`,
 
   `ID_Cust`,
 
   `ID_Cust`,
Line 86: Line 90:
 
   `Extra`,
 
   `Extra`,
 
   `Descr`
 
   `Descr`
   FROM cust_addrs;</source>
+
   FROM cust_addrs_old;</source>
Then rename the tables:<source lang=mysql>ALTER TABLE `vbz-vc-dev`.`cust_addrs`
+
To be fixed: Error Code: 1292. Incorrect datetime value: '0000-00-00 00:00:00' for column 'WhenEnt' at row 1
RENAME TO  `vbz-vc-dev`.`cust_addrs_old` ;
 
ALTER TABLE `vbz-vc-dev`.`cust_addrs_new`
 
RENAME TO  `vbz-vc-dev`.`cust_addrs` ;
 
</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.

Revision as of 18:04, 26 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` ;

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`,
  `WhenEnt`,
  `WhenUpd`,
  `WhenVoid`,
  `Name`,
  NULL,
  `Full`,
  `Search`,
  `Street`,
  `Town`,
  `State`,
  `Zip`,
  `Country`,
  `Extra`,
  `Descr`
  FROM cust_addrs_old;

To be fixed: Error Code: 1292. Incorrect datetime value: '0000-00-00 00:00:00' for column 'WhenEnt' at row 1

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