Difference between revisions of "VbzCart/tables/cust addr"
Jump to navigation
Jump to search
(moved from "customer tables" page) |
(WhenEnt, WhenUpd) |
||
| Line 7: | Line 7: | ||
* '''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 | ||
==SQL== | ==SQL== | ||
<section begin=sql /><mysql>CREATE TABLE `cust_addrs` ( | <section begin=sql /><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", | ||
| − | `Name` VARCHAR(31) COMMENT "human-assigned name for this address", | + | `Name` VARCHAR(31) COMMENT "human-assigned name for this address", |
| − | `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", |
| − | `Full` VARCHAR(255) COMMENT "Full address (street, town, zip, country)", | + | `WhenEnt` DATETIME NOT NULL COMMENT "date when record was first created", |
| − | `Search` VARCHAR(255) COMMENT "Full address, normalized for searching", | + | `WhenUpd` DATETIME DEFAULT NULL COMMENT "date when record was last updated", |
| − | `Search_raw` VARCHAR(255) COMMENT "Text used to create Search field", | + | `Full` VARCHAR(255) COMMENT "Full address (street, town, zip, country)", |
| − | `Street` VARCHAR(127) COMMENT "address within town (street, apt., etc.)", | + | `Search` VARCHAR(255) COMMENT "Full address, normalized for searching", |
| − | `Town` VARCHAR(63) COMMENT "name of town", | + | `Search_raw` VARCHAR(255) COMMENT "Text used to create Search field", |
| − | `State` VARCHAR(63) COMMENT "state or province name/code", | + | `Street` VARCHAR(127) COMMENT "address within town (street, apt., etc.)", |
| − | `Zip` VARCHAR(31) COMMENT "zipcode or postal code", | + | `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", | `Country` VARCHAR(63) COMMENT "name of country", | ||
`Extra` VARCHAR(63) COMMENT "additional instructions to always write on pkg", | `Extra` VARCHAR(63) COMMENT "additional instructions to always write on pkg", | ||
Revision as of 19:26, 22 November 2011
About
- Fields:
- 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.
- 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
- 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:
- 2009-07-09 Moved to separate wiki page; no design changes
- 2011-11-22 Added WhenEnt and WhenUpd
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 "date when record was first created", `WhenUpd` DATETIME DEFAULT NULL COMMENT "date when record was last updated", `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>