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
(+WhenVoid)
Line 4: Line 4:
 
** '''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.
 
** '''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.
 
** '''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)
** '''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
+
** '''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
 
** '''State''' probably needs to be separate fields for as-entered and code-if-known
 
* '''History''':
 
* '''History''':
Line 10: Line 13:
 
** '''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'''
 
==SQL==
 
==SQL==
 
<section begin=sql /><mysql>CREATE TABLE `cust_addrs` (
 
<section begin=sql /><mysql>CREATE TABLE `cust_addrs` (

Revision as of 02:45, 12 January 2012

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.
    • 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.
    • 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

SQL

<mysql>CREATE TABLE `cust_addrs` (

 `ID`         INT NOT NULL AUTO_INCREMENT,
 `ID_Cust`    INT COMMENT "core_custs.ID",
 `Name`       VARCHAR(31)           COMMENT "human-assigned name 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",
 `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",
 `Extra`      VARCHAR(63) COMMENT "additional instructions to always write on pkg",
 `Descr`      VARCHAR(255) COMMENT "notes on this address",
 PRIMARY KEY(`ID`)

) ENGINE = MYISAM;</mysql>