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
(WhenEnt, WhenUpd)
(+WhenVoid)
Line 1: Line 1:
 
==About==
 
==About==
 
* '''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.
 
** '''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)
 
** '''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
 
** '''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
 
** '''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
−
** '''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... egh). (Consequently, the [Contacts x Addrs] table in the  Access version will be going away too.)
 
 
* '''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'''
 
==SQL==
 
==SQL==
 
<section begin=sql /><mysql>CREATE TABLE `cust_addrs` (
 
<section begin=sql /><mysql>CREATE TABLE `cust_addrs` (
Line 15: Line 17:
 
   `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",
−
   `WhenEnt`    DATETIME    NOT NULL COMMENT "date when record was first created",
+
   `WhenEnt`    DATETIME    NOT NULL COMMENT "when record was first created",
−
   `WhenUpd`    DATETIME DEFAULT NULL COMMENT "date 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",
 
   `Full`      VARCHAR(255)          COMMENT "Full address (street, town, zip, country)",
 
   `Full`      VARCHAR(255)          COMMENT "Full address (street, town, zip, country)",
 
   `Search`    VARCHAR(255)          COMMENT "Full address, normalized for searching",
 
   `Search`    VARCHAR(255)          COMMENT "Full address, normalized for searching",

Revision as of 00:30, 4 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)
    • 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
    • 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

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>

Retrieved from "https://htyp.org/mw/index.php?title=VbzCart/tables/cust_addr&oldid=16598"