VbzCart/docs/tables/cust addr: Difference between revisions
Jump to navigation
Jump to search
imported>Woozle (moved from "customer tables" page) |
imported>Woozle (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
<section begin=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> <section end=sql />