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