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

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
Jump to: navigation, search
(ID_Country discarded for now)
 
Line 17: Line 17:
 
** '''2014-08-30''' Removed '''ID_Country''' for now -- not yet implemented in database, and we're not ready for it yet.
 
** '''2014-08-30''' Removed '''ID_Country''' for now -- not yet implemented in database, and we're not ready for it yet.
 
==SQL==
 
==SQL==
<mysql>CREATE TABLE `cust_addrs` (
+
<source lang=mysql>CREATE TABLE `cust_addrs` (
 
   `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 39: Line 39:
 
   PRIMARY KEY(`ID`)
 
   PRIMARY KEY(`ID`)
 
)
 
)
ENGINE = MYISAM;</mysql>
+
ENGINE = InnoDB;</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.
<mysql>
+
<source lang=mysql>
 
   `ID_Country` INT NOT NULL          COMMENT "ref_country.ID",
 
   `ID_Country` INT NOT NULL          COMMENT "ref_country.ID",
</mysql>
+
</source>

Latest revision as of 14:47, 1 August 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.

SQL

CREATE TABLE `cust_addrs` (
  `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",
  `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",
  `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;

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